Google Sheets Query: How to Use WHERE IN a List

You can use the following syntax in a Google Sheets query to return all rows where a column contains a value in a list:

=QUERY(A1:C11, "SELECT * WHERE A MATCHES '(value1|value2|value3)'")

This particular query will return all rows in the range A1:C11 where the value in column A is equal to value1, value2, or value3.

Note: The | operator stands for “OR” in Google Sheets.

The following example shows how to use this syntax in practice.

Example: Use WHERE IN List in Google Sheets Query

Suppose we have the following dataset that contains information about various basketball players:

We can use the following query to return all rows where the value in the “Team” column is equal to Mavs, Magic, Kings, or Lakers:

=QUERY(A1:C11, "SELECT * WHERE A MATCHES '(Mavs|Magic|Kings|Lakers)'")

The following screenshot shows how to use this query in practice:

Notice that the only rows returned are the ones where the value in the “Team” column is equal to Mavs, Magic, Kings, or Lakers.

You can also use similar syntax to query for rows where a column contains one of several numeric values.

For example, we can use the following query to return all rows where the value in the “Points” column is equal to 19, 20, or 22:

=QUERY(A1:C11, "SELECT * WHERE C MATCHES '(19|20|22)'")

The following screenshot shows how to use this query in practice:

Notice that the query only returns the rows where the value in the “Points” column is equal to 19, 20, or 22.

The following tutorials explain how to perform other common operations in Google Sheets: