fbpx
Search
Close this search box.

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

Share

Facebook
Twitter
LinkedIn

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.

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Overall I learned a lot and I thought you did a great job of explaining how to do things. This will definitely elevate my reporting in the future.
Rebekah S
Reporting Analyst
Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

3 Responses to “Filter one table if the value is in another table (Formula Trick)”

  1. Montu says:

    What about the opposite? I want a list of products without sales or customers with no orders. So I would exclude the ones that are on the other table.

    • Chandoo says:

      Good question. You can check for the =0 as countifs result. for example,
      =FILTER(orders, COUNTIFS(products, orders[Product])=0)
      should work in this case.

      PS: I have added this example to the article now.

  2. Raphael says:

    Hi there!

    Could i check if there was a way to return certain fields of the table only?

    so based off your example above, i would like to continue to use the 'Products" table as a way to filter out items from my "Orders" table, but only want to show maybe only the "Product" and "Order Value" fields, rather than all 5 fields (sales person, customer, product, date, order value).

Leave a Reply