Formula to Find Max in a Row and Return Column Title

[zwestbrook]

I have searched for this and not found exacly what I need.

I have a dataset of 550 rows * 23 columns of data. What I am looking
to do is create a formula that compares by row the values in each
column and returns the column header/title that has the highest
value.

For example, column A has names and columns C-Y has spend amounts. I
want to identify which category had the highest spend and return that
category name in column B.

I have found how to identify the call value for the MAX and how to
return the column title for each column, but not how to combine the
two.

=MAX(C554:Y554)
returns the highest value of row 554 (24.71%)

=CELL("address",INDEX(C554:Y554,MATCH(MAX(C554:Y554),C554:Y554,0)))
returns the cell value of the MAX value ($J$554)

=OFFSET(J554,-552,0)
returns the column title for column J ("ABC")

But when I try to combine the formulas I get an error:
=OFFSET(CELL("address",INDEX(C554:Y554,MATCH(MAX(C554:Y554),C554:Y554,0))),-552,0)

Any suggestions?

 

[BobT]

If you can repeat the headers at the bottom of your data set (even hide them
in row 65535), then you could you the HLOOKUP function to return your headers:

=HLOOKUP (MAX(...), datarange, offset,false)

 

[Rick Rothstein]

Assuming your data starts on Row 2, put this formula in B2 and copy down...

=INDEX(C$1:Y$1,MATCH(MAX(C2:Y2),C2:Y2,0))

 

[zwestbrook]

Assuming your data starts on Row 2, put this formula in B2 and copy down....

=INDEX(C$1:Y$1,MATCH(MAX(C2:Y2),C2:Y2,0))

Rick....this works great! If you have a moment I would appreciate you
breaking your formula down explaining how it works. Thanks again!

 

[Rick Rothstein]

As you already know, MAX(C2:Y2) finds the maximum value in the range C2:Y2.
Then MATCH(MAX(C2:Y2),C2:Y2,0) find the offset to that value within the
range used to determine the maximum. That offset is then used in the INDEX
function to find the value at that offset distance in the row with the
headers. I hope that is clear enough (the wording seems more awkward than
the thought process behind them).

--
Rick (MVP - Excel)

Assuming your data starts on Row 2, put this formula in B2 and copy
down...

=INDEX(C$1:Y$1,MATCH(MAX(C2:Y2),C2:Y2,0))

Rick....this works great! If you have a moment I would appreciate you
breaking your formula down explaining how it works. Thanks again!