Google Sheets QUERY Function: Select Columns by Name

by

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)

More Related Contents:

  1. ArrayFormula of Average on Infinite Truly Dynamic Range in Google Sheets
  2. Query is ignoring string (non numeric) value
  3. What factors determine the memory used in lambda functions?
  4. Col BY causing parse error in Google sheets query
  5. QUERY and order by the first date of column
  6. How to create a runing total formula that expands automatically when new rows are added to my spreadsheet [duplicate]
  7. List comprehension vs. lambda + filter
  8. Java 8 Streams: multiple filters vs. complex condition
  9. Import data into Google Colaboratory
  10. Conditional formatting based on another cell’s value
  11. How to stop / freeze / pause volatile RAND / RANDBETWEEN / RANDARRAY?
  12. How to SUM duration in Google Sheets?
  13. ArrayFormula and “AND” Formula in Google Sheets
  14. SUMIFS function in Google Spreadsheet
  15. ARRAY_LITERAL, an Array Literal was missing values for one or more rows
  16. How to compare dates or date against today with query on google sheets?
  17. Repeat whole row N times based on column value in Google Sheets
  18. Download link for Google Spreadsheets CSV export – with Multiple Sheets
  19. IMPORTXML and right XPath from Bloomberg price [duplicate]
  20. How to list all permutations without repetition?
  21. How to use arrayformula with formulas that do not seem to support arrayformulas?
  22. Repeat each row N times in Google Sheets
  23. Stacking multiple columns on to one?
  24. New Google spreadsheet Concatenate limit 50000 characters
  25. Count cells that contain any text
  26. How to make a range repeat n-times in Google SpreadSheet
  27. Can the google spreadsheet ‘query’ function be used in google apps script?
  28. In ARRAY_LITERAL, an Array Literal was missing values for one or more rows – indirect formula error in google spreadsheet
  29. Google Sheet API v4
  30. How to Print sheet/range using .gs script in Google Sheets?

Leave a Comment