Forum Discussion
Return column header for max value excel but ...
Hi,
I'm trying to return the column header for max value in excel.
Formula using below at the moment:
=INDEX($1:$1,1, MATCH(MAX(C5,AG5,AP5,AY5,BH5,BQ5,BZ5,CI5,CR5),(C5,AG5,AP5,AY5,BH5,BQ5,BZ5,CI5,CR5), 0))
$1:$1 - column headers
MAX(C5,AG5,AP5,AY5,BH5,BQ5,BZ5,CI5,CR5) - individual cells to choose the maximum value from
(C5,AG5,AP5,AY5,BH5,BQ5,BZ5,CI5,CR5) - look up array part of the formula which I'm having issues with.
If the individuals formed part of a continuous range e.g. c5:AG5 then there isn't a problem.
However, because the range is individual cells I don't know how to display them in the look array part of the formula.
Please help.
Thanks
1 Reply
I tried your formula with fewer cells but it works the same. If there is no other numeric data between the cells referenced, something like this will work:
=INDEX($1:$1,,MATCH(MAX(C5,AG5,AP5,AY5,BH5),$5:$5,0))
