So, you’re working with Google Sheets and you realize, “Hey, I need to get a specific column. But wait… I need to be able to identify that column by name (i.e. the value of the cell in the first row) because I can’t be sure of the letter that represents each column.” You can do this. Here’s how.
Now, at first you might think you’d need to create a script to retrieve the first row to select a column in Google Sheets by referencing a cell value in the first row, but you can actually do this quite easily with a relatively simple formula.
Our goal for this formula is to get the letter name (A, B, C, etc.) of the column where a cell’s value in the first row matches the value we’re looking for.
But before we get started, let’s set up an example. Here’s a table of data.
| Make | Model | Year |
|---|---|---|
| Toyota | Corolla | 2010 |
| Ford | Fusion | 2011 |
| Honda | Civic | 2012 |
| Hyundai | Elantra | 2013 |
The first step is to search the first row for the desired column name and return the column’s position. To do this, we’ll use MATCH.
=MATCH("Year",data!A1:C1,0)The will return the value “3“. In other words, the formula has found the value “Year” in the third column of the first row.
So, now we know the column number. But now we need to transform that number into the corresponding letter.
To accomplish this, we’ll use the ADDRESS function to retrieve the cell’s coordinates. The address function requires two parameters: 1) the column number, and 2) the row number. We now know the column number. And, of course, we’ve known the row number all along: 1. (Our column names are in the first row.)
So, our address function looks like this:
=ADDRESS(1,MATCH("Year",data!A1:C1,0),4)You may have noticed the third parameter in that formula. That third parameter tells Google Sheets whether the references to the row and column are relative or absolute. I’ve set the value to 4 which tells Google Sheets that both references are relative.
The ADDRESS function returns the cell reference as a string. In this case, we get “C1“.
Hey! There’s that column letter that we’re looking for. If only there were a function to get rid of that pesky row ID… 😉
As you’ve probably guessed, we’ll apply SUBSTITUTE to get rid of the “1”, leaving just “C“.
=SUBSTITUTE(ADDRESS(1,MATCH("Year",data!A1:C1,0),4),1,"")And there you have it. You’re now left with the letter name of the column where the cell in the first row matches the value you’re looking for. In other words, you’ve just looked up a column by its name (or “label”, if you prefer).
Bonus How To
Suppose you wanted to retrieve the entire column. In other words, suppose you wanted to get all the rows from the column you’ve just looked up. There are a couple ways you could do this.
Use INDIRECT to get a column by name / label
The INDIRECT function returns a cell reference specified by a string. What this means is that you can tell the INDIRECT function, “Hey, get me the column of data whose reference is represented by ‘data!C:C'”.
The first step is to create the “string” that represents the column you want to retrieve. Of course, we want to get the column by name. So, we’ll incorporate our fancy column identification formula.
=SUBSTITUTE(ADDRESS(1,MATCH("Year",data!A1:C1,0),4),1,"")That gives us “C”, right? We want our final string result to look like “data!C:C”. Here’s how to accomplish that.
="data!"&SUBSTITUTE(ADDRESS(1,MATCH("Year",data!A1:C1,0),4),1,"")&":"&SUBSTITUTE(ADDRESS(1,MATCH("Year",data!A1:C1,0),4),1,"")Now, just pass that formula to your indirect function.
=INDIRECT("data!"&SUBSTITUTE(ADDRESS(1,MATCH("Year",data!A1:C1,0),4),1,"")&":"&SUBSTITUTE(ADDRESS(1,MATCH("Year",data!A1:C1,0),4),1,""))Use QUERY to get specific rows by column name / label
Suppose that, rather than retrieving the values from a specific column, you wanted to retrieve a specific set of rows where the value in a specific column matches a value of your choosing. For example, suppose you wanted to say something like “Get me all the rows where the value of the column named ‘Year’ is ‘2010’.”
This is where the QUERY function really comes in handy.
=QUERY(data!A:C,"SELECT * WHERE "&SUBSTITUTE(ADDRESS(1,MATCH("Year",data!A1:C1,0),4),1,"")&"=2010")Now you have at your disposal some powerful functions for looking up data by column name. I hope this proves useful. Please share your comments, ideas, and suggestions below.
Hello! For the “Use INDIRECT to get a column by name / label”, how do I use this to combine columns from multiple tabs? I have three tabs that are importing data from external sources using importrange. They are all called ‘Unique ID’. I want to combine them into a single column in a new tab. Thank you!!
It sounds like you are wanting to create an array where the data from each sheet is appended as rows. Use an array to combine the data from the three sheets into a single sheet. Use FILTER to limit the ranges to only those rows that have data.
={FILTER(Tab1!A:A,Tab1!A:A<>"");FILTER(Tab2!A:A,Tab2!A:A<>"");FILTER(Tab3!A:A,Tab3!A:A<>"")}I am following and using this formula down to final string result to look like “data!C:C” – I am trying to use the Query Function to populate the rest of the column with the data in the identified header of the column but it looks like the Google Visualisation API Query Language no longer supports the combination of the above with my query.
=”Data!’!”&SUBSTITUTE(ADDRESS(1;MATCH(“nameofcolumnHeaderColumnA”;’Data’!A1:S1;0);4);1;””)
This
=QUERY(Data!D:S;”SELECT “&SUBSTITUTE(ADDRESS(1;MATCH(“nameofcolumnHeaderColumnA”;Data!A1:S1;0);4);1;””))
works in older versions of my setup but now when creating it in a new formula it states that it is not compatible with the API query language so it just returns an error.
Thanks,
I suspect you’ve got something else going on there. I attempted the following formula. Worked just fine.
=QUERY(data!A:C,"SELECT "&SUBSTITUTE(ADDRESS(1,MATCH("make",data!A1:C1,0),4),1,""))One peculiarity… Maybe it’s a regional thing. You’re using semi-colons instead of commas. At least in the U.S., the functions in this formula call for commas.