Perform Lookups with FILTER 2

This is the second post in a series where we are talking about how FILTER can be a nice alternative to traditional lookup functions such as VLOOKUP.

In the first post, we saw how the FILTER function supports multiple return columns (it can return values from multiple columns). In this post, we’ll see how the FILTER function supports multiple lookup columns (multiple lookup 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.

Multiple Lookup Columns

Before we dive into the mechanics, let’s just step back and confirm our objective. We have exported some expense totals from our accounting system, like this:

We want to retrieve the Budget amount from our budget table (Table1) which looks like this:

The key observation here is that there isn’t a single lookup column. To find our matching budget value requires us to consider multiple values … Dept and Acct.

With a traditional lookup function such as VLOOKUP, we would first need to combine the Dept and Acct labels in order to create a single lookup column. After all, VLOOKUP is designed to operate on a single lookup value. And that would be fine. This is how we’ve been approaching this issue for decades.

But, if we use FILTER instead of VLOOKUP, we wouldn’t need to combine multiple columns to create a single lookup column. FILTER can operate on multiple lookup columns. Let’s check it out.

Details

The FILTER function has the following arguments:

=FILTER(array, include, [if_empty])

In the first post, we used a single condition with the include argument. Now it is time to use multiple conditions with the include argument.

We need to understand how to express multiple conditions in a single argument. In summary, we wrap each condition in parentheses and combine them with an operator to express AND or OR logic.

To start, let’s test for a single condition. To test if ColumnA is equal to ValueA, we would use this logic in our include argument:

(ColumnA=ValueA)

Let’s say we also wanted to test if ColumnB is equal to ValueB. If both conditions need to be true, this is AND logic which is expressed with a multiplication operator * like this:

(ColumnA=ValueA)*(ColumnB=ValueB)

If instead only one condition needs to be true, this is OR logic …which is expressed with the addition operator + like this:

(ColumnA=ValueA)+(ColumnB=ValueB)

Now that we see how to write a single include argument that tests for multiple conditions, let’s put this knowledge to use and complete our objective.

Illustration

Table1 contains the budget values we’d like to retrieve:

We want to pull them into the Budget column of our summary:

So, in E7 we write the following:

=FILTER(Table1[Budget],(Table1[Dept]=B7)*(Table1[Acct]=C7))

We hit Enter and bam …

We fill the formula down … and got it!

Now the great news here is that we were able to work with the data as it comes, rather than having to first combine multiple lookup columns as we’ve done for decades with VLOOKUP. So, as you can see FILTER can be a nice alternative to VLOOKUP in some cases.

What do you think about FILTER? I’d love to know … post a comment below to share your thoughts.

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

5 Comments

  1. Jim Thornton on September 30, 2020 at 8:57 am

    Good stuff as usual. I appreciate the introduction to the Filter function.

    Two quick questions.
    1. If we are dealing with numbers we want to retrieve in the second table couldn’t we use our old friend SUMIFS?
    2. I can see an advantage of using Filter for retrieving non-numerical entries. Are there other advantages of using filter over SUMIFS?

    Thanks

    • Jeff Lenning on September 30, 2020 at 9:04 am

      Thanks Jim!
      Regarding:
      1. Yes, SUMIFS is a rock star and we could certainly use it. Note that SUMIFS uses ‘and’ logic but FILTER can use both ‘and’ and ‘or’ logic … so depending on what we are working on FILTER can be a nice alternative. I’ll be demonstrating this in an upcoming video 🙂
      2. Yes, FILTER has the advantage that it can just as easily retrieve text. And, as we’ll discover in the next post, we can easily combine them into a single cell with TEXTJOIN.
      We are just getting started with FILTER … it is an incredible function!
      Thanks
      Jeff

  2. Geoff Stuart on September 30, 2020 at 10:48 am

    Jeff,

    This is very powerful, and I’m so glad you wrote these two posts about it. I have an immediate need to retrieve a key value based on my cell entry falling between two dates (Start end End). I think FILTER will do the trick.

    Thanks again!

    Geoff

    • Jeff Lenning on September 30, 2020 at 10:50 am

      Awesome Geoff, thanks! And we are just getting warmed up with FILTER … it is an amazing function and we’ll continue exploring it.
      Stay tuned for the next post and follow up video 🙂
      Thanks
      Jeff

  3. Craig Smedley on September 30, 2020 at 6:37 pm

    Hi Jeff,
    This is great, thank you!
    No more creating another column with a concatenation of two columns just to do a lookup. Much neater.
    Craig

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.