Finding column Heading based on max row value

Hi there,

I'm trying to return a colum heading based on the maximum value in the rows below. Getting the max value is easy using MAX but does anyone know how I can return the value of the the column heading for this max value?


Table

Tom ...... Fred .....Harry
5 ....... 10....... 20
17 ...... 12 ....... 6
45 ...... 100 ....... 36


Results

Max value Belongs to
20 .............Harry
17 ............Tom
100 ...........Fred

many thanks

Sandies

Hi,

Assuming that the max value is in C2 (Harry 20), try this formula on cell D2

=MID(ADDRESS(ROW(C2),COLUMN(C2)),2,FIND("$",ADDRESS(ROW(C2),COLUMN(C2)),2)-2)

If you want the cell address (column and row heading) try this:

=ADDRESS(ROW(C2),COLUMN(C2))

Hi Sandies,

Opsss, silly me, i didn't read the whole question, i thought you were asking how to find the column address of the max value. Anyway i think here is the solution you want.

A1:Tom	B1:Fred	C1:Harry
A2:5	B2:10	C2:20
A3:17	B3:12	C3:6
A4:45	B4:100	C4:36

on cell A7 enter this formula:
=MAX(A2:C2) copy down up to A9

on cell B7 enter this formula:

=INDEX($A$1:$C$1,1,MATCH(MAX(A2:C2),A2:C2,0))

copy down up to cell B9

RESULT will look something like this:

20	Harry
17	Tom
100	Fred