Table of Contents
Google Sheets allows users to filter data by a specific list of values. This feature is useful when you need to narrow down a large dataset to show only certain values. To filter by a list of values, first select the column you want to filter. Then, click on the filter icon and select “Filter by condition.” In the dropdown menu, choose “Custom formula is” and enter the formula “=ISNUMBER(MATCH(A2, {value1, value2, …}, 0))” where A2 is the cell containing the data and {value1, value2, …} is the list of values you want to filter by. This will only display rows that contain one of the specified values in the selected column. This feature can save time and effort when working with large datasets in Google Sheets.
Filter by List of Values in Google Sheets
You can use the following syntax to filter a dataset by a list of values in Google Sheets:
=FILTER(A2:C11,COUNTIF(E2:E5,A2:A11))
This particular formula filters the cells in the range A2:C11 to only return the rows where cells in the range A2:A11 contain a value from the list of values in the range E2:E5.
The following step-by-step example shows how to use this syntax in practice.
Step 1: Enter the Data
First, let’s enter the following dataset in Google Sheets that contains information about various basketball players:
Step 2: Define List of Values
Next, let’s define a list of Team values that we’d like to filter by:
Step 3: Filter by List of Values
Next, let’s type the following formula into cell A14 to filter the dataset by the list of teams we defined:
=FILTER(A2:C11,COUNTIF(E2:E5,A2:A11))
The following screenshot shows how to use this formula in practice:
Notice that the filtered dataset only contains the rows where the Team names from original dataset in the range A2:C11 are in the list of names in the range E2:E5.
Note: The FILTER function is case-insensitive. This means if you type “nets” instead of “Nets”, the function will still return the row with the “Nets” in the Team column.
Cite this article
stats writer (2024). How do I filter by a list of values in Google Sheets?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-do-i-filter-by-a-list-of-values-in-google-sheets/
stats writer. "How do I filter by a list of values in Google Sheets?." PSYCHOLOGICAL SCALES, 26 Jun. 2024, https://scales.arabpsychology.com/stats/how-do-i-filter-by-a-list-of-values-in-google-sheets/.
stats writer. "How do I filter by a list of values in Google Sheets?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-do-i-filter-by-a-list-of-values-in-google-sheets/.
stats writer (2024) 'How do I filter by a list of values in Google Sheets?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-do-i-filter-by-a-list-of-values-in-google-sheets/.
[1] stats writer, "How do I filter by a list of values in Google Sheets?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. How do I filter by a list of values in Google Sheets?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.