Can I query a range of cells that match the criteria of another range of cells in Google Spreadsheets?

Question

I want to query a range of cells, using another range as the criterion. Is this possible?

Something like this:

QUERY('Current Schedules'!A5:F154, "Select F where A = '"&A13:A15&"' ")

Where A13:A15 are three names. I would preferably replace the &A13:A15& with another query that returns an array of names, but I need to get this working with an array of criteria first.

Answer 1

I know, I'll use regular expressions:

=query('Current Schedules'!A5:F154, "select F where A matches '" & join("|", A13:A15) & "'")

E.g., if A13:A15 have the names Jim, Mary and Jon, the query reads as

select F where A matches 'Jim|Mary|Jon'

So, the content of A has to be one of these three names (this is not a substring search, so "Jon Ericson" would not be selected).

Reference: where ... matches ...