Google Sheets – How to Use Column Headers in a QUERY

formulasgoogle sheetsgoogle-sheets-query

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?

Best Answer

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.

Related Topic