Google-sheets – Google Sheet Query smarter way to get multiple criteria

formulasgoogle sheetsgoogle-apps-scriptgoogle-sheets-queryregex

I use a spreadsheet where I want a list of items which contains multiple criteria.
(I need all cells in a range which contains the letter 'a – g'

The formula I use now is

=query(Planning!G5:G37;  "select * where G contains 'a' or G contains 'b' or G contains 'c'   or ............  " )

What I want is the query get the criteria out of a list or an array, but I couldn't figure out how to do this.

you can use regex if you do it like this:

=QUERY(Planning!G5:G37, "where G matches '[a-g]'", 0)

=QUERY(Planning!G5:G37, "where G matches 'a|b|c|d|e|f|g'", 0)

to mimic contains do:

=QUERY(G5:G37, "where G matches '[a-g]*|.*[a-g]'", 0)

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

You can use REGEXMATCH:

=IF(REGEXMATCH(A1, "sites"), 1, 0)

To explain, REGEXMATCH returns true if and only if the argument is a substring of your string.