Google Sheets QUERY Function: Select Columns by Name

Question

I have a Google Sheet with named ranges that extend beyond columns A-Z. The name ranges have header rows. I would like to use the QUERY function to select columns by their header labels.

My formula is like this:

=QUERY(NamedRange,"SELECT AZ, AX, BM where BB='student' ORDER BY BM DESC",1)

Answers to other questions on StackOverflow, like that accepted here, haven't worked. Another answer found here on Google's support page doesn't work for columns beyond Z.

How can I use the QUERY function and select columns beyond column AA by their header labels?

DESIRED OUTPUT / SAMPLE DATA

A sample spreadsheet with desired output can be found here.

share a copy / sample of your sheet with an example of the desired output — player0, Nov 10 '22 at 18:00
NB: I'm trying to avoid using `IMPORTRANGE` for security reasons though a user responding to the first linked question above states that it works as a go-around. For similar reasons, I'm also trying to avoid using Google AppsScript as I have multiple spreadsheets that need regular updating, some with new columns hence the need for selecting columns by header name. — Catalyx, Nov 10 '22 at 18:10

player0 · Accepted Answer · 2022-11-24T11:41:17.830

5

you can transpose it and header row becomes a column. then:

=TRANSPOSE(QUERY(TRANSPOSE(A1:C), "where Col1 matches 'bb header|student'", ))

where A1:C is your named range (including header row)

update:

=QUERY({AI1:AK6}, "select Col2,Col3 where Col1='Jones'", 1)

dynamically:

