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

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

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

on cell B7 enter this formula:

Please Login or Register  to view this content.

copy down up to cell B9

RESULT will look something like this:

Please Login or Register  to view this content.