Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or
0
0
0
s2smodern

 How to find highest value in a row and return column header in Excel?

In this article, I will talk about how to return the column header of the largest value in a row in Excel. For Example, I have the following data range, column A is the year, and column B to F are populated the order numbers from Jan to May. And now, I want to get the month name of the largest value in each row.

doc-get-column-header-1

Find highest value in a row and return column header with formula


Select or highlight cells with max & min value in a range of cells or each column or row:

doc-get-column-header-4

Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!


arrow blue right bubble Find highest value in a row and return column header with formula


To retrieve the column header of the largest value in a row, you can apply a combination of INDEX, MATCH and MAX functions to get the result. Please do as follows:

1. Enter this formula into a blank cell you need: =INDEX($B$1:$F$1,0,MATCH(MAX($B2:$F2),$B2:$F2,0)), and then press Enter key to get the month name which matches the largest value in a row. See screenshot:

doc-get-column-header-2

2. And then select the cell and drag the fill handle over to the range that you want to contain this formula, see screenshot:

doc-get-column-header-3

Note: In the above formula: B1: F1 is the headers row that you want to return, B2: F2 is the data range which contains the largest value you want to find.


Related article:

How to find the highest value and return adjacent cell value in Excel?


Recommended Productivity Tools

Office Tab

gold star1 Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.

Kutools for Excel

gold star1 Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!

gold star1 300 New Features for Excel, Make Excel Much Easy and Powerful:

  • Merge Cell/Rows/Columns without Losing Data.
  • Combine and Consolidate Multiple Sheets and Workbooks.
  • Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
  • Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
  • More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools...

Screen shot of Kutools for Excel

btn read more      btn download     btn purchase

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Janice Matthies · 11 months ago
    Thank you so much!!!
  • To post as a guest, your comment is unpublished.
    Kirthika · 1 years ago
    Hi, am trying to find lowest value in a row and the highest value on the corresponding column. Is there a formula or shortcut to find this. Please guide
  • To post as a guest, your comment is unpublished.
    Sean · 1 years ago
    Thank you! very helpful!
  • To post as a guest, your comment is unpublished.
    dave · 1 years ago
    pardon: think my message dissappeard (2nd copy?)
    hi, i am trying to find the column number of a max number in 1 row of 5 cells. if i start from M9 to Q9 and the max number is in the middle cell: O9 the result looking for would be: 3. i cannot change the header cells to have: 1 2 3 4 5 as they get values pasted over.. if this is the same formula as above. how to i modify it to use column locations instead of header values. thanks:

    =INDEX(M1:Q1,0,MATCH(MAX(M9:Q9),M9:Q9,0))
  • To post as a guest, your comment is unpublished.
    dave · 1 years ago
    hi, i am having trouble finding an "unquote" :) column number for where the maximun number is. if i use columns M9 thru Q9 and the max is in the middle column: O9, that is the 3rd column in that range. i cannot put 1 2 3 4 5 in the header cells as they get values pasted over. if this is the same formula as above, how do i modify it to get a result eg: 3 for 3rd column in. thanks.

    =INDEX(M1:Q1,0,MATCH(MAX(M9:Q9),M9:Q9,0))