How do I get the column letter of a single row where a particular value equals a test value?

Question

I need to get the letter of the column that has a value in a given row that matches a given value in Google Sheets, assuming that no values in the row are duplicates.

For example, in the above screenshot, if the row is the first row, and the test value is Jun, the formula will return H.

Kind of meta. Appreciate any help.

Answer 1

Answer

The following formula should produce the behaviour you desire:

=REGEXREPLACE(ADDRESS(1,MATCH("Jun",A1:1),4),"[1-9]*",)

Explanation

The =MATCH formula returns the position of the item in a range which has a specified value. In this case, the specified value is "Jun" and the range is A1:1.

=ADDRESS returns the A1 notation of a row and column specified by its number. In this case, the row is 1 and the column is whichever number is returned by the =MATCH. The 4 is there so that =ADDRESS returns H1 instead of $H$1 (absolute reference is its default).

=REGEXREPLACE looks through a string for a specified pattern and replaces that portion of the string with another string. In this case, the pattern to search for is any number. The last argument of =REGEXREPLACE is blank so it simply removes all numbers from the string.

What is left is the letter of the column where the value is found.

Functions Used:

• =MATCH
• =ADDRESS
• =REGEXREPLACE