Query with range of values for WHERE clause?

Question

I have a Google Spreadsheet and I want to run a QUERY function. But I want the WHERE statement to check a series of values. I'm basically looking for what I would use an IN statement in SQL - what the IN equivalent in Google Spreadsheets? So right now I have:

=QUERY(Sheet1!A3:AB50,"Select A,B, AB WHERE B='"& G4 &"'")

And that works. But what I really need is the equivalent of:

=QUERY(Sheet1!A3:AB50,"Select A,B, AB WHERE B='"& G4:G7 &"'")

And of course, that statement fails. How can I get the where against a range of values? These are text values, if that makes a difference.

Answer 1

Great trick Zolley! Here's a small improvement.

Instead of:

=CONCATENATE(G3,"|",G4,"|",G5,"|",G6,"|",G7)

we can use

=TEXTJOIN("|",1,G3:G7)

That also allows us to work with bigger arrays when adding every cell into the formula one by one just doesn't make sense.

UPD:

Going further I tried to compose two formulas together to exclude the helping cell and here we go:

=QUERY(Sheet1!A3:AB50,"Select A,B, AB WHERE B matches '^.(" & TEXTJOIN("|",1,G3:G7) & ").$'")

Used this in my own project and it worked perfectly!

Answer 2

Although I don't now the perfect answer for that, I could find a workaround for the problem, which can be used in small amount of data (hope that's the case here :) )

First step: You should create a "helper cell" in which you concatenate the G4:G7 cells with a "|" character:

=CONCATENATE(G3,"|",G4,"|",G5,"|",G6,"|",G7) - Let's say it's the content of the cell H2.

Now, you should change your above query as follows:

=QUERY(Sheet1!A3:AB50,"Select A,B, AB WHERE B matches '^.*(" & H2 & ").*$'")

This should do the trick. Basically we the "matches" operator allows the use of regular expressions, which also allow construction using the | symbol.

As I said, it's a workaround, it has drawbacks, but I hope it'll help.