double lookup

[bg]

In Excel2000, I need help with the following formula:

my data is arranged like this ( I can rearrange if needed)..

joe pete sue rating
30 10 5 A
20 5 3 B
10 2 1 C

I then have values for joe, pete, sue. I want to look up the name and then
give me the rating. The numbers may not be exact. For example, I want the
output to look like this ( with the formula looking up the rating)

name amount rating (formula needed)
joe 11 C
pete 7 B
sue 1 C

Can someone please help me with the formula to find the rating.

Thanks

 

[Frank Kabel]

Hi
try the following formula in C2:
=INDEX(OFFSET('sheet1'!$A$2:$A$10,0,MATCH(A2,'sheet1'!$A$1:$C$1,0)),MAT
CH(B2,OFFSET('sheet1'!$A$2:$A$10,0,MATCH(A2,'sheet1'!$A$1:$C$1,0)),-1))

 

[bg]

I am still having problems with this formula ( I keep getting #N/A). Can you
please tell me where the data should be. Also, the amounts I am looking up
are not exact. Does this matter?

Thanks again.

 

[Frank Kabel]

Hi
that the amounts are not exact shoudn't matter (As I'm using -1 as 3rd
parameter in the last MATCH function call. You may try both MATC
functions individually and see which data it does not find. e.g. try:
=MATCH(A2,'sheet1'!$A$1:$C$1,0)

and (if you want to check for Joe)
=MATCH(B2,'sheet1'!$A$2:$C$20,0)

Also I made one mistake in the original formula. Try:
=INDEX(OFFSET('sheet1'!$A$2:$A$10,0,MATCH(A2,'sheet1'!$A$1:$C$1,0)-1),M
ATCH(B2,OFFSET('sheet1'!$A$2:$A$10,0,MATCH(A2,'sheet1'!$A$1:$C$1,0)),-1
))

 

[bg]

Thanks again. I am still not getting the ranking. I am still getting N/A. In
sheet1, I have the data in cells A15. In sheet2, I have my lookup area in
A1:C3. With your formula I get:

name amount rating
joe 11 N/A
pete 7 N/A

thanks