Can I use column headers in a =QUERY?

Question

Reading the documentation for the =QUERY function^{1, 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?

Answer 1

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:

I've used the following data set:

If set to be 0, then no header will be used, leaving:

If set to be 1, then the first row will be used, leaving:

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

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.

Answer 2

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'")

• Example in Google Sheets
• Detailed explanation of the formula

Answer 3

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.

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

Answer 5

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.