Thread: Excel lookup function - harder than normal

Wondering if someone could help:

I have a database with a 'MAX' function to the right of it. I am trying to lookup that max value in the row and return the title in row 1 that it corresponds to.

Failing that, I would like to return the actual column that the corresponding number is in so I can then write another formula using that colomn reference point.

Can anyone please help?

Thanks in advance!!

Use Index & Match

Formula would be something along the lines of:

=INdex($A$1:$E$1,Match(MAX(A2:E2),A2:E2,0)

I think,

Basically you are telling excel to look for the max value in Range A2:E2 and return it's position.

So if the Max value was 100, and in A4, it would return the 4.

Then INdex Says return in A1:E1 what is position 4..


Sorry i cant be more specific but that is definately what you needs, I dont have excel access atm. ]=

Thank you so much, I tried for ages with the INDEX function and just could not get it to work.

In a data table, is there a way of search for the max number in the table and returning the corresponding title at the top of the table and name at the left of the table.

w x y z
a 1 2 6 3
b 7 8 2 3
c 4 9 4 1
d 6 2 3 7


ie, to look up the max (9 in this case) and return x in one cell and c in another??

This is only a good appraoch if you can guarantee a single max value (no ties). If the numbers are in B2:E5 with column headers in E1:E5 and row "headers" in A2:A5

=INDEX(B1:E1,MIN(IF(B2:E5=MAX(B2:E5),COLUMN(B1:E1)-COLUMN(B1)+1)))

and

=INDEX(A2:A5,MIN(IF(B2:E5=MAX(B2:E5),ROW(A2:A5)-ROW(A2)+1)))

both confirmed with CTRL+SHIFT+ENTER

It works - that is tremendous news!!

Thanks ever so much