Google Sheets Query, filtering by rows & columns?

by Riccardo   Last Updated October 18, 2019 15:03 PM - source

Is there a way to use QUERY formula in Google Sheets, filtering results by row AND columns? Basically I need to filter results using two filters. Columns must be filtered by a number from 4 to 10, but column position may vary, so our reference is the value in row one.... Can't rely on column position as it may change....

For instance:

         A          B        C                  D        E  
-----------------------------------------------------------------
   1 | -----        5        6                  7        8
   2 | Speed        Slow     Fair               Medium   Fast  
   3 | Comfort      Bad      Barely acceptable  Bla      Bla,bla                
   4 | Reliability  Low      Acceptable         Blabla   Blablabla 

Then something like:

=QUERY('Sheetname'!A1:E; "Select xxx where A = 'Speed')

In the above sample, xxx should filter columns using to the numeric value in row 1....

Tags : google-sheets


Answers 1


Determine column alphabet letter as follows: =char(64+MATCH(N,1:1,0)) where you substitute the number you want for N=4,5...10.

Then add that to the query string formulaically. Done:

=QUERY(A1:D4,"select "&char(64+MATCH(5,1:1,0))&" where A = 'Speed'")

Important limitation for generalisations: this only works for column index up to 27, because it can't form column names like AA etc.

Cornelius Roemer
Cornelius Roemer
October 18, 2019 14:58 PM

Related Questions



Adding missing dates to Google Sheets

Updated September 20, 2019 15:03 PM



Copying values between google sheets

Updated July 26, 2018 21:03 PM