Google Sheets QUERY Function: Select Columns by Name-CodePudding

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

A sample spreadsheet with desired output can be found

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, ))

CodePudding user response：

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

Name

_BETTERQUERY

Usage example

=_BETTERQUERY({A1:C},"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

=ARRAYFORMULA(IFERROR(QUERY(range,LAMBDA(b,JOIN(,IFNA(REGEXEXTRACT(b,".*&(.*)"),b))&QUERY(REGEXEXTRACT(SUBSTITUTE(better_query,"&"&FLATTEN(REGEXEXTRACT(better_query,REGEXREPLACE(REGEXREPLACE(REGEXREPLACE(better_query,"([\[\]()])","\\$1"),"(matches.*?)'(.*?)'","$1['$2']*"),"&(.*?)&","&($1)&")))&"&","Col"&MATCH(FLATTEN(REGEXEXTRACT(better_query,REGEXREPLACE(REGEXREPLACE(REGEXREPLACE(better_query,"([\[\]()])","\\$1"),"(matches.*?)'(.*?)'","$1['$2']*"),"&(.*?)&","&($1)&"))),QUERY(range&"","limit 1",0) ,0),LAMBDA(a,COUNTIFS(a,a,SEQUENCE(ROWS(a)),"<="&SEQUENCE(ROWS(a))))("Col"&MATCH(FLATTEN(REGEXEXTRACT(better_query,REGEXREPLACE(REGEXREPLACE(REGEXREPLACE(better_query,"([\[\]()])","\\$1"),"(matches.*?)'(.*?)'","$1['$2']*"),"&(.*?)&","&($1)&"))),QUERY(range&"","limit 1",0) ,0))),".*Col\d (.*)"),"offset "&len(REGEXREPLACE(better_query,"[^&]",))/2-1))(REGEXEXTRACT(SUBSTITUTE(better_query,"&"&FLATTEN(REGEXEXTRACT(better_query,REGEXREPLACE(REGEXREPLACE(REGEXREPLACE(better_query,"([\[\]()])","\\$1"),"(matches.*?)'(.*?)'","$1['$2']*"),"&(.*?)&","&($1)&")))&"&","Col"&MATCH(FLATTEN(REGEXEXTRACT(better_query,REGEXREPLACE(REGEXREPLACE(REGEXREPLACE(better_query,"([\[\]()])","\\$1"),"(matches.*?)'(.*?)'","$1['$2']*"),"&(.*?)&","&($1)&"))),QUERY(range&"","limit 1",0) ,0),LAMBDA(a,COUNTIFS(a,a,SEQUENCE(ROWS(a)),"<="&SEQUENCE(ROWS(a))))("Col"&MATCH(FLATTEN(REGEXEXTRACT(better_query,REGEXREPLACE(REGEXREPLACE(REGEXREPLACE(better_query,"([\[\]()])","\\$1"),"(matches.*?)'(.*?)'","$1['$2']*"),"&(.*?)&","&($1)&"))),QUERY(range&"","limit 1",0) ,0))),".*?Col\d ")),headers),query(range,better_query,headers)))

Notes

This function is built on top of QUERY, so you can use it exactly as QUERY. If you want to refer to the columns with their headers instead, you must enclose the range in curly brackets {range} (the first row of the range must be the header) and enclose the column header between two ampersands &col_header&. (See example usage above)
The headers parameter is not optional since Named Functions do not currently allow optional parameters.