Google sheet dynamic WHERE clause for query() statement
- by jason_cant_code
I have a data table like so:
a 1
a 2
b 3
b 4
c 5
c 6
c 7
I want to pull items out of this table by dynamically telling it what letters to pull. My current formula is: =query(A1:B7,"select * where A ='" & D1 & "'"). D1 being the cell I wish to modify to modify the query. I want to be able input into D1 -- a, a,b, a,b,c and have the query work. I know it would involve or statements in the query, but haven't figured out how to make the formula dynamic.
I am looking for a general solution for this pattern:
a -- A = 'a'
a,b -- A = 'a' or A = 'b'
a,b,c -- A = 'a' or A = 'b' or A='c'
Or any other solution that solves the problem.
Edit: So far I have =ArrayFormula(CONCATENATE("A='"&split(D3,",")&"' or ")) this gives A='a' or A='b' or A='c' or for a,b,c. can't figure out how to remove the last or.
