Table of Contents
The WHERE IN function in Google Sheets allows users to filter data based on a specific list of values. This function works by specifying a range of cells or a column containing the desired values, and then selecting the data that matches those values. By using the WHERE IN function, users can easily narrow down their data and focus on specific information that is relevant to their needs. This feature is particularly useful for organizing and analyzing large sets of data in a more efficient and organized manner.
Google Sheets Query: 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.
Cite this article
stats writer (2024). How can I use the WHERE IN function in Google Sheets to filter data based on a list of values?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-can-i-use-the-where-in-function-in-google-sheets-to-filter-data-based-on-a-list-of-values/
stats writer. "How can I use the WHERE IN function in Google Sheets to filter data based on a list of values?." PSYCHOLOGICAL SCALES, 26 Jun. 2024, https://scales.arabpsychology.com/stats/how-can-i-use-the-where-in-function-in-google-sheets-to-filter-data-based-on-a-list-of-values/.
stats writer. "How can I use the WHERE IN function in Google Sheets to filter data based on a list of values?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-can-i-use-the-where-in-function-in-google-sheets-to-filter-data-based-on-a-list-of-values/.
stats writer (2024) 'How can I use the WHERE IN function in Google Sheets to filter data based on a list of values?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-can-i-use-the-where-in-function-in-google-sheets-to-filter-data-based-on-a-list-of-values/.
[1] stats writer, "How can I use the WHERE IN function in Google Sheets to filter data based on a list of values?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. How can I use the WHERE IN function in Google Sheets to filter data based on a list of values?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.