Formula to Find Max in a Row and Return Column Title
 

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:Y55 4),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(C 554:Y554),C554:Y554,0))),-552,0)

Any suggestions?

Formula to Find Max in a Row and Return Column Title
 

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)

"zwestbrook" wrote:

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:Y55 4),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(C 554:Y554),C554:Y554,0))),-552,0)

Any suggestions?

Formula to Find Max in a Row and Return Column Title
 

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 (MVP - Excel)


"zwestbrook" wrote in message
...
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:Y55 4),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(C 554:Y554),C554:Y554,0))),-552,0)

Any suggestions?

Formula to Find Max in a Row and Return Column Title
 

On Oct 8, 2:51*pm, "Rick Rothstein"
wrote:
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 (MVP - Excel)

"zwestbrook" wrote in message

...

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:Y55 4),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(C 554:Y554),C554:Y554,0))),-552,0)

Any suggestions?

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

Formula to Find Max in a Row and Return Column Title
 

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)


"zwestbrook" wrote in message
...
On Oct 8, 2:51 pm, "Rick Rothstein"
wrote:
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 (MVP - Excel)

"zwestbrook" wrote in message

...

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:Y55 4),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(C 554:Y554),C554:Y554,0))),-552,0)

Any suggestions?

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