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.

2
  • 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. Oct 18, 2019 at 14:49
  • 1
    Cheers for the edits @CorneliusRoemer!
    – Riccardo
    Oct 18, 2019 at 15:10

1 Answer 1

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.

1
  • 1
    Great help! Thanks! :-)
    – Riccardo
    Oct 18, 2019 at 15:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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