Google Sheets QUERY WHERE IN List: OR and MATCHES Examples

Google Sheets QUERY has no SQL-style IN operator. To return rows where a column equals any value in a list, use repeated or conditions for a short list or matches for a longer text list.

Use this for two or three values:

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

Use this for several controlled text values:

=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. Replace A1:C, the queried column and the values to fit your sheet.

Your list is…	Use…
Two or three fixed values	Repeated or conditions
Several controlled text values	matches with `
Stored in cells and may contain punctuation	FILTER with COUNTIF
Numeric	Repeated numeric or conditions

If you want a formula you can paste immediately, start with or. It is longer than matches, but it has no regular-expression surprises.

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.

For Open and Pending, the result is:

Status	Task	Owner
Open	Prepare quote	Mia
Pending	Confirm address	Mia

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 must name the column again.

This is 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 is exactly Open, Pending or Review. The ^ and $ anchors make the exact-match intention explicit; the parentheses group the 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.
• ^( and )$ make the generated alternatives an exact-value match.
• 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.

Keep the criteria range bounded where practical—for example, E2:E20 rather than the whole of column E. If every criteria cell is blank, the generated regex can match blank values, so validate that the list contains at least one item.

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.

Dates also require their QUERY-language literal syntax rather than ordinary quoted text:

=QUERY(A1:C, "select * where B = date '2026-09-01' or B = date '2026-09-05'", 1)

The source cells in column B must contain real date values, not dates stored as text.

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)

Safer alternative for arbitrary values: FILTER and COUNTIF

When the allowed values are maintained by users, they may contain punctuation with special regex meaning. In that situation, FILTER with COUNTIF provides exact membership testing without constructing query text.

With headers in row 1, data in A2:C and allowed statuses in E2:E20, use:

=FILTER(A2:C, COUNTIF(E2:E20, A2:A)>0)

This keeps each row from A:C when its column-A value occurs in the allowed list. It does not return the header row; place your own headers above the result or stack them separately.

Use QUERY when you also need query-language features such as select, group by, pivot, order by, label or format. Use FILTER when exact membership is the main requirement.

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.

NO_COLUMN after combining ranges

A normal range such as A1:C uses column letters inside the query. A constructed array such as {A1:A,C1:C} uses Col1, Col2 and so on:

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

Col1 means the first column of the constructed array, not necessarily spreadsheet column A.

The formula works in one locale but not another

Some spreadsheet locales use semicolons rather than commas between function arguments. If Google Sheets reports a formula parse error before evaluating QUERY, replace the argument separators required by your locale; do not change commas that are part of the query text itself.

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	FILTER with COUNTIF
Excluding values	not (...) around the conditions

Final checklist

Before using the result, confirm that:

1. The data range includes every column named in the query.
2. Text values use single quotes; numbers do not.
3. The final QUERY argument matches the number of header rows.
4. Mixed and and or conditions are grouped with parentheses.
5. A generated matches list contains no unescaped regex punctuation or blank-only criteria.
6. Constructed arrays use Col1, Col2 and similar identifiers.

The safest QUERY default is repeated or conditions. Use matches when the list is longer and its text values are controlled; use FILTER with COUNTIF when arbitrary values are stored in cells. Google Sheets runs QUERY expressions through the Google Visualization query language, where or, not, parentheses and matches are supported but SQL-style IN is not. Google’s QUERY function reference documents the surrounding QUERY(data, query, [headers]) formula and its data-type rules.