Many users coming from SQL wonder if the SQL IN Operator in Google Sheets Query is available. The short answer: Google Sheets QUERY does not support SQL IN or NOT IN operators directly, but there are practical alternatives.

In this guide, we’ll explain how to use the MATCHES operator as an alternative to SQL IN and NOT IN in Google Sheets QUERY. This tutorial is part of the WHERE Clause in Google Sheets QUERY: Logical Conditions Explained hub, which covers all logical operators, comparisons, and condition-building techniques used in QUERY statements.

Why SQL IN Operator Matters

In SQL, the IN operator is used to match a column against multiple values in a list:

SELECT * FROM table WHERE column IN ('Value1', 'Value2', 'Value3');

It allows you to check for multiple possible matches without writing multiple OR conditions.

Since Google Sheets QUERY doesn’t have IN, we can achieve the same result using MATCHES, which supports regular expressions.

Using MATCHES as SQL IN Alternative in Google Sheets QUERY

Suppose you have a table of products in A1:C, where:

• Column A → Product Names
• Column B → Unit Price
• Column C → Stock Availability

And you want to filter products based on a list in column E (your criteria column).

Option 1: Hardcoded values in QUERY

You can directly list the product names separated by |:

=QUERY(A1:C, "SELECT * WHERE A MATCHES 'Apple|Sweet Melon|Avocado'")

Option 2: Dynamic list using a column reference

If your criteria are in E1:E, combine them using TEXTJOIN:

=QUERY(A1:C, "SELECT * WHERE A MATCHES '" & TEXTJOIN("|", TRUE, E1:E) & "'")

• TEXTJOIN("|", TRUE, E1:E) combines all values in E1:E into a string separated by |.
• Example output: Apple|Sweet Melon|Avocado
• This allows dynamic updates if your criteria change.

Explanation of TEXTJOIN Usage

To verify, enter the following formula in cell F1:

=TEXTJOIN("|", TRUE, E1:E)

This will return the string:

Apple|Sweet Melon|Avocado

You can then use this string in the QUERY formula like this:

=QUERY(A1:C, "SELECT * WHERE A MATCHES '" & F1 & "'")

Alternatively, you can replace F1 with the TEXTJOIN formula directly inside the QUERY:

=QUERY(A1:C, "SELECT * WHERE A MATCHES '" & TEXTJOIN("|", TRUE, E1:E) & "'")

For more details on using cell references and literals in QUERY, see Examples for Using Literals in QUERY in Google Sheets.

Using MATCHES as SQL NOT IN Alternative

To exclude products in a list (SQL NOT IN equivalent), use the NOT operator:

=QUERY(A1:C, "SELECT * WHERE NOT A MATCHES '" & TEXTJOIN("|", TRUE, E1:E) & "'")

Important: The NOT operator must prefix the column identifier, not follow it.

This formula will return all products not listed in your criteria column.

SQL IN Operator in Google Sheets Query – Summary

• SQL IN Alternative: Use MATCHES with a |-separated list or TEXTJOIN for dynamic lists.
• SQL NOT IN Alternative: Prefix NOT to the column and combine values with TEXTJOIN.
• Both methods work for single or multiple criteria without manually writing multiple OR conditions.

For a complete guide on logical operators in Google Sheets QUERY, see the hub: WHERE Clause in Google Sheets QUERY: Logical Conditions Explained.

For learning MATCHES operator and string patterns, see: String Matching in QUERY.