Perform Lookups with FILTER 3

This is the third post in this series, where we are doing lookup tasks with the FILTER function instead of VLOOKUP. In the first post, we discovered that the FILTER function supports multiple return columns. In the second post, we discovered that it also supports multiple lookup columns. In this post, we’ll discover that it can return the results from multiple rows. This is much different from VLOOKUP, which stops at the first matching row. The FILTER function examines the entire range and returns results for all of the matching rows. Pretty cool. Let’s check it out.

Objective

Before we jump into the mechanics, let’s confirm our goal here.

We have exported a bunch of transactions from our sales system. They are stored in Table1:

The key observation is that there can be multiple rows for any given Item, such as X100.

Let’s say we would like to retrieve data for a specific Item, like X100. We enter the desired Item into our worksheet like this:

Now, let’s say that we wanted to return a list of the customers that purchased this item. With a traditional lookup function such as VLOOKUP, it would stop at the first matching row and return that one related value. In this case, it would be RAM101.

But, our data has multiple matching rows … and thus, we want to return a list of all matching customers. This brings us to the reason why we are here. Let’s use FILTER to accomplish this.

Note: depending on your version of Excel, you may not have access to the FILTER function. At the time of this writing, it is available in Excel 365.

Illustration

The FILTER function automatically returns a list of all items that meet the condition (or conditions).

So, in C6 we write the following formula:

=FILTER(Table1[Customer],Table1[Item]=B6)

We hit Enter and bam…

FILTER returns a list of all matching customers … yay!

But … hang on. Currently, the formula results spill into multiple cells. Can we somehow get Excel to place all of the customers in a single cell? Yes … with a little help from a friend, TEXTJOIN.

TEXTJOIN

TEXTJOIN enables us to take multiple values and place them in a single cell with a desired delimiter. A delimiter is just the character we select to place in between each value.

The syntax of TEXTJOIN is:

=TEXTJOIN(delimiter, ignore_empty, text1, ...)

Where:

  • delimiter is the character to separate each item, enclosed in quotes
  • ignore_empty when true will exclude any blank items
  • text1, … is a list of the text values or ranges to join

So, if we wanted to use a comma as the character between each customer name, and ignore any empty results, we would wrap the TEXTJOIN function around the existing FILTER function, like this:

=TEXTJOIN(",",TRUE,FILTER(Table1[Customer],Table1[Item]=B6))

We hit enter, and bam:

We did it!

If we had additional items, we could simply fill the formula down:

Pretty cool!

What do you think about FILTER? I’d love to know … please share by posting a quick comment below.

Sample file

Posted in ,

Jeff Lenning

I love sharing the things I've learned about Excel, and I built Excel University to help me do that. My motto is: Learn Excel. Work Faster.

Stay Connected

If you'd like to be notified when I write a new Excel article, enter your name and email and click SUBSCRIBE. You can unsubscribe anytime, and I will never sell your email address.

Want to learn Excel?

Our Campus Pass includes access to our entire Undergrad and Masters catalog. Gamification ensures it is the most fun you can have learning Excel :)

2 Comments

  1. Jack on October 6, 2020 at 11:16 am

    I can’t find the Filter function in my version of Excel

    • Jeff Lenning on October 6, 2020 at 11:20 am

      Hi Jack! The FILTER function is available with a 365 subscription (but not in perpetual license versions such as Excel 2019, 2016, 2013, and so on). If you have 365 but don’t see the FILTER function, perhaps you can expedite this enhancement by changing your office update channel to monthly. If you are a part of a corporate network, your IT folks can help with changing the update channel.
      Thanks
      Jeff

Leave a Comment





For security, use of Google's reCAPTCHA service is required which is subject to the Google Privacy Policy and Terms of Use.

I agree to these terms.