Google Sheets QUERY WHERE IN List: OR and MATCHES Examples

Google Sheets QUERY does not support the SQL syntax WHERE A IN ('Open', 'Pending'). To return rows where a column equals any value in a list, use either repeated or conditions or the matches operator:

=QUERY(A1:C, "select * where A = 'Open' or A = 'Pending'", 1)

For a longer text list, the equivalent matches formula is:

=QUERY(A1:C, "select * where A matches 'Open|Pending|Review'", 1)

Both formulas filter column A. The final argument, 1, tells QUERY that the source range contains one header row.

Example data

Suppose columns A to C contain tasks:

Status	Task	Owner
Open	Prepare quote	Mia
Closed	Send invoice	Liam
Pending	Confirm address	Mia
Review	Check contract	Noah

The aim is to return only rows whose status appears in an allowed list.

Method 1: Use OR for a short list

Join complete conditions with or:

=QUERY(A1:C, "select * where A = 'Open' or A = 'Pending'", 1)

This is the clearest method for two or three values. Each comparison names the column again; the following abbreviated version is invalid:

// Invalid QUERY syntax
where A = 'Open' or 'Pending'

To combine the list with another requirement, put the alternatives in parentheses. This example returns Open or Pending tasks owned by Mia:

=QUERY(A1:C, "select * where (A = 'Open' or A = 'Pending') and C = 'Mia'", 1)

Parentheses ensure the owner condition applies to both status values. For more combinations, see using multiple criteria in the Google Sheets QUERY function.

Method 2: Use MATCHES for a longer text list

The matches operator accepts a regular-expression pattern. A vertical bar means “or”, making it a compact substitute for a text IN list:

=QUERY(A1:C, "select * where A matches 'Open|Pending|Review'", 1)

This selects rows where column A matches any of the three alternatives. Google documents matches as a regular-expression comparison rather than a global substring search. Use contains when you deliberately want a value to appear anywhere inside longer text.

Text matching is case-sensitive. If the sheet may contain OPEN, Open and open, normalise the queried column with lower() and write the alternatives in lowercase:

=QUERY(A1:C, "select * where lower(A) matches 'open|pending|review'", 1)

Values containing regular-expression characters such as ., +, (, ) or ? need escaping. For arbitrary user-entered values, explicit or comparisons are usually easier to reason about than a generated regex.

Use a list stored in cells

If cells E2:E4 contain Open, Pending and Review, TEXTJOIN can build the alternatives for matches:

=QUERY(A1:C, "select * where A matches '"&TEXTJOIN("|", TRUE, E2:E4)&"'", 1)

The pieces work as follows:

TEXTJOIN("|", TRUE, E2:E4) produces Open|Pending|Review.
The & operators insert that pattern inside the query string.
TRUE tells TEXTJOIN to ignore blank cells.

This approach works best when the list contains controlled, plain-text values. If list values can contain regex punctuation, escape those characters first or use a helper column and FILTER instead.

If you only need one value from a cell, use a direct comparison rather than building a list. The guide to referencing a cell in a QUERY WHERE clause covers text, numbers and quoting.

Filter by a list of numbers

Numbers are not wrapped in single quotes:

=QUERY(A1:C, "select * where B = 100 or B = 250 or B = 500", 1)

Do not use matches as the first choice for numeric equality. matches converts non-string inputs to strings for comparison, while numeric or conditions preserve the intended data type and are easier to read.

Exclude a list of values: NOT IN

To reproduce SQL’s NOT IN, negate a parenthesised set of conditions:

=QUERY(A1:C, "select * where not (A = 'Closed' or A = 'Cancelled')", 1)

With a controlled text pattern, you can also negate matches:

=QUERY(A1:C, "select * where not (A matches 'Closed|Cancelled')", 1)

Common errors

Unable to parse query string

Text values inside the query require single quotes, while the spreadsheet formula holds the full query in double quotes:

// Wrong
=QUERY(A1:C, "select * where A = Open or A = Pending", 1)

// Correct
=QUERY(A1:C, "select * where A = 'Open' or A = 'Pending'", 1)

If the message includes NO_COLUMN, QUERY may be interpreting an unquoted value as a column identifier. The QUERY errors troubleshooting guide covers that error and Col1 notation in more detail.

The formula returns no rows

Check these points:

matches and text comparisons are case-sensitive.
The queried column may contain leading or trailing spaces.
The third QUERY argument must correctly state the number of header rows.
A column containing mixed data types is assigned one majority type; minority values are treated as null.
Regex punctuation in a dynamically generated matches list may change the pattern.

Which method should you use?

Situation	Recommended approach
Two or three fixed values	Repeated `or` comparisons
Several controlled text values	`matches ‘One
Values maintained in cells	`matches` with `TEXTJOIN`
Numeric equality	Repeated numeric `or` comparisons
Arbitrary text containing punctuation	Explicit `or` or a helper-column `FILTER`
Excluding values	`not (...)` around the conditions

The safest default is repeated or conditions. Use matches when the list is longer and its text values are controlled. Google Sheets runs these expressions through the Google Visualization query language, where or, not, parentheses and matches are supported but SQL-style IN is not.