Excel: Get the Column Letter of a Matched Value (Formula + Builder)

Updated on Mon Oct 12 2026

The formula: to get the column letter of the cell in B5:F5 that holds 65, use

=SUBSTITUTE(ADDRESS(1,COLUMN(INDEX(B5:F5,MATCH(65,B5:F5,0))),4),"1","")

It returns E. Swap in your own range and value, or let the builder below write it for you.

Formula builder: column letter of a match

Type your range and the value to find. Edit the cells to test it, then copy the formula.

Range to search

One row (like B5:F5) or one column (like A2:A8).

Value to find

A number, text, or a cell reference such as H2.

Formula (any Excel version)

How the formula works

Excel has no function that returns a column letter directly, so the formula builds one in four steps. The example finds 65 in the range B5:F5.

Row 5	B	C	D	E	F
Value	12	40	27	65	18

Find the position. MATCH(65,B5:F5,0) returns 4, because 65 is the 4th cell of the range. The 0 asks for an exact match.
Get that cell. INDEX(B5:F5,4) returns a reference to the 4th cell, E5.
Get its column number. COLUMN(E5) returns 5.
Turn the number into a letter. ADDRESS(1,5,4) returns the relative reference E1. SUBSTITUTE(…,"1","") removes the 1, leaving E.

The step-by-step way: first column + position − 1

You can also add the range's first column number to the MATCH position:

=COLUMN(B5)+MATCH(65,B5:F5,0)-1

COLUMN(B5) is 2 and MATCH returns 4. Subtract 1, because MATCH counts the first cell as 1: 2 + 4 − 1 = 5, column E. Leaving out the −1 is the most common mistake with this method, and it gives you the column to the right of the match.

To convert that number to a letter, wrap it the same way:

=SUBSTITUTE(ADDRESS(1,COLUMN(B5)+MATCH(65,B5:F5,0)-1,4),"1","")

Excel 365 and 2024: TEXTBEFORE

Newer versions can cut the letter out with TEXTBEFORE in place of SUBSTITUTE:

=TEXTBEFORE(ADDRESS(1,COLUMN(INDEX(B5:F5,MATCH(65,B5:F5,0))),4),"1")

XMATCH(65,B5:F5) also works in place of MATCH(65,B5:F5,0), since XMATCH defaults to an exact match.

Match on a condition instead of a value

MATCH also accepts a calculated lookup value. For example, to find the column of the largest number:

=SUBSTITUTE(ADDRESS(1,COLUMN(INDEX(B5:F5,MATCH(MAX(B5:F5),B5:F5,0))),4),"1","")

To find the first value above 50 (in Excel 365), use a logical test inside XMATCH:

=SUBSTITUTE(ADDRESS(1,COLUMN(INDEX(B5:F5,XMATCH(TRUE,B5:F5>50))),4),"1","")

Common errors

#N/A: no cell matches exactly. Check for extra spaces (TRIM) or numbers stored as text.
Wrong letter, one column to the right: you added COLUMN and MATCH without the −1.
A letter and a digit, like E5: ADDRESS used the real row number. Use 1 as the row argument so SUBSTITUTE can remove it cleanly.

Need the row instead? See how to return the row number of a matching cell.