Google Sheets Query – Making Selected Column Depend on Formula

google sheetsgoogle-sheets-query

I would like to use a normal QUERY with where operating in the usual way where A = 'Speed' but with the selected column being determined based on a formula. Specifically, I want to return only the column that has a certain user-determined number in row 1. Row 1 has numbers from the range 4 to 10 without duplicates but in a variable order.

My sample data is:

         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

The query should look something like:

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

I'm looking for what I should put in place of xxx depending on the values of row 1.

Best Answer

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.

Related Topic