[SOLVED] Lookup in a table

prabodh1234 · #1 07-04-2009, 10:32 AM

I have a table of this format:
d e f
a 1 2 3
b 4 5 6
c 7 8 9

Now i want to find the column title for a particular value in the table. For ex, corresponding to row 'b' and value '5', how to get 'e'?? I am a beginner. Please help.

TIA

windknife · #2 07-04-2009, 11:53 AM

how about ?

=INDEX(A1:D1,1,SUM(TRANSPOSE((A2:D4=5))*ROW(1:4)))

Committed with Ctrl+Shift+Enter

HTH,
windknife

Ancalagon12321 · #3 07-04-2009, 12:11 PM

I will assume that your 'd', 'e' and 'f' are in cells B1, C1 and D1, and that 'a', 'b' and 'c' are in A2, A3 and A4.

Then to get the name the header corresponding to the 5 in row "b" you need to write:

=INDEX(B1:D1, 1, MATCH(5, OFFSET(B1:D1, MATCH("b", A2:A4, 0), 0), 0))

Alternatively, if you need to find the name of the header above the number 5 without knowing which row it's in, and are sure that there is only one number 5 in the table, then use windknife's method.

prabodh1234 · #4 07-05-2009, 11:24 AM

Thanks guys...yes it worked

Welcome to the Excel Forum

Forum Statistics