Lookup in a table

**prabodh1234** · 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** · 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** · 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.