CODEWITHSUNDEEP
google-sheets-api
Mindfreak
Mindfreak

Reputation: 17

Query function to pull data based on multiple values within one cell

I would like to ask how to use Query function where one of the cell contains multiple comma separated values

For example:-

To pull col2 values when Col 1 is B, it would be straightforward.

Col 1 Col 2
A 1
B 2
C 3
D 4 
=QUERY(A1:B3,"select B where A = '"&D3&"' ",0)

D3 cell value is B

Similar to how we have IN clause in SQL, I would like to pull data from col 2 when col1 values are B,C,D.

Would it be possible to concatenate the results in one row as well?

Upvotes: 0

Views: 799

Answers (1)

NightEye
NightEye

Reputation: 11184

Try this one:

=join(", ", QUERY(A1:B4,"select B where "&CONCATENATE("A = '", join("' or A = '", SPLIT(D4, ",")), "'"),0))

Where D4 = B,C,D

Sample Output:

Sample

Note:

  • The values on column B below are the value of the formula:

CONCATENATE("A = '", join("' or A = '", SPLIT(D4, ",")), "'"),0))

Since there was no in statement in sheets query (not that I have encountered), what I did was split those said values in column D and have them format like the multiple or statements which are equal to a single in statement. This is a workaround and should do what you wish to achieve.

Upvotes: 1

Related Questions