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.

MakeModelYear
ToyotaCorolla2010
FordFusion2011
HondaCivic2012
HyundaiElantra2013

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 numberand, 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
26 Responses
  1. Dave Meindl

    Import the data into your “data” sheet with importrange. Effectively, there’s no difference between static data and imported data.

  2. David Stirlng

    Thank you very much for writing this. Using your example made it really easy to create a “Sort by: [Column name]” dropdown menu.

  3. Rodolpho Brock

    I MADE THIS SCRIPT

    function columnToLetter(column)
    {
    var temp, letter = ”;
    while (column > 0)
    {
    temp = (column – 1) % 26;
    letter = String.fromCharCode(temp + 65) + letter;
    column = (column – temp – 1) / 26;
    }
    return letter;
    }

    function columnOf(columnName, sheetName, headerRow){
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName(sheetName);
    headerRow = (headerRow?headerRow:1);

    var headerAddress = “A” + headerRow + “:” + columnToLetter(sheet.getLastColumn()) + headerRow;
    var range = sheet.getRange(headerAddress);
    var values = range.getValues();

    for( var column = 0; column <= values[0].length; column++ ) {
    if( values[0][column] == columnName ) return columnToLetter(column+1);
    }

    throw ("ERROR: NOT FOUND!");
    }

    SO I USE LIKE THESE:
    =QUERY(data!A:C;CONCATENATE("SELECT ";columnOf("Year";"data")))

  4. Wanderley Oliveira

    Thank you so much for sharing this awesome way to get things working out in a easy way. Your exemplos made it clear!

    Hugs from Brasil

  5. Elaine

    Thank you for posting this! I spent a ridiculous amount of time searching for a way to deal with my variable column locations. It seemed like such a small problem when I created the spreadsheet, yet it was incredibly difficult to find a working solution. This worked a treat AND provided to ability to match a cell reference instead of a string. I ended up creating a column of drop down menus to choose my desired headers:

    ‘DataSheet’ contains the data with variable columns
    ‘QuerySheet’ is where you choose the headers you’d like
    “DataHeaders” is a named range for your data’s headers, ie ‘DataSheet’!1:1
    ‘QuerySheet’!A2 and down are each Data Validated with a dropdown list for the named range “DataHeaders”

    =QUERY(‘DataSheet’!$A:$Z,”SELECT”
    &SUBSTITUTE(ADDRESS(1,MATCH(‘QuerySheet’!$A$2,DataHeaders,0),4),1,””)
    &”, “&SUBSTITUTE(ADDRESS(1,MATCH(‘QuerySheet’!$A$3,DataHeaders,0),4),1,””)
    &”, “&SUBSTITUTE(ADDRESS(1,MATCH(‘QuerySheet’!$A$4,DataHeaders,0),4),1,””)
    &”, “&SUBSTITUTE(ADDRESS(1,MATCH(‘QuerySheet’!$A$5,DataHeaders,0),4),1,””))

    My data set has over 60 columns, and I use this to grab over 30 of them with no problems. The formula is absurdly long, but it works. I’m sure it could be condensed to be more efficient, but I’ve spent far too much time already on such a small issue! It would be great if Google could make selecting headers in a Query more intuitive.

    Thanks again!

  6. Arun Kumar Nair

    This Is close to what I was searching. Thank you. Can you help me with my exact problem. It is as below:
    I have a sheet with sales data of salesman selling two products x and y arranged over a period of time. Like x jan’17 y jan’17 x feb’17 y feb’17 and so on till x dec’18 y dec’18. I want to find all columns which has sales of x in 17. Is it possible?

  7. Narendra

    hi expert, I have done code in MS XLS but this does not work for Google Sheets can you please help me create a macro for Google sheet, which will find certain text in a in given column and copy entire row and paste in given sheet name after Last entry.

    For an example I have a sheet which content verius fruites data on date 13-1-2018, if I run macro then it should find mango and apple in the given column refrence and copy entire row and add it to “mango” sheet after 12-1-2018 entry and same thing with apple too

  8. You can create macros in Google Sheets. However, in this case, it sounds like a macro might be unnecessary (and inefficient).

    For example, you could use QUERY. Suppose that your “fruit” is in column C.
    =QUERY(product_sales!A:Z,"SELECT * WHERE C = 'mango'")
    If you want to show only records from yesterday, you’d do something like the following. (Suppose that the dates are in column A.)
    =QUERY(product_sales!A:Z,"SELECT * WHERE C = 'mango' AND A = DATE '"&TEXT(DATEVALUE(TODAY()-1),"yyyy-mm-dd")&"'")

    Alternatively, you could use the FILTER function to pull in only the desired rows.

    However, easiest of all, you could skip all the extra sheets and formulas and simply create filtered views of the original sheet.

  9. Narendra

    Thanks sir for your reply. Actually I don’t want to filter data , what I want to do is, find given text here is “mango” in given column specified and copy entire row than it should add in next empty row in named “mangos inventory” sheet.

    I have searched a lot on net and taking some bits and pieces from verius code I came up with this code ,

    function givenmodi() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var s = ss.getSheetByName(‘Sheet1’);
    var r = s.getDataRange();
    var v = r.getValues();
    var arr = new Array();
    for(var i=0;i<v.length;i++) {
    if(v[i][0]==='Table') {
    arr.push(v[i]);
    }
    }
    var t = ss.getSheetByName('Table');
    t.clearContents();
    t.getRange(1,1,arr.length,arr[0].length).setValues(arr);
    };

    Now this code finding and copying properly but problem is it's not adding in next empty row in " mangos inventory'" sheet. In fact it's easiering sheet and than pasting..

    Hope you understood my problem.

    Thanks & regards

  10. Niranjan

    Hi, Thanks for the Query.
    How to arrange the same in ascending or descending order of a particular column value

  11. DP

    Is it possible to add Arithmetic, Grouping, Etc?

    I tried things like sum(“&SUBSTITUTE(ADDRESS(1,MATCH(“Year”,data!A1:C1,0),4),1,””)&” ), but kept getting errors.

  12. Yep. SUM, GROUP BY, etc. will all work so long as the their conditions are met. For example, SUM requires that all rows in the column be numeric. Anyway, here’s an example that works:

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

  13. Eléonore Breton

    Dave, thanks a million for your post, extremely useful. Would you by chance have a trick to select multiple columns with the same header? For example, I have 3 columns ‘Components’ but using SUBSTITUTE(ADDRESS(1;MATCH(“Components”;’Import’!A1:EE1;0);4);”1″;””) will always give me the first one…
    Best,
    E

  14. Eléonore, I’d recommend not having multiple columns with exactly the same header name. You can think of your spreadsheets like a database, especially when using QUERY. And think of each individual sheet like a table in your database. The first row is the header row in your table, and each column has a unique name. Having a unique name is how SQL and your database work together to identify what column you want. For example, if you have multiple columns named “Components” in a single table, writing a query like “SELECT `Components` FROM my_database” would be ambiguous. It’s unclear which “Components” column is desired.

    The quick and dirty solution is to add a sequential numeric value to each “Components” header name (e.g. Components_1, Components_2, Components_3).

  15. Eléonore Breton

    Dave, yes I totally agree this isn’t a database format for my sheet but it’s the format I export from the tool I use (Jira). Yes I’ll think I’ll have to resort to your quick & dirty solution.
    Thanks a lot again!
    Eléonore

Leave a Reply