software

hi, just wondering if anybody has any ideas about solving this problem.

I have the following tableMin A%Max B%Max C%Max D% Name
0111 James
99.50.10.30.1 Phillip
99.60.150.30.1 Mark
99.70.160.20.1 Adam
99.70.170.080.1 Peter

I have many records which look like the following,

ABCD Name
99.8990.0270.0500.016 ??

Has any body got any ideas how I can use the first table to apply a
name to this record? I have many records to match so I was wondering if
there is any way of doing this automatically. I have tried using V
lookup, H lookup and If statements but so far i've had no luck.--
qwerty_2006
------------------------------------------------------------------------
qwerty_2006's Profile: www.excelforum.com/member.php...oamp;userid=32303
View this thread: www.excelforum.com/showthread...hreadid=520576In your table, assuming it starts at A1 with a header row, insert a new
column E just before Name and join the four values together with this
formula in E2:

=TEXT(A2,quot;0.000quot;)amp;TEXT(B2,quot;0.000quot;)amp;TEXT(C2,quot;0.000quot; )amp;TEXT(D2,quot;0.000quot;)

Copy this down your table, then fix the values by highlighting the
column, then lt;copygt; Edit | Paste Special | Values (check) OK the lt;Escgt;.

Do the same for your records, though these are likely to be on a
different row (let's say row 20). In the first cell for Name, F20,
enter this formula:

=VLOOKUP(E20,E$2:F$10,2,0)

and copy down as required. I have assumed your table occupies rows down
to 10 - adjust as necessary.

Hope this helps.

PeteHere is a different approach with these features:
The initial postion of data does not have to be at a specific
row/column in order for the formulas to apply.
If the input data changes, the output data updates in real time.
No need to regenerate the helper column.
Multiple matches can be detected.
All output formulas are identical in appearance when written in R1C1.
No helper column appears on the spreadsheet.

Assume your data looks like this:
Acct
minAmaxBmaxCmaxD
99.30.170.200.20 James
99.50.100.300.10 Phillip
99.70.170.080.15 Mark
99.70.160.200.10 Adam
99.70.170.080.15 Peter

AnBnCnDnAcct1 Acct2
99.70.170.080.15PeterMark
99.30.170.200.20James
99.50.100.200.10

Name all columns with the suggested header names.
Acct is a six cell vector. The first cell contains a space.
Use Insert gt; Name gt; Define
Also define this name:
seq_r Refers To =ROW(INDEX(C1,1):INDEX(C1,ROWS(minA)))
In the first cell below Acct1 enter this array formula:
=INDEX(Acct,LARGE(--((--(MinA=An R)) (--(MaxB=Bn R))
(--(MaxC=Cn R)) (--(MaxD=Dn R))=4)*seq_r,1) 1)
The 4 in the formula refers to the number of data columns.
Copy or drag this cell down three (or more) times.
If you want to check for multiple matches,
fill in Acct2 with the same formula, but
change LARGE(range,1) to LARGE(range,2)
Before you start, check R1C1 in
Tools gt; Options gt; General
It is safe to uncheck it after everything works.I wanted to fire off thsi before I take my annual trip to Ireland.

Herbert, that 4 in your formula is going to confuse the average user.

Why don't you simply change your funny OR to a regular AND?

It will also get rid of all those strange -- marks.
=INDEX(Acct,LARGE((MinA=An R)*(MaxB=Bn R)*
(MaxC=Cn R)*(MaxD=Dn R)*seq_r,1) 1)

Manfred
============================================
quot;If your enemy has no scruples, your own scruples are a weapon in his
handquot;
============================================

Herbert Seidenberg wrote:
gt; Here is a different approach with these features:
gt; The initial postion of data does not have to be at a specific
gt; row/column in order for the formulas to apply.
gt; If the input data changes, the output data updates in real time.
gt; No need to regenerate the helper column.
gt; Multiple matches can be detected.
gt; All output formulas are identical in appearance when written in R1C1.
gt; No helper column appears on the spreadsheet.
gt;
gt; Assume your data looks like this:
gt; Acct
gt; minAmaxBmaxCmaxD
gt; 99.30.170.200.20 James
gt; 99.50.100.300.10 Phillip
gt; 99.70.170.080.15 Mark
gt; 99.70.160.200.10 Adam
gt; 99.70.170.080.15 Peter
gt;
gt; AnBnCnDnAcct1 Acct2
gt; 99.70.170.080.15PeterMark
gt; 99.30.170.200.20James
gt; 99.50.100.200.10
gt;
gt; Name all columns with the suggested header names.
gt; Acct is a six cell vector. The first cell contains a space.
gt; Use Insert gt; Name gt; Define
gt; Also define this name:
gt; seq_r Refers To =ROW(INDEX(C1,1):INDEX(C1,ROWS(minA)))
gt; In the first cell below Acct1 enter this array formula:
gt; =INDEX(Acct,LARGE(--((--(MinA=An R)) (--(MaxB=Bn R))
gt; (--(MaxC=Cn R)) (--(MaxD=Dn R))=4)*seq_r,1) 1)
gt; The 4 in the formula refers to the number of data columns.
gt; Copy or drag this cell down three (or more) times.
gt; If you want to check for multiple matches,
gt; fill in Acct2 with the same formula, but
gt; change LARGE(range,1) to LARGE(range,2)
gt; Before you start, check R1C1 in
gt; Tools gt; Options gt; General
gt; It is safe to uncheck it after everything works.