CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulaspreadsheet
Matthew Adams
Matthew Adams

Reputation: 2197

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

enter image description here

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.

Upvotes: 5

Views: 4584

Answers (2)

Trashman
Trashman

Reputation: 1584

Now that Google Sheets has added Named Functions, there is an easier way to do this.

To use named functions, go to Data -> Σ Named Functions. A sidebar will pop up. At the bottom use "Add new function" to create a new named function.

I created two functions to do this: First, COL_CHAR which will take a column reference and return its letter Second, ALPHA_CHAR which takes a numeric input and converts it to letters. I made this one recursive, so if it's an n-letter column name, it will keep calling itself until it gets the full name.

COL_CHAR just converts the referenced column to a column number and passes that to ALPHA_CHAR. It's formula is:

=ALPHA_CHAR( column(cell) )

where cell is an Argument placeholder. Make sure to add that to the argument placeholder list in the sidebar.

Here is the (recursive) formula for ALPHA_CHAR:

=IF( num > 26, ALPHA_CHAR( INT( num / 26 ) ), "") & CHAR( CODE("A") - 1 + MOD( num, 26 ) )

where num is an Argument placeholder.

By making this recursive, even if Google Sheets expands to allow 4-letter (or more) columns in the future, it will keep iterating through every letter regardless of how many there is.

Then, to get the letter of a column in the spreadsheet, you just call COL_CHAR and pass the cell in the column you want, for example:

= COL_CHAR(BK1)

Will return the string "BK"

Upvotes: 3

Sebastian Smiley
Sebastian Smiley

Reputation: 1099

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:

Upvotes: 5

Related Questions