Google Sheets Formula to Get Column by Name / Label

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.

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!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.

Related Posts

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