2

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.

  • Denoting the content of the sheet at row i and column j as S(i,j), you want to return all the S(i,j) where i satisfies: S(i,A)="Speed" and j satisfies S(1,j)=N. N can vary from 4...10. Correct? If that's the case we should probably rewrite the question to make that clearer. – Cornelius Roemer Oct 18 '19 at 14:49
  • 1
    Cheers for the edits @CorneliusRoemer! – Riccardo Oct 18 '19 at 15:10
2

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.

|improve this answer|||||
  • 1
    Great help! Thanks! :-) – Riccardo Oct 18 '19 at 15:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.