Search

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

Share

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.

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.

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

Excel School made me great at work.
5/5

– Brenda

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.

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.

How to fix SPILL Error in Excel Tables (3 easy solutions)

So you have a SPILL error in your Excel tables? In this quick article, let me show you 3 easy fixes to the problem. Fix

Related Tips

Excel Howtos

How to fix SPILL Error in Excel Tables (3 easy solutions)

Financial Modeling

Excel Howtos

Featured

Learn Excel

Excel Howtos

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).

Get FREE Excel & Power-BI Newsletter

One email per week with Excel and Power BI goodness. Join 100,000+ others and get it free.