if statements

[qwerty_2006]

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

I have the following table


Min A% Max B% Max C% Max D% Name
0 1 1 1 James
99.5 0.1 0.3 0.1 Phillip
99.6 0.15 0.3 0.1 Mark
99.7 0.16 0.2 0.1 Adam
99.7 0.17 0.08 0.1 Peter

I have many records which look like the following,

A B C D Name
99.899 0.027 0.050 0.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.

[Pete_UK]

In 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,"0.000")&TEXT(B2,"0.000")&TEXT(C2,"0.000")&TEXT(D2,"0.000")

Copy this down your table, then fix the values by highlighting the
column, then <copy> Edit | Paste Special | Values (check) OK the <Esc>.

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.

Pete

[Herbert Seidenberg]

Here 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
minA maxB maxC maxD
99.3 0.17 0.20 0.20 James
99.5 0.10 0.30 0.10 Phillip
99.7 0.17 0.08 0.15 Mark
99.7 0.16 0.20 0.10 Adam
99.7 0.17 0.08 0.15 Peter

An Bn Cn Dn Acct1 Acct2
99.7 0.17 0.08 0.15 Peter Mark
99.3 0.17 0.20 0.20 James
99.5 0.10 0.20 0.10

Name all columns with the suggested header names.
Acct is a six cell vector. The first cell contains a space.
Use Insert > Name > 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 > Options > General
It is safe to uncheck it after everything works.

[Manfred]

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
============================================
"If your enemy has no scruples, your own scruples are a weapon in his
hand"
============================================

Herbert Seidenberg wrote:
> Here 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
> minA maxB maxC maxD
> 99.3 0.17 0.20 0.20 James
> 99.5 0.10 0.30 0.10 Phillip
> 99.7 0.17 0.08 0.15 Mark
> 99.7 0.16 0.20 0.10 Adam
> 99.7 0.17 0.08 0.15 Peter
>
> An Bn Cn Dn Acct1 Acct2
> 99.7 0.17 0.08 0.15 Peter Mark
> 99.3 0.17 0.20 0.20 James
> 99.5 0.10 0.20 0.10
>
> Name all columns with the suggested header names.
> Acct is a six cell vector. The first cell contains a space.
> Use Insert > Name > 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 > Options > General
> It is safe to uncheck it after everything works.