5

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?

tehhowch
  • 9,645
  • 4
  • 24
  • 42
Asaf Nevo
  • 11,338
  • 23
  • 79
  • 154

3 Answers3

12

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)).

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
Robin Gertenbach
  • 10,316
  • 3
  • 25
  • 37
1

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.*' ")
Johan
  • 11
  • 1