Return the Column Header for a Value in Excel

To return the column header in Excel for a value, you search across a row to find where the value sits, then bring back the header from the same position in row 1. INDEX with MATCH does this in every version of Excel, and XLOOKUP does it in one function in Excel 2021 and Microsoft 365. This guide shows how to find the header of the highest value, of a specific value, and of every value that meets a condition. It is for anyone who needs to answer "which month?" or "which quarter?" from a grid of numbers.

All examples use this quarterly sales grid:

How to return the column header with INDEX and MATCH#

The idea is a horizontal lookup in reverse. You know a value in the data, and you want the label above it. Two functions share the work:

• MATCH searches a row and returns the position of the value: 1 for the first cell, 2 for the second, and so on.
• INDEX takes a range and a position and returns what sits there.

To find which quarter Dana sold 520 in:

=INDEX($B$1:$E$1,MATCH(520,B2:E2,0))

MATCH finds 520 in the 2nd cell of B2:E2, so it returns 2. INDEX returns the 2nd cell of B1:E1, which is Q2. The 0 in MATCH means exact match. Without it, MATCH assumes the row is sorted and can return the wrong position.

The key rule: the header row and the search row must start in the same column and be the same width. B1:E1 and B2:E2 both run from B to E, so position 2 means column C in both. The INDEX function guide and the MATCH function guide explain each part, and the article on the MATCH function covers its match types.

How to find the header of the highest value#

Replace the fixed value with MAX to find the best column in each row:

=INDEX($B$1:$E$1,MATCH(MAX(B2:E2),B2:E2,0))

Type it in F2 and fill down to F4 (Windows: Ctrl+D; Mac: ⌘D):

The $ signs lock the header row, so every row still returns a name from B1:E1. The search row has no $, so it moves down with the formula. For the weakest column, use MIN instead of MAX: =INDEX($B$1:$E$1,MATCH(MIN(B4:E4),B4:E4,0)) returns Q3 for Mia (290). Practise INDEX and MATCH in a live spreadsheet to get the pattern into your fingers.

How to return the column header with XLOOKUP#

XLOOKUP needs only one function. You give it the value to find, the row to search and the row to return from:

=XLOOKUP(MAX(B3:E3),B3:E3,$B$1:$E$1)

The result is Q3, the quarter of Raj's 700. XLOOKUP looks across a row just as easily as down a column, and it defaults to an exact match. Add a fourth argument for a message when nothing is found:

=XLOOKUP(999,B3:E3,$B$1:$E$1,"Not found")

This returns Not found instead of #N/A. XLOOKUP needs Excel 2021 or Microsoft 365. See the XLOOKUP function guide and practise XLOOKUP.

How to pick the row by name#

Sometimes you know the rep's name, not their row. Find the row first, then find the header inside it. With XLOOKUP, the inner XLOOKUP returns Mia's whole row of numbers:

=XLOOKUP(MAX(XLOOKUP("Mia",A2:A4,B2:E4)),XLOOKUP("Mia",A2:A4,B2:E4),B1:E1)

The result is Q4. With INDEX and MATCH, INDEX with a column number of 0 returns the whole row:

=INDEX(B1:E1,MATCH(MAX(INDEX(B2:E4,MATCH("Mia",A2:A4,0),0)),INDEX(B2:E4,MATCH("Mia",A2:A4,0),0),0))

It's long, but every piece is a pattern you already know. Put "Mia" in a cell, such as H1, and point the formula at it so you can change the name. The guides to two-way lookups and INDEX MATCH MATCH go the other direction: from a row name and a column header to the value.

How to list every matching header#

MATCH and XLOOKUP stop at the first match. To list all headers that meet a condition, combine FILTER and TEXTJOIN:

=TEXTJOIN(", ",TRUE,FILTER(B1:E1,B3:E3>500))

FILTER keeps the headers where Raj's sales are over 500, and TEXTJOIN joins them with a comma. The result is Q1, Q3, Q4. This needs Excel 2021 or Microsoft 365.

The same idea handles ties. If two quarters share the highest value, =TEXTJOIN(", ",TRUE,FILTER(B1:E1,B2:E2=MAX(B2:E2))) lists both instead of only the first.

How to find the header of a value anywhere in the grid#

If you don't know which row the value is in, SUMPRODUCT can find its column position across the whole grid:

=INDEX(B1:E1,SUMPRODUCT((B2:E4=700)*(COLUMN(B2:E4)-COLUMN(B1)+1)))

The test marks the cell that holds 700 with a 1. COLUMN(B2:E4)-COLUMN(B1)+1 numbers the columns 1 to 4, so SUMPRODUCT returns 3, and INDEX returns Q3. This only works when the value appears exactly once. If it appears twice, the positions are added together and the answer is wrong. If it doesn't appear, the result is 0, and INDEX with a column number of 0 returns the whole header row instead of one header.

Why is the header lookup wrong or #N/A?#

Most errors come from a few causes:

1. The value isn't in the row. MATCH and XLOOKUP return #N/A. Check spelling, and check for numbers stored as text.
2. The rows don't line up. If the header row is A1:E1 but the search row is B2:E2, every result is shifted one column. Start both in the same column.
3. Missing 0 in MATCH. Without the 0, MATCH does an approximate match and can return a nearby column.
4. Missing $ on the header row. When you fill down, B1:E1 becomes B2:E2, and the formula returns numbers instead of headers.
5. Ties. Only the first header from the left is returned. Use the FILTER method if ties matter.

For a newer alternative to MATCH that can also search from the right, see the XMATCH function. The guide to INDEX MATCH covers the vertical version.

Practise it#

Returning a header is a lookup turned sideways, and it comes up in reporting tasks and job tests. Practise INDEX and MATCH in a live spreadsheet, then practise XLOOKUP: you type each formula and ExcelReady checks it instantly. Start with Raj's best quarter in the exercise on this page.