How to Query Google Sheets by Column Name / Label

The ability to query Google Sheets as though it were a database is absolutely awesome. There's just one small challenge: You can't reference columns by header labels, i.e. the names you add the first row of each column. This limitation exists probably because the first row of a spreadsheet is no different from all the other rows. It's just a row. It serves no pre-defined special function such as to specify names of columns. Fortunately, though, Google Sheets is insanely awesome in a million other ways. And with a little Google Sheets trickery, you can easily query Google Sheet by the column names in your header row. Here's how. As you no doubt know, the Google Sheets QUERY function requires that you reference a column by it's letter.

SELECT C, A, B

The problem is... what if you don't necessarily know which column your desired data is going to appear in. For example, maybe you're pulling in data from a CSV feed using IMPORTDATA? The data could get moved around on that external file, columns might drop in and out, etc. What we want is to be able to look up columns by a value in the header row of our sheet. Something like:

SELECT Year, Make, Model

We'll achieve our desired result by figuring out the letter name of each column where the first row's value equals the label we want. 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_table!A1:C1,0)

This 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... and, because your header is pretty much always going to be the first row, we know the row number. We can take these two numbers (1 and 3) and pass them to the ADDRESS function.

=ADDRESS(1,MATCH("Year",data_table!A1:C1,0),4)

The ADDRESS function gives us the of the actual 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_table!A1:C1,0),4),1,"")

All that remains is to plug this into our QUERY function and we can dynamically reference columns by the column names in the first/header row.

=QUERY(data!A:C,"SELECT "&SUBSTITUTE(ADDRESS(1,MATCH("Year",data!A1:C1,0),4),1,""))

Heck yeah! We've got our "Year" column!

Year
2010
2011
2012
2013