3

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

enter image description here

A sample spreadsheet with desired output can be found here.

Improve this question
2
  • share a copy / sample of your sheet with an example of the desired output Commented Nov 10, 2022 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. Commented Nov 10, 2022 at 18:10

3 Answers 3

Reset to default
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)

enter image description here

update:

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

enter image description here

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

enter image description here

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

enter image description here

you can have as many LAMBDAs as you wish:

enter image description here

enter image description here

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

enter image description here

and without lambda: pastebin.com/raw/BREgC9La

enter image description here

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

Improve this answer
Sign up to request clarification or add additional context in comments.

14 Comments

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.
@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 you can have as many as you wish. try like this: i.sstatic.net/20wBS.png
@Catalyx or: i.sstatic.net/inHDg.png
@Catalyx here, just for fun, one more example: with lambda: i.sstatic.net/k7pBE.png and without lambda: pastebin.com/raw/BREgC9La from: stackoverflow.com/a/74380299/5632629
|
5

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

  1. 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)

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

Improve this answer

5 Comments

Seamless! Thank you. Curious, why the leading underscore?
I like to prepend my named functions with an underscore for 2 reasons: (1) avoid conflict with potentially existing functions. (2) autocomplete allows me to quickly see which named functions I have in my spreadsheet by simply typing =_ in an empty cell. (I just realized my link is broken because it points to my old domain, I fixed it)
IMPORTANT: Verify if you have correct function parameter separator in your sheet (should be ,). If not, change your region in File → Settings. Without that, you may break your sheet during named function import.
Do you know how to support new line in column name?
Ok I got it, if you want support for multiline columns. Replace `(.*?)` with `([\s\S]*?)`
1

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)

enter image description here

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.

Improve this answer

2 Comments

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.
Similar to "z" answer. Verify if you have correct function parameter separator in your sheet (should be ,). If not, change your region in File → Settings. Without that, you may break your sheet during named function import.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.