Mahbub
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 Datasheet.
and a list for filtering in the List for Filter sheet.
Download Practice Workbook
2 Ways to Filter by List in Another Sheet in Excel
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 that 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 (5 Ways)
Similar Readings
- How to Use Profit Percentage Formula in Excel (3 Examples)
- How To Search Multiple Items in Excel Filter (2 Ways)
- Excel VBA to Filter in Same Column by Multiple Criteria (6 Examples)
- How to Remove Filter in Excel VBA (5 Simple Methods)
- VBA Code to Filter Data in Excel (8 Examples)
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 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 (2 Ways)
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.
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.
” title=”