Spreadsheet Practice

How to find the column number of a header in Excel

Formula (Excel and Google Sheets)

=XMATCH(K1,A1:H1)

Match the header name in the header row: =XMATCH(K1,A1:H1) returns its column number within the row, here 4.

Result on the example
4 in K2
Works in
Excel 2021, 2024 and Microsoft 365
Google Sheets
Same formula

See it work

The formula is already in K2. Change any input and the result updates, or edit the formula to experiment.

How it works

XMATCH searches the header row for the name and returns its position: Region is the 4th header. Because the range starts in column A, the position is also the sheet column number. It is the number VLOOKUP and INDEX need when you want a column by name.

=XMATCH(K1,A1:H1)

Result4

  1. K1"Region"

    The value in K1.

  2. A1:H11 row × 8 cols: "Order", "Date", "Customer", "Region", …

    A range: 8 cells in one row.

  3. XMATCH(K1,A1:H1)4

    XMATCH: Modern MATCH: exact by default.

    • lookup_valueK1"Region"
    • lookup_arrayA1:H11 row × 8 cols: "Order", "Date", "Customer", "Region", …

In Google Sheets

The same formula works in Google Sheets: =XMATCH(K1,A1:H1). We ran it in Google Sheets mode on the same data and it returns 4.

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=MATCH(K1,A1:H1,0)4Works in every version; the 0 asks for an exact match.

Common mistakes

  • The number is counted from the first cell of the range. With C1:H1, Region would be 2.
  • A header with a trailing space doesn't match. Use TRIM(A1:H1) inside XMATCH if headers come from imports.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Find the column number of a header

In K2, return the column number of the header named in K1, counting from column A.

Questions

How do I get the column letter?

Turn the number into an address and strip the row: =SUBSTITUTE(ADDRESS(1,XMATCH(K1,A1:H1),4),"1","").

How do I use it to return the whole column?

Give it to INDEX: =INDEX(A2:H4,0,XMATCH(K1,A1:H1)).

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.