How to filter based on a list in Google Sheets

Written by Updated on August 31, 2023 in Google Sheets Formulas
The words "Filter based on a list" on a glowing green background- A detailed explanation on how to filter based on a list in Google Sheets, with several examples included

When filtering data in Google Sheets you may sometimes need to filter by an entire list of values, rather than by just a single/few specified values.

You can use the FILTER and COUNTIF functions to filter based on a list in Google Sheets. You can also achieve the same task by using the FILTER and MATCH functions, but this formula combination is slower and much more resource-intensive than using the FILTER COUNTIF combination, especially when filtering multiple thousands of rows of data.

Here are formulas that you can use to filter by a list in Google Sheets:

FILTER COUNTIF

  • =FILTER(A3:C,COUNTIF(E3:E,A3:A))

FILTER MATCH

  • =FILTER(A3:C,MATCH(A3:A,E3:E,0))

*Important Note: Do not confuse the formula(s) above with COUNTIF FILTER, which a completely different formula when nested/written in this order. COUNTIF FILTER is a formula which will count occurrences of a specified criteria within a filtered range, where FILTER COUNTIF will allow you to filter by a list of data.

This article focuses on filtering by a list in Google Sheets, but check out this article if you want to learn how to filter by a list in Excel.

FILTER COUNTIF diagram: (Recommended formula)

A detailed formula breakdown diagram on using the FILTER function with the COUNTIF function to filter by a list in Google Sheets

FILTER MATCH diagram: (Recommended to use FILTER COUNTIF instead)

You'll notice that even though the FILTER COUNTIF and FILTER MATCH formulas look very similar and perform the exact same task, the references used in these formulas are in a different order.

A detailed formula breakdown diagram on using the FILTER function with the MATCH function to filter by a list in Google Sheets

Get the Google Sheets formulas cheat sheet

In this article I will stick to using the FILTER and COUNTIF functions, since this is the faster and more reliable choice for filtering a range by an array in Google Sheets.

If you want to learn how to use the FILTER function to perform ordinary filtering tasks, here is an article that will help you learn the function from the ground up.

How to use the Google Sheets FILTER function

But here we will only be filtering by lists/arrays/columns of data.

How the formula works is fairly simple, but making sure that you have arranged your cell references in the right order can sometimes be confusing when filtering based on a list in Google Sheets, especially when filtering data from multiple tabs.

This is why I have provided multiple examples showing the different ways to set up the formula depending on how your data is arranged (i.e. depending on which tab your data/formula is on).

I have also provided the diagrams above to help you understand the formulas, how they work, and the type of data that each reference should point to.

Filter rows based on a list in another sheet

To start with, we will put our formula, our unfiltered data, and the list to filter by, all on the same tab so that you can easily see how the formula is set up and how it functions.

Filter a range by an array in Google Sheets

In this first example will start very simple, and filter a range of data, by a single-column / list, where the list that we are going to filter by does not contain any extra entries that are not present in the source data (data to be filtered).

This is not always how it will be in the real world… but this will make it easy to see what the formula is doing.

In this scenario, we have a set of data that shows a list of employees within an individual department at a company, as well as their contact information. We also have another list that shows employees within the department who are eligible for a bonus.

We are going to filter the data set that shows employees and their contact information, by the list of eligible employees… so that only the contact information data of eligible employees is displayed in the filter output.

Again, in this example, every employee that is on the "eligible" list will also be found within the unfiltered data.

The task: Show employee contact information for employees who are eligible for a bonus

The logic: Filter the range A3:C, where the values in the range A3:A are equal to any of the values in the range E3:E

The formula: The formula below, is entered in the blue cell (G3), for this example

=FILTER(A3:C,COUNTIF(E3:E,A3:A))

An example of how to filter based on a list in Google Sheets by using the FILTER and COUNTIF functions- A list of employees and their contact information being filtered by a list

Filter a list by another list in Google Sheets

Now I am going to show you how to filter a list by another list in Google Sheets, or in other words how to filter a list by a single column.

This is similar to the last example, but here the employee contact information is not displayed, which can sometimes make it a little bit harder to determine what your source range is (range to be filtered).

When setting up your formula, always ask yourself, "which data is it that I am needing to filter?", and then ask, "which data is it that I am needing to filter by?".

Then refer to your formula diagram / cheat sheet to remember which range / reference goes where in the formula.

So once again, here we have a list of employees within an individual department at a company… but this time let's say that the list of eligible (for a bonus) employees represents a company-wide list, and this means that only some of the employees on the eligibility list will be found within the source data/ list.

This will not affect how the formula operates but it's good to note that your filter output will not show every single employee that is show on the eligibility list, as should be expected… since in this example only some employees are on both lists.

The task: Show a list of employees in your department, who are on the company-wide eligibility list

The logic: Filter the range A3:A, where the values in the range A3:A are equal to any of the values in the range C3:C

The formula: The formula below, is entered in the blue cell (E3), for this example

=FILTER(A3:A,COUNTIF(C3:C,A3:A))

An example of how to filter a list by another list in Google Sheets, or in other words, how to filter a column based on a list- A list of employee names being filtered by another list of employee names

Filter a range based on a column from another range

In this example, both the unfiltered source data (data to be filtered) and the list to filter by are given in the format of multiple column ranges. In other words, the list that we want to filter by is provided with other columns of data beside it this time.

This type of situation where the list to filter by is attached to other data can be expected in the real world… but it presents another potentially confusing situation with setting up your formula references because of the added columns.

This will not change how we setup the formula, and you may even notice that the formula in this example is the same formula that is used in the first example… but it is important to learn how to identify your column references with data that looks like this.

Again, if you are setting up a formula and you find yourself confused, just ask yourself which data needs to be filtered, and which data you are using to filter with/by, then refer to the diagram to find where each reference/range should be typed into the formula.

So in this scenario, let's say that we have two different data reports that show employee information. One report shows employee contact information like in the previous examples, and the other report shows employee work schedules.

What we want to do is filter the contact information, by the list of employees who appear on the work schedule report. Pretend that your boss gave you these two different reports / sets of data, and said that he or she needed you to filter the contact information so that only the employees who are on the schedule are shown.

The task: Show employee contact information for the employees who appear on the work schedule

The logic: Filter the range A3:C, where the values in range A3:A are equal to any of the values in the range E3:E

The formula: The formula below, is entered in the blue cell (I3), for this example

=FILTER(A3:C,COUNTIF(E3:E,A3:A))

An example of how to filter a range by a column from another range in Google Sheets- Employees and their contact info being filtered by a column from another range that displays work schedule data for employees

Filtering by a list when the data is on separate sheets

Now that you know how to filter by a list where all of the data and the formula are held on the same sheet/tab, let's go over how to filter by a list when using multiple tabs.

There are a variety of arrangements that are possible when using multiple tabs for filtering a range by an array, and so I'll go over each of them.

We will use the same exact data and task as in the last example, to make things simple and to allow for easy comparison.

Source data and formula on the same sheet

Here is an example showing how to set up your formula when the unfiltered source data and filter formula are on the same tab… but where your list that you want to filter by is on a separate tab.

Unfiltered source data: Tab 1

Formula: Tab 1

List to filter by: Tab 2

The formula: The formula below, is entered in the blue cell (E3), for this example

=FILTER(A3:C,COUNTIF('Tab 2'!A3:A,A3:A))

An example of how to filter based on a list from another sheet in Google Sheets, where the unfiltered source data and the filter formula are on the same sheet (Tab 1)... and the list to filter by is on a separate sheet
An example of how to filter based on a list from another sheet in Google Sheets- The list to filter by on Tab 2

List to filter by and formula on the same sheet

Here is an example showing how to set up your formula when the list that you want to filter by and the formula are on the same tab, but the unfiltered source data is on a separate tab.

List to filter by: Tab 1

Formula: Tab 1

Unfiltered source data: Tab 2

The formula: The formula below, is entered in the blue cell (E3), for this example

=FILTER('Tab 2′!A3:C,COUNTIF(A3:A,'Tab 2'!A3:A))

An example of how to filter a range by an array from another sheet in Google Sheets, where the list to filter by and the filter formula are on the same sheet (Tab 1)... and the unfiltered source data is on a separate sheet
An example of how to filter a range by an array from another sheet in Google Sheets- The unfiltered source data on Tab 2

Source data, list to filter by, and formula, all on separate sheets

Finally, here is an example showing how to set up your formula when the unfiltered source data (data to be filtered), the list to filter by, and the formula are each on a separate tab.

Unfiltered source data: Tab 1

List to filter by: Tab 2

Formula: Tab 3

The formula: The formula below, is entered in the blue cell (A3), for this example

=FILTER('Tab 1'!A3:C,COUNTIF('Tab 2′!A3:A,'Tab 1'!A3:A))

An example of how to filter by a list from another sheet in Google Sheets- The unfiltered source data on Tab 1
An example of how to filter by a list from another sheet in Google Sheets- The list to filter by on tab 2
An example of how to filter by a list from another sheet in Google Sheets, where the unfiltered source data is on tab 1, the list to filter by is on tab 2, and the filter formula is on tab 3

Pop Quiz: Test your knowledge

Answer the questions below about filtering based on a list, to refine your knowledge! Scroll to the very bottom to find the answers to the quiz.

Classroom downloads:

Filter by list cheat sheet (PDF)

Google Sheets formulas cheat sheet

Click the green "Print" button below to print this entire article.

Question #1

Which of the following formulas filters a range, by the column/range C3:C?

  1. =FILTER(A3:G,COUNTIF(Z3:Z,A3:A))
  2. =FILTER(B3:C,COUNTIF(A3:A,B3:B))
  3. =FILTER(D3:J,COUNTIF(C3:C,E3:E))

Question #2

Which of the following formulas has a source range of A3:G?

  1. =FILTER(A3:G,COUNTIF(Z3:Z,A3:A))
  2. =FILTER(B3:C,COUNTIF(A3:A,B3:B))
  3. =FILTER(D3:J,COUNTIF(C3:C,E3:E))

Question #3

True or False: FILTER COUNTIF and FILTER MATCH do the same thing?

  1. True
  2. False

Answers to the questions above:

Question 1: 3

Question 2: 1

Question 3: 1

Related Posts: