Although Excel has a handful advanced filtering feature to filter by multiple criteria, it is not dynamic and may require complex prep work. On the other hand, the FILTER function can give the dynamic approach you need. However, establishing logical rules can be hard to understand at first. In this guide, we’re going to show you How to filter a table based on another in Excel in a simpler way.
Sample Data
We have two tables as the title refers:
- Source
- Criteria
Our goal is to filter the source table by using items in the criteria table.
Formula
Syntax
=FILTER( <source table>, COUNTIFS( <criteria column>, <source filter column> ) )
How it works
The FILTER function needs an equivalent length Boolean-array to filter out the values that do not match the condition. The TRUE values are kept and the FALSE are eliminated. Thanks to Excel's acceptance of one (1) and zero (0) as TRUE and FALSE, we can use the COUNTIFS function to generate such an array.
When you give the source table's column as criteria to the COUNTIFS, Excel runs the COUNTIFS for each value in the criteria argument and returns an array of values instead of a single value.
In our example, the formula is like this:
=FILTER(Source,COUNTIFS(Criteria[Type],Source[Type]))
If you select the COUNTIFS and press F9 you can see the array.
How to negatively filter a table based on another
As we have mentioned before, the FILTER requires a Boolean or 0-1 array to keep or eliminate the value. You can send an array with opposite values by using a simple trick:
=FILTER(Source,COUNTIFS(Criteria[Type],Source[Type])=0)
"=0" equation causes to return TRUE for zero (0) values and vice versa.
