How to return the column header of the max value in Excel

INDEX and MATCH: works in every version

The sheet holds monthly sales for five reps, January to June. The question is which month was each rep's best. MAX(B2:G2) gives Ana's top figure, 24,100. MATCH(…,B2:G2,0) says it is the 4th cell in the row. INDEX($B$1:$G$1,4) returns the 4th header: Apr.

The dollar signs matter. The header row must stay fixed when you copy the formula down, so it is $B$1:$G$1. The data row must move, so B2:G2 has no dollar signs and becomes B3:G3 on the next row. Without the anchors, the second row looks for its header in row 2 and returns a sales figure instead of a month.

XLOOKUP version (Excel 365 and 2021)

XLOOKUP takes the value to find, where to look and what to return, so it reads more naturally: look up the max in the row, return from the header row. The same anchoring rules apply.

To show the value with the header, join them: =INDEX($B$1:$G$1,MATCH(MAX(B3:G3),B3:G3,0))&" ("&TEXT(MAX(B3:G3),"#,##0")&")" returns Mar (17,600). The result is text, so keep the plain header formula if other formulas need to use it.

Ties: show every month that hit the max

Ben sold 17,600 in both March and May. INDEX MATCH and XLOOKUP stop at the first match, so they return Mar and never mention May. If you need the last one instead, give XLOOKUP a search mode of -1 (search from the end): =XLOOKUP(MAX(B3:G3),B3:G3,$B$1:$G$1,,0,-1) returns May.

To list all of them, FILTER the header row by the cells equal to the max and join the result with TEXTJOIN. FILTER works across a row as well as down a column. It needs Excel 365 or 2021. On rows without a tie it returns a single month, so you can use it for every row.

Lowest month, second best and top 3

Swap MAX for MIN to get each rep's weakest month: Jan for Ana. MIN skips empty cells, so Farah, who joined in April, gets Apr (8,200), not a blank month. A month typed as 0 would count, though, so leave months without data empty or use MINIFS with ">0".

For the second best month, use LARGE(B2:G2,2) in place of MAX: =INDEX($B$1:$G$1,MATCH(LARGE(B2:G2,2),B2:G2,0)) returns Jun. In Excel 365, =TAKE(SORTBY(B1:G1,B2:G2,-1),,3) sorts the headers by Ana's sales and keeps the first three: Apr, Jun, May. If two values tie, LARGE returns the same number twice and MATCH finds the same month both times, so use the SORTBY version when ties are possible.

The other direction: who had the max in a column

To find which rep won a given month, run the same pattern down the column and return from the name column: =INDEX(A2:A6,MATCH(MAX(E2:E6),E2:E6,0)) returns Ana Ruiz for April. The guide on finding the highest and lowest value covers this case in more detail.

For the single best month in the whole table, find the cell's column with the max of COLUMN where the grid equals its max. The best figure is 25,400. If the max appears twice, this version returns the rightmost column. In Excel 2019 and earlier, confirm it with Ctrl+Shift+Enter. The same idea with ROW returns the rep: =INDEX(A2:A6,MAX((B2:G6=MAX(B2:G6))*ROW(B2:G6))-ROW(A2)+1) gives Chloe Martin.

When the wrong month comes back: numbers stored as text

Dev's April figure, 15,600, came in from a CSV as text. MAX ignores text, so it sees 14,200 as the top value and the formula returns May. Nothing looks wrong unless you notice the left-aligned cell or the green triangle. =COUNT(B5:G5) returns 5 for six filled months, which gives it away.

The real fix is converting the cell: select it, click the warning triangle and choose Convert to Number. To make the formula cope in Excel 365, convert inside it with --, as below. Also watch for headers that are real dates: the result then shows as a serial number such as 46113. Format the result cell as mmm, or wrap the formula in TEXT(…,"mmm").

Highlight each row's max instead

If you only want to see the best month, conditional formatting marks it on every row, ties included.

1. Select B2:G6.
2. Go to Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format.
3. Enter =B2=MAX($B2:$G2). The $ before the columns keeps every cell comparing with its own row.
4. Choose a fill colour and click OK.