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.
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:
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.
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
More than 300 powerful features. Supports Office/Excel
2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features
30-day free trial. 60-day money back guarantee.
Hi, I have attendance sheet. First column heading is names and rest columns for dates. Names columns shows names in rows and dates columns shows p (present) H (weekoff) L (leave) It's for 23 people. and at last I have made total presents in a day, then total leaves and then total weekoffs. "Now I am searching the way where I can click on total present numbers and get list of all names who are present in a particular day." I tried many ways but no luck. first columns names, second shows if they were present or leave or on week offs.
Is there anyway where I can get list of all names who are present or on leave?
Thank for this. It helped. Does it work the same way for a range of cells instead of just a row? I am trying to get the formula to return the column header (month) of the largest and smallest value in a range of cells? This is what I tried =INDEX($B$4:$M$4,0,MATCH(MAX($B5:$M42),$B5:$M42,0)) but it gives a #N/A answer
What happens if you have duplicate values in a row and you want the latest value's header. Is this possible?
The current formula only returns the first match, but in my case I need to know the latest one
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
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:
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.
Hi, i want to make a list with names and grades and i want to find the 3 highest grades and the names that go with them i used the formula =INDEX(A3:A13,MATCH(LARGE(K3:K13,2),K3:K13,0)) but if two people have the same grade what happens??? can someone help me????
How can I do this if I want to return a row header rather than column header? I would also like to base the row number on a value in another cell that matches the column header. (So, look up column header and max value in that column, return the row header for that max value).
Thanks. I would like to know how can this function work if there are multiple cells in a row with the same value ? Is it possible to return multiple column headers in the target cell ?
