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

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.

The Best Office Productivity Tools

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...
Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
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.

Read More... Free Download... Purchase...

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
Open and create multiple documents in new tabs of the same window, rather than in new windows.
Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!

Read More... Free Download... Purchase...

21 Comments
Login

Sort by Newest
- Best
- Popular
- Newest
- Oldest

You are guest

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.
aj942e · 10 months ago
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?
Any formula?
- Reply
To post as a guest, your comment is unpublished.
saad101 · 11 months ago
how can i modify this logic to show me all the records in one row where a value in all selected columns are greater than 1
- Reply
To post as a guest, your comment is unpublished.
saad · 11 months ago
how can i modify this logic to show all the column header in a single row where the value is > 1?
- Reply
- To post as a guest, your comment is unpublished.
  Marwan · 2 months ago
  
  Did you figure this out
  
  Reply
To post as a guest, your comment is unpublished.
vireb · 1 years ago
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
- Reply
To post as a guest, your comment is unpublished.
Fernando · 1 years ago
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
- Reply
- To post as a guest, your comment is unpublished.
  Hemanta Das · 1 years ago
  
  hi. having same issue. Pls do reply if u have found the way out.
  
  Reply
To post as a guest, your comment is unpublished.
sierranord@gmail.com · 1 years ago
You're Aces Kid !!! ... this example worked perfect for my application: =INDEX($B$1:$F$1,0,MATCH(MAX($B2:$F2),$B2:$F2,0))

ISA 40:31
- Reply
To post as a guest, your comment is unpublished.
Janice Matthies · 3 years ago
Thank you so much!!!
- Reply
To post as a guest, your comment is unpublished.
Kirthika · 3 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
- Reply
To post as a guest, your comment is unpublished.
Sean · 3 years ago
Thank you! very helpful!
- Reply
To post as a guest, your comment is unpublished.
dave · 4 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))
- Reply
To post as a guest, your comment is unpublished.
dave · 4 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))
- Reply
To post as a guest, your comment is unpublished.
Stephanie · 4 years ago
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????
- Reply
- To post as a guest, your comment is unpublished.
  Victoria · 3 years ago
  
  Hello, Did you find a solution?
  
  Reply
To post as a guest, your comment is unpublished.
Liz · 4 years ago
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).
- Reply
To post as a guest, your comment is unpublished.
THANK YOU · 4 years ago
OMFG THIS ARTICLE JUST SAVED ME HOURS OF WORK TODAY

THANK YOU SO MUCH FOR THIS SOLUTION. YOU ARE TRULY A GENIUS AND THIS METHOD IS MUCH APPRECIATED!!!!

10/10
- Reply
To post as a guest, your comment is unpublished.
Joseph · 4 years ago
Is it possible to have this function work with multiple tabs on the same spreadsheet?
- Reply
To post as a guest, your comment is unpublished.
MikeS · 5 years ago
Great thank you, I'd have strugled creating that formula myself, kind of you to share.
- Reply
- To post as a guest, your comment is unpublished.
  Murtaza · 4 years ago
  
  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 ?
  
  Reply
  
  To post as a guest, your comment is unpublished.
  
  Siba · 1 months ago
  
  i have same requirement, pls let me know if you found the answer, thx
  
  Reply

Login to your account

Password Reset