The FILTER function allows us to filter a set of data based on a set of criteria. Filtering data based on a list in another worksheet is also beneficial. When filtering data in Excel, you may need to filter by a whole list of values rather than just one or a few selected variables on occasion.  We will see two methods for Excel filter by list in another sheet. We have a sample dataset in the Sample Data sheet.
and a list for filtering in the List for Filter sheet.
How to Filter by List in Another Sheet in Excel: 2 Ways
Using formulae, there is a simple method to accomplish this. To filter based on a list in Excel, utilize the COUNTIF function. Advance Filter is another quick option to do the same.
Method 1: Filter by List in Another Sheet Using Advance Filter
In our first method, we will use Advance Filter to extract data based on the list in another sheet.
Steps:
- Select the entire data set including headers and go to Data > Advanced as shown in the image below.
- Now, select the list in another sheet which is list for filter, and click OK.
- Remember one thing. The header must be the same as the header in the actual dataset.
- Finally, our result will look like the following image, where unfiltered cells are hidden.
Read More: How to Perform Custom Filter in Excel
Method 2: Using COUNTIF Function to Filter by List in Another Sheet
The COUNTIF function is the most used formula for filtering data based on a list in another sheet. Let’s see, how it works.
Steps:
- First, type the following formula in cell G5.
=COUNTIF('List for Filter'!$C$5:$C$9, B5)
- Now, press the ENTER key.
- Finally, drag down to AutoFill the rest of the series.
- After that, select the entire dataset and press CTRL+SHIFT+L.
- So, we applied Filter here. At this point, Filter Count for 1 and click OK.
- Finally, our result will look like the following image.
Read More: How to Filter Cells with Formulas in Excel
Practice Section
The single most crucial aspect in becoming accustomed to these quick approaches is practice. As a result, we’ve attached a practice workbook where you may practice these methods.
Another sheet with a list for filtering is also given in the practice book.
Download Practice Workbook
Conclusion
That’s all for the article. These are 2 methods for Excel Filter by List in Another Sheet. Based on your preferences, you may choose the best alternative. Please leave them in the comments area if you have any questions or feedback.
Related Articles
- How to Excel Filter Data Based on Cell Value
- How to Filter Data in Excel Using Formula
- How to Add Filter in Excel
- Shortcut for Excel Filter
- How to Use Filter in Protected Excel Sheet
- How to Filter in Excel with Merged Cells
- How to Filter Email Addresses in Excel
- How to Remove Filter in Excel
- How to Filter Column Based on Another Column in Excel
