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.

Answer 1

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)