Filter one table if the value is in another table (Formula Trick)
Time for a quick but very handy Excel FORMULA trick.
For the purpose of this example, assume you have a table named orders and another table named products.
Here is the formula to fetch all orders for the products listed in the other table.
How does this formula work?
This is a two-part puzzle.
Let’s look at the COUNTIFS portion first:
This formula returns an array of 1s & 0s
The NEW FILTER() function in Excel can take a list or table and filter it based on a criteria.
So, for example, =FILTER(orders, orders[Product]=”Milk Bars”) would filter all rows where the product names is Milk Bars.
The trick is to use COUNTIFS output (which will be a bunch of 1s & 0s) as the criteria for FILTER. When Excel sees these 1s&0s, it’s going to include the rows with 1 & exclude the rows with 0.
Thus, producing the relevant orders for us.
Here is a quick demo of the formula in action:
We can use the same approach to check for reverse too. This is helpful in finding which customers did not purchase in a month or which products did not sell in a given country etc. Here is an example formula for that:
While this is a great technique, if you have large amounts of data then the formula could be slow. In such cases, I recommend using Power Query to handle the job. We can use inner join in Power Query merge operation to filter one table by merging it with another table.
I have been using the FILTER(… COUNTIFS()) approach for quick scenarios and Power Query Merge option for more complicated situations. What about you? How are you solving this problem? Please share your thoughts or questions in the comments section.
The post Filter one table if the value is in another table (Formula Trick) appeared first on Chandoo.org – Learn Excel, Power BI & Charting Online.
