Can the column number in a google sheets WHERE clause be a variable?

user_11192835 0
Viewed 609

Can the column number in a where clause be a variable?
Here's a simple query:

=query(MasterData!B2:BR,"Select B where AJ <>''",1)

Is it possible to swap out AJ with something like: 

=match($B$1,MasterData!2:2,0)

I've tried this but it doesn't work: 

=query(MasterData!$B$2:$BR,"Select B where 'Col'& match($B$1,MasterData!2:2,0) <>''",1)

B1 is a drop down menu and AJ is Col36 The idea is to have the column the where clause evaluates driven by a menu.

Example Data: https://docs.google.com/spreadsheets/d/1L01n2U3cJgrt5ILKvv19cwYcBnc-lP7g0dWxrb8knNM/edit?usp=sharing

In this example, I'm trying to fix the formula in the "Query with variable column in WHERE" tab, cell A16

Thanks for the help!

google-sheets where-clause variables
1 Answers

EDIT (Following OP's presented sample sheet)

IN SHORT

Can the column number in a google sheets WHERE clause be a variable?

Yes it can

You need to comply with the following

  • Use curly quotes {} and use Col1, Col2, Col3 etc
  • After Col use "&.......&" where ...... is anything that returns the column number.

As an example

=query({C2:H11},"Select Col1, Col5 where Col"&IF(A1=2,2,"xxx")&" <>''",1)

For this particular question as stated by the OP

You could use the formula (ranges and cells as in your test sheet):

=query({$C$2:$F},"Select Col1 where Col"&B2&" <>''",1)

OR

Change your Data validation drop-list in cell A2 to 2,3,4 and use A2 in your query instead of B2.

OR

use any formula within your where query clause as long as you get the Column number there.

Original answer

Assuming the match formula itself is correct (giving the correct column number), you would need to correct your query syntax to:

=query({MasterData!$B$2:$BR},"Select Col1 where Col"& match($B$1,MasterData!2:2,0)&" <>''",1)

You cannot use letters as columns and at the same time use Col1, Col2 etc.
So we use the curly {} quotes and everything can be referred as Col1, Col2, Col36

As for when referencing the cell (as B1 here) the syntax should be
" & YourCellFormulaHere & " .

user_15277800
Related