Google Sheets - query contains array

Question

I have a range with a name column.

I also have a list of names chosen be presented.

I want to run a query which will select data from this range only if it names is inside this list:

Select C Where A "is in the selected array of names"

What would be the right way of doing that?

Answer 1

Suppose the array of names is D2:D10; then you could use the query string

"select C where A = '" & join("' or A = '", D2:D10) & "'"

This creates a long query string A = 'name' or A= 'another name'...

If the range of names is variable (e.g., you want all names listed in D2:D), replace D2:D10 with filter(D2:D, len(D2:D)).

Answer 2

QUERY doesn't have a concept of in.

If you have your list of names in column A, say

  A  B  C
1 1     A
2 2     B
3 3     C

And you name of names to find in D1:D2

  D
1 A
2 C

You can build a regex to match the names you want to find in E1

=QUERY(C1:C3, "SELECT A WHERE A MATCHES '(" & JOIN("|", D1:D2) & ")'")

which returns

  E
1 A
2 C

Answer 3

Or blend both methods, and set it so you can use additional filters...

=query(A1:A, "SELECT A,B,C WHERE (A MATCHES '("& JOIN("|", filter(D1:D, LEN(D1:D))) &")' OR C = 'Criteria' ")

Answer 4

If you want to provide the list of names in the query rather than another range you can use matches

Exact match:

=QUERY(A1:C3; "SELECT C WHERE A matches 'name1|name2|name3' ")

Match part of a name:

=QUERY(A1:C3; "SELECT C WHERE A matches '.*name1.*|.*name2.*' ")