=LAMBDA(p, t, s, QUERY({AI1:AK6}, 
 "select Col"&t&",Col"&s&" 
  where Col"&p&"='Jones' 
  order by Col"&t&" desc", 1))
 (MATCH("principal", AI1:AK1, ), 
  MATCH("teacher",   AI1:AK1, ), 
  MATCH("student",   AI1:AK1, ))

WHY LAMBDA ?

LAMBDA is a regular GS function that allows substituting any type of ranges with custom strings. generic example of simple lambda: =LAMBDA(x, x+5)(A1) which is in old terms: =A1+5 therefore you can understand it as x being a placeholder for A1. one more example: =IF((A1+1)>(B1+1), B1+1-A1+200, B1+1*A1+20) contains a lot of repeating cell references so we can refactor it like: =LAMBDA(a, b, IF((a+1)>b, b-a+200, b*a+20))(A1, B1+1) this comes especially handy with more advanced formula stacking when instead of repeating the whole fx multiple times we can wrap it in Lambda to shorten it and make it cleaner

you can have as many LAMBDAs as you wish:

here, just for fun, one more example... with lambda:

and without lambda: pastebin.com/raw/BREgC9La

(from: stackoverflow.com/a/74380299/5632629)

edited Nov 24 '22 at 11:41

answered Nov 10 '22 at 18:07

player0

124,011
12
67
124

That will work if all the columns are of the same type, i.e., all columns contain text, but I don't think that works if one column contains numbers, another contains text, and a third contains dates. – doubleunary Nov 10 '22 at 18:12
@doubleunary could be modded in various ways, sadly OP did not include a representative sample (yet) – player0 Nov 10 '22 at 18:18
Could you adjust the code to filter (e.g., ```'aa header'="c"```) the table and sort (e.g. ```ORDER BY 'student'```) the results? – Catalyx Nov 10 '22 at 18:25
@Catalyx can you share a copy / sample of your sheet with an example of the desired output? – player0 Nov 10 '22 at 18:27
1

@player0 Your solution seems to work. (Thanks.) I was looking to complete the query with filtering and sorting aspects. I can create a sample spreadsheet if that would help. – Catalyx Nov 10 '22 at 18:34
@Catalyx a sample would really help to narrow down all possibilities of what could go wrong – player0 Nov 10 '22 at 19:14
I see ```LAMBDA``` is a custom Google Sheets function that wraps around the ```QUERY``` function and then there is a ```MATCH``` function appended. Can you describe briefly what the formula does / how it works? Thanks. – Catalyx Nov 10 '22 at 21:17
@Catalyx sure, LAMBDA is a regular GS function that allows substituting any type of ranges with custom strings. generic example of simple lambda: `=LAMBDA(x, x+5)(A1)` which is in old terms: `=A1+5` therefore you can understand it as `x` being a placeholder for A1. one more example: `=IF((A1+1)>(B1+1), B1+1-A1+200, B1+1*A1+20)` contains a lot of repeating cell references so we can refactor it like: `=LAMBDA(a, b, IF((a+1)>b, b-a+200, b*a+20))(A1, B1+1)` – player0 Nov 10 '22 at 21:42
@Catalyx this comes especially handy with more advanced formula stacking when instead of repeating the whole fx multiple times we can wrap it in Lambda to shorten it and make it cleaner – player0 Nov 10 '22 at 21:43
@Catalyx https://i.stack.imgur.com/FTwpn.png – player0 Nov 10 '22 at 21:55
@player0 Thanks for the additional explanations. I've tried extending the formula to four columns or more and it returns ```Formula parse error```. The error appears in the sample spreadsheet. I also tried to pass the query with the ```CONCATENATE``` function with the same result. Is three the maximum number of columns I can query while using the ```LAMBDA``` function? If so, is there a way around this? If necessary, let me know if I should start another question. Thanks. – Catalyx Nov 11 '22 at 00:00
1

@Catalyx you can have as many as you wish. try like this: https://i.stack.imgur.com/20wBS.png – player0 Nov 11 '22 at 00:06
1

@Catalyx or: https://i.stack.imgur.com/inHDg.png – player0 Nov 11 '22 at 00:09
1

@Catalyx here, just for fun, one more example: with lambda: https://i.stack.imgur.com/k7pBE.png and without lambda: https://pastebin.com/raw/BREgC9La from: https://stackoverflow.com/a/74380299/5632629 – player0 Nov 11 '22 at 04:44

z'' · Answer 2 · 2022-11-13T01:50:15.100

You can try the below Named Function I created a while ago. Import from here

Name

_BETTERQUERY

Usage example

=_BETTERQUERY(A1:C10,"select `name` where `age` > 18",1)

Formula description

Runs a Google Visualization API Query Language query across data. It supports the usage of column headers.

Argument placeholders

range
better_query
headers

Formula definition

=QUERY({range},REGEXREPLACE(REDUCE(better_query,
REGEXEXTRACT(better_query,REGEXREPLACE(REGEXREPLACE(better_query,
"([()\[\]{}|^.+*$?])","\\$1"),"`(.*?)`","`($1)`")),LAMBDA(acc,cur,
SUBSTITUTE(acc,cur,"Col"&MATCH(cur,ARRAY_CONSTRAIN(range,1,9^9),0),1))),
"`(Col\d+)`","$1"),headers)

Notes

This function is built on top of QUERY, so you can use it exactly as QUERY. When referring to the columns with their header, make sure that the first row of range is the header and in better_query enclose the column header between two backticks `col_header`. (See example usage above)
The headers parameter is not optional since Named Functions do not currently allow optional parameters.

If you want to understand more about how this works. See How to Use Column Names in QUERY

score 1 · Answer 3 · answered May 30 '23 at 19:33

I stumbled on a nice solution using a custom function and some fancy app script.

QUERY & MYSELECT functions, Select by Column Names (Headers, Labels) - Google Sheets

Here is an example of how this solution is called:

=QUERY({'Sales Orders'!A1:G}, MySelect("SELECT [Sales Rep], [Item], [Sales] WHERE [Sales] > 250", 'Sales Orders'!A1:G), 1)

Although I have written many custom functions myself I decided to develop a solution using native google functions.

My solution uses the SCAN() function and a Named Function to make it easier to use:

=QUERYBYNAME("SELECT [Sales Rep], [Item], [Sales] WHERE [Sales] > 250",A1:G1,A1:G)

Here is a link to a view only spreadsheet with the Named Functions as well as the custom function script. Feel free to make a copy.

QueryByName

Querying using column names also solves the problem when columns are inserted or removed.

I was trying to accomplish this without using custom functions or Google Apps Script (GAS), for various reasons. The native ```LAMBDA``` function permits creating temporary "functions" within a cell, per the selected solution, without having to grant GAS permissions, amongst other features. Thanks. — Catalyx, Jun 01 '23 at 01:25