## Perform Lookups with FILTER 1

This is the first post in a series where we’ll talk about how to use the FILTER function as an alternative to lookup functions including VLOOKUP. Why? Well, as we’ll discover in this series, the FILTER function offers several key benefits. For example, FILTER supports:

- Multiple
**return**columns - Multiple
**lookup**columns - Multiple matching
**row**values

In this first post, we’ll see how the FILTER function can return multiple column values. This is much different from VLOOKUP which is designed to return a value from a single column.

## Return Multiple Column Values

Before we get too far, let’s just confirm the Excel scenario we are trying to tackle.

We have some transactions, like this:

We need to retrieve the account names and groups from a lookup table like this:

One option would be to use a traditional lookup function such as VLOOKUP. In this case, we would write two formulas, one to retrieve the Name and another to retrieve the Group. And that would be fine. In fact, this is a common method we’ve used for decades. But, now we have another option. The FILTER function can return multiple columns. This means that we can write one formula instead of two (or more) to retrieve multiple column values.

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

## Details

The FILTER function is designed to return a subset of data, meaning, selected rows (or columns) from a data table. The basic syntax is this:

=FILTER(array, include, [if_empty])

**Where:**

**array**is the range you wish to filter**include**is the expression that defines which rows/columns to retrieve**[if_empty]**is an optional value to return if no items are found

So, Jeff … that doesn’t really sound like a lookup function. I know. And so often, when an Excel user is presented with a lookup task, traditional lookup functions such as VLOOKUP immediately come to mind. But, sometimes, there are alternatives to VLOOKUP which provide some benefits.

*Note: other examples include SUMIFS and Power Query. *

So, let’s see how we can apply the FILTER function to accomplish our lookup task.

## Illustration

We want to write a formula in **E7 **to populate both the Name and Group values.

That formula should retrieve values from **C15:D23** by finding a matching AcctID in **B15:B23** here:

So, in cell **E7**, we write the following formula:

=FILTER($C$15:$D$23,$B$15:$B$23=D7)

We hit Enter … and bam:

That one formula returns both the Name and Group values … yay!

We can fill the formula in E7 down to populate the remaining transactions:

In this illustration, the column order was the same … **Name **and then **Group**. But … what if the column orders are different? No problem.

### What if the column order is different?

Let’s say the column order is different. For example, Group then Name in one place:

And Name then Group in another:

One way to accomplish this is to FILTER a FILTER. In other words, we use FILTER to retrieve all columns that match the AcctID like we did previously with this formula:

=FILTER($C$15:$D$23,$B$15:$B$23=D7)

And then FILTER those results to only include one column. We can tell the FILTER function which column we want based on matching the column labels.

We wrap another FILTER function around the previous FILTER function and use the *include *argument to compare the column labels in **C14:D14** with the column label in **E6**. We are careful to use the proper absolute/relative references so that when we fill down/right it continues to work as expected:

=FILTER(FILTER($C$15:$D$23,$B$15:$B$23=$D7),$C$14:$D$14=E$6)

We hit Enter and bam …

Now, we can fill that formula down and right … and bam:

Yay … we did it!

## Conclusion

As you can see, the FILTER function provides an alternative to traditional lookup functions such as VLOOKUP. One benefit is that it supports multiple *return *columns … which is another way to say it can return multiple values from related columns.

In the next post, we’ll talk about how the FILTER function supports multiple *lookup *columns.

In the meantime, let me know what you think about the FILTER function by writing a comment below … thanks!

#### Sample file:

### Excel is not what it used to be.

You need the **Excel Proficiency Roadmap** now. Includes 6 steps for a successful journey, 3 things to avoid, and weekly Excel tips.

### Want to learn Excel?

Access all Undergrad and Masters lessons with a Campus Pass or CPE Pass. Includes on-demand training plus live office hours.

This seems similar in function to Match,Index lookups in previous versions of Excel. However, this syntax seems easier to use.

Agreed 🙂

I agree as well, Ann. A simplified version of the INDEX MATCH or INDEX MATCH MATCH nested functions is welcome. I often find myself using INDEX MATCH and frequently have to double check what the syntax is.

A neat solution that is only available to maybe 10% of excel users due to the 365 limitation.

It does seem strange to me that MS release some very powerful new functions to just one element of the excel distribution. I understand things need to be tested in real world scenarios, but for collaboration with others I can’t see giving up index & match until the new x-functions etc. are compatible with all versions of excel.

Do keep up the excellent work Jeff, you are an excellent teacher.

Interesting article. What are some of the data or result set characteristics that you might look for when deciding between the XLOOKUP and FILTER functions?

Your blog posts are a great compliment to the Excel University course materials.

John

Hi John,

I’d probably use XLOOKUP when I need to use some of the XLOOKUP-specific capabilities, including searching bottom to top, otherwise I’m really enjoying discovering various ways FILTER can be an alternative to so many functions.

And thanks for your kind note, I’m glad they are a helpful resource 🙂

Thanks

Jeff