Filter one table if the value is in another table (Formula Trick)

Time for a quick but very handy Excel FORMULA trick.

You have two tables. ORDERS & PRODUCTS. You want to see all the orders if the product name is mentioned in the other table. Thanks to the new FILTER() function in Excel, we can do this easily now.

The data setup

For the purpose of this example, assume you have a table named orders and another table named products.

The formula to filter one table by another list

Here is the formula to fetch all orders for the products listed in the other table.

=FILTER(orders, COUNTIFS(products, orders[Product]))

How does this formula work?

This is a two-part puzzle.

PART 1 – COUNTIFS with Arrays:

Let’s look at the COUNTIFS portion first:

COUNTIFS(products, orders[Product])

This formula returns an array of 1s & 0s

• It will be 1 whenever the orders[Product] matches one of the products table items.
• Else it will be 0
• The size of this array will be same as orders table. So if orders table has 100 rows, then you will get 100 ones or zeros here.

PART 2 – FILTER

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:

Checking the reverse – Filter list when values are not in the other list

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:

=FILTER(orders, COUNTIFS(products, orders[Product])=0)

Problems with this approach:

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.

For more on Power Query, please read this article.

Example workbook

If you need a sample file to practice this formula, click here and download it.

How do you deal with this problem?

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.