How to get Excel column letter from match

Summary

1. First using COLUMN and MATCH functions, get the column number of the matching value.

2. Then using ROW function get the row number.

3. After that use the ADDRESS function to get cell reference of the matching cell with absolute reference for the row number.

4. Now you can isolate the column letter by splitting the above found cell address into two parts using ‘$’ symbol as a separator.

Now let's see in detail, how we can return the column letter of a matching value or based on some sort of criterion.

To find out the column letter of a cell that matches a criterion, first we need to find the column number of that cell.

• Use Excel COLUMN function to get the first column number of the given cell range. Then use MATCH function to find the relative position of the cell that matches the given criteria.

• Now you know the first column number of the cell range and the relative position of the matched cell.

• Adding up those two values gives the column number counting from the first column which is column ‘A’.

• If you know the column number it is very easy to find the column letter.

Let us consider a simple example on how to find the column letter based on a criterion.

Given below is a cell range with 5 numbers located in a row from B5 to F5.

Now let’s say we want to get the column letter where the value of the cell is 65. Here, column letter that matches with given criterion is E.

We want it to find using excel functions.

First get the column number of the first cell of the range using COLUMN function.

• In a cell, insert the formula, =column(B5)

• It returns the number 2, which is the column number for the first cell of the given range. And it’s column letter is B.