23

Reading the documentation for the =QUERY function1, 2, 3, some of it seems to imply that I should be able to use column headers directly in my query. For example, the =QUERY function takes a third optional parameter, HEADERS, which lets you specify a number of header rows.

Most of my queries would be a lot prettier if I could use column headers, thus not having to use column indexes, but I'm not able to make it work.

Example:

A      B         C
---------------------
Name   Phone     City
Vidar  12345678  Oslo
Rupert 32165487  Berlin

I'm able to query this by using column indexes:

=QUERY(Sheet1!A1:C; "select A, B, C where A = 'Vidar'"; 1)

... but not using column headers:

=QUERY(Sheet1!A1:C; "select A, B, C where Name = 'Vidar'"; 1)

... this gives me Error: Invalid query: Column [Name] does not exist in table

Is it at all possible to use column headers like this? If not, what is the purpose of the HÈADERS parameter?

6

The third parameter you speak of in the QUERY function, is meant to control the headers. If set to be -1, then the Google Spreadsheet will depict its own choice in choosing headers, based on the data available: enter image description here

I've used the following data set: enter image description here

If set to be 0, then no header will be used, leaving: enter image description here

If set to be 1, then the first row will be used, leaving: enter image description here

If set to be 2 or -1 or left blank, then the first two rows will be used, leaving: enter image description here

Using the headers, the way you did in your example, isn't possible. The closest thing would the usage of the QUERYfunction, described in this answer. There a quasi column name is used.

4
  • Ah, OK, so if HEADERS > 0 then the given number of header rows will be returned by =QUERY on top of the query results, right? That makes sense - I thought HEADERS only specified rows to ignore in the query. Feb 26 '14 at 17:35
  • @Vidar The OFFSET option can be used to ignore first rows. Feb 26 '14 at 18:00
  • In my experience setting the headers parameter to 0 does not do what Google docs describe or what is depicted here. It basically does nothing (i.e., same as -1) as far as I can tell. Has anyone else noticed this?
    – user24601
    May 13 '18 at 13:29
  • 8
    I believe the intention of the original poster was not about getting headers in the report but rather about using header names in the query statement rather than having to use column indexes (as in A or B or C).
    – Farrel
    Jun 10 '18 at 16:51
10

Is it at all possible to use column headers like this?

Yes, it is possible. First, you need to use MATCH to get the column number of the column whose value matches 'Name'. Then you need to use ADDRESS to get the cell reference. Finally, you need to use SUBSTITUTE to remove the row number from the cell reference.

=QUERY(Sheet1!A1:C,"SELECT A, B, C where "&SUBSTITUTE(ADDRESS(1,MATCH("Name",Sheet1!A1:C1,0),4),1,"")&" = 'Vidar'")
4
  • 3
    Please see the OP "Most of my queries would be a lot prettier if I could use column headers". Do you truly believe that your answer makes the query prettier?!? Sure your solution WORKS, but it doesn't address the OP's intent at all - that's a hot mess you're proposing there.
    – Tom Auger
    Feb 10 at 20:47
  • Seems pretty clear that the OP wants to use in a "where" clause the values of the cells in the first row (which he refers to as "headers") in lieu of the column indices. See his example: =QUERY(Sheet1!A1:C; "select A, B, C where Name = 'Vidar'"; 1) I welcome your suggestions for improving my solution. Please demonstrate how you would achieve the desired result in a "prettier" way. Feb 12 at 2:10
  • Helpful even if it's not prettier than column headers... Might not be prettier but it sure is a lot more portable when you add more columns to your data.
    – blak3r
    Apr 28 at 14:27
  • OP: Can I use column names? TL;DR: No
    – yoyo
    Nov 28 at 21:46
4

While an old post, I wanted to add my solution to the mix. You can use column names which I find to be more useful. This way you don't have to edit your query function calls when you insert or remove columns from the source data.

I have seen others use the match and substitute, I implemented something a little different to simply the query function call.

First - Create a lookup table of all of your column names like this. My lookup table starts in Column E of a 'Config' sheet only because i have other items on the sheet, it could easily be placed in its own sheet.

  • Column 1 (Column Name)

    =TRANSPOSE( 'Source Data'!1:1 )
    
  • Column 2 (Column #)

    =arrayformula( row( E2:E ) - 1 )
    
  • Column 3 (Column Letter)

    =arrayformula( if( int( F2:F / 26.5 ) > 0, char( int( F2:F / 26.5 )  + 64), "" ) & char( (F2:F - (int( F2:F / 26.5 ) * 26 ) ) + 64 ) )
    

    I'm open to refinements to convert a column number to a letter. This formula is limited in that it only handles 78 columns. More than enough for me though.

Now your Query function call would look something like this:

=query( 'Source Data'!$A:$L,
"Select " & " " &
vlookup( "Date", Config!$E:$G, 3, false ) & ", " &
vlookup( "Dev Query Engine Conn Count", Config!$E:$G, 3, false ) & ", " &
vlookup( "Dev Conn Limit Retry Count", Config!$E:$G, 3, false ) & ", " &
vlookup( "Dev Max Sequential Retry in One Minute", Config!$E:$G, 3, false ) & ", " &
vlookup( "Dev Conn Limit Errors", Config!$E:$G, 3, false ) & " " &
"where " & vlookup( "Display in Graph.", Config!$E:$G, 3, false ) & "=TRUE " &
"order by " & vlookup( "Date", Config!$E:$G, 3, false ) & " desc "
)

Keep it nicely formatted and it isn't bad to manage at all. The only thing you will suffer is if you change the column index of your vlookup range. But this should not change often, if at all, considering it is a helper range.

2
  • This answer is what I was looking for, but I needed it a little longer, so I wrote a query that supports up to 3 letters =arrayformula( if(F2:F < 702, "", CHAR(MOD((F2:F / 26 / 26) - 1, 26) + 65)) & if(F2:F < 26, "", CHAR(MOD((F2:F / 26) - 1, 26) + 65)) & CHAR(MOD(F2:F, 26) + 65) ) In my case, I start at index 0, so I had to have the 2nd column actually be - 2
    – JRJurman
    May 27 '20 at 2:33
  • Using a named range for Config!$E:$G would tidy this up a little further. And you could add another named range to look up the 3.
    – yoyo
    Nov 28 at 21:48
4

Old question, but I think this solution may be worth it.

You can use a custom script function which retrieves the column index (i.e. A, B, C...) using a header name, allowing to do something like:

=query('MySheet'!A2:Z; 
   "select "&colIndex("'MySheet'!A1:Z1"; "Car name")&"  
    where "&colIndex("'MySheet'!A1:Z1"; "Car color")&"='Blue'"))

With colIndex function:

function colIndex(a1NotationStr, header){
  var range = SpreadsheetApp.getActiveSpreadsheet().getRange(a1NotationStr)
  var row = range.getValues()[0]

  //parse the header row and stop at first header matching our search
  //use the related cell A1Notation and remove all numbers (i.e. 'A45' becomes 'A')
  for(var i=0; i<row.length; i++){
    var currHeader = row[i]
    if(currHeader == header){
      return range.getCell(1, i+1).getA1Notation().replace(/[0-9]/g, '');
    }  
  }
}

Which may be easier to use and read.

3

Here is my solution:

if you have a column called "city" create a new cell somewhere with the index of that cell:

   A          B               C        ...         Y               Z        
 --------- ------------ ------------ ------ --------------- --------------- 
  Name        Phone        City        ...    Column Name     Column Index  
  Vidar      12345678     Oslo         ...    Name           A              
  Rupert     32165487     Berlin       ...    Phone          B              
  Sahid      32165487     Colombo      ...    City           C              

You can then create a named range for cell Z:2 called : "name" , Z:3 = "phone", Z:4 = "city"

in your query you can then write:

=QUERY( Sheet1!A1:C,"SELECT A, B, C where "&name&" = 'Vidar'"

You can combine this with the address match solution, to not have to keep track of rearranged columns.

1
  • 2
    To clarify, this answer shows that you can use Named Ranges somewhat cleanly in the query, so use them to map a nice name to the column letter that QUERY() requires. You must create a named range for each column name, and since named range names are global this is best left for small workbooks. FYI, column Y in this answer has no meaning, except to maybe use it to populate Column Z via the SUBSTITUTE, ADDRESS, MATCH method.
    – Adam
    Dec 30 '20 at 1:57
3

I was searching for a suitable answer to this question, also, since using column letters like A, B, C is not good for flexibility and becomes a maintenance hassle. Every time your source data columns are reordered, or a new column comes in, you have to update your QUERY().

So I came up with this solution. It does require a short Apps Script function, but the benefit is that you get quite a clean, readable, maintainable query.

Using Google Sheets QUERY with column headings/labels instead of letter

You use it like this:

=QUERY(A9:C13, QueryByName(A9:C9, "SELECT `Name`, `country` WHERE `age`>7"))

In order to use this function, open your Google Sheet, click Tools > Script Editor and paste in the following function.

/**
 * Custom sheet function for allowing the use of column headings 
 * instead of column labels inside the QUERY() function
 * 
 * Example:
 * =QUERY(A1:C13, QueryByName(A1:C1, "SELECT `name`, `country`"))
 * 
 * The first range (in the QUERY() function) is your data source, 
 * and the range given in QueryByName() should be the header row
 * that lists out your headings. Consider giving both ranges
 * a name for easy reference.
 * 
 * The third parameter 'firstColumnIndex', is only used if your
 * source data does NOT start in column A. Otherwise you can leave
 * it blank. If it starts in column B, set this to 1, if it starts 
 * in C, set to 2, etc.
 * You can make this dynamic by setting it to COLUMN(x) where x is
 * the top-left cell of your source data (or even the entire range).
 * 
 * Function by Simon East
 * <https://webapps.stackexchange.com/a/155456/20364>
 */
function QueryByName(columnHeadings, queryString, firstColumnIndex = 0) {

  // Lowercase all heading names so that case errors are accepted
  const headings = columnHeadings[0].map(item => item.toLowerCase());

  // Use a regular expression to get a list of all strings inside `backticks`
  let fieldNamesInQuery = queryString.matchAll(/`([^`]+)`/g);

  // Loop over each one and replace it with the column letter
  for (let [nameWithTicks, name] of fieldNamesInQuery) {
    let columnLetter = columnNumberToLetter(headings.indexOf(name.toLowerCase()) + firstColumnIndex + 1)
    if (!columnLetter)
      throw new Error('The field named ' + nameWithTicks + ' was not found in the header range you specified.');
    queryString = queryString.replace(nameWithTicks, columnLetter);
  }

  return queryString;
  
  // Converts 1 to A, 2 to B, 27 to AA, etc...
  function columnNumberToLetter(column) {
    var temp, letter = '';
    while (column > 0) {
      temp = (column - 1) % 26;
      letter = String.fromCharCode(temp + 65) + letter;
      column = (column - temp - 1) / 26;
    }
    return letter;
  }

}

If you experience any bugs, feel free to post a comment here.

1

The two cleanest ways to implement this:

  1. Use {} around the query range so you can use columns instead of letters. E.g.:
    =Query({A:B},"Select Col"&Match("Header Name",1:1,0))
    Where A:B is where your query data lives and 1:1 is the where the headers live.

  2. Name the range you want to pull from. You can reference the named range within the query.

This site has a pretty great breakdown of how to implement: https://francofolini.com/2020/08/04/reference-columns-by-name-in-google-sheets-query/

0

You can actually get around to using column headers by splitting the Query formula and using other formula's to automatically get the desired column names from a list.

For example if you have a table in range A1:E15 with headers "H1, H2, H3, H4, H5", and you'd like to only get columns H3 & H5:

  1. Store the desired headers (H3 & H5) in another table/range as a list - lets say this range is G1:G2

  2. Use MATCH formula along with TextJoin formula to generate an concatenated string like Col3, Col5

    =TextJoin(", ",TRUE,ArrayFormula(IFERROR("Col"&MATCH(G1:G6,$A$1:$E$1,0),"")))
    
    • Lets say this was in cell H1
  3. You can refer to this cell in your Query formula like below

    =QUERY({A1:E20},"SELECT "&H1&" WHERE Col2='w'")
    

You can see it in action in below screenshot:
Sample Screenshot

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.