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.
Select or highlight cells with max & min value in a range of cells or each column or row:
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!
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:
2. And then select the cell and drag the fill handle over to the range that you want to contain this formula, see screenshot:
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.
Recommended Productivity Tools
Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.
Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!
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...
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
To post as a guest, your comment is unpublished.· 11 months agoThank you so much!!!
To post as a guest, your comment is unpublished.· 1 years agoHi, 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.· 1 years agoThank you! very helpful!
To post as a guest, your comment is unpublished.· 1 years agopardon: 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:
To post as a guest, your comment is unpublished.· 1 years agohi, 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.