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