CODEWITHSUNDEEP
sqlgoogle-sheets
Unknown Coder
Unknown Coder

Reputation: 6731

Query with range of values for WHERE clause?

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.

Upvotes: 3

Views: 16559

Answers (3)

Dmytro Hladyshevskyi
Dmytro Hladyshevskyi

Reputation: 76

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!

Upvotes: 6

user3598166
user3598166

Reputation: 1

I had the same question and came across your question in my research. Your Concatenate idea got me thinking. The problem is that the range that I'm using in my where clause is dynamic. The number of rows changes. That got me onto the Join function which lead me to this:

=(QUERY('Sheet1!A3:AB50,"select A where B = "& JOIN(" OR B = ",ARRAY_CONSTRAIN($A$5:A,COUNTIF($G$4:$G,">0"),1)) &"")

COUNTIF counts the number of rows with data. I'm using numbers in this range so maybe "!= ''" would be more appropriate than ">0". ARRAY_CONSTRAIN creates an array with with cells that only has data. JOIN turns the range into query language for the where clause.

Need to start where clause with basically same text that is in delimeter in the JOIN function. Note that I am using numbers so I don’t need ‘’ around values. This is what works in my spreadsheet and I hope it helps.

Upvotes: -2

zolley
zolley

Reputation: 6060

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.

Upvotes: 4

Related Questions