FILTER vs VLOOKUP, INDEX/MATCH, XLOOKUP, SUMIFS

In this video, which is part of Microsoft’s Modern Excel webcast series, we’ll cover the basic syntax of FILTER and then see how it compares to VLOOKUP, INDEX/MATCH, XLOOKUP, and SUMIFS. The question is: can FILTER accomplish the tasks we typically associate with these powerful Excel functions?

Note: not all versions of Excel contain the FILTER function. At the time of this writing, it is available in Excel 365 subscription only, and not in perpetual license versions such as Excel 2019, 2016, 2013, 2010, and so on.

Video

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

11 Comments

  1. Stuart Luxmore on October 9, 2020 at 8:01 am

    I would like to know what effect the different ways of using VLOOKUP, XLOOKUP and FILTER have on performance. Since I added FILTERs to a large workbook created some time ago, I seem to have lost a lot of performance and Excel is constantly recalculating. I know that VLOOKUP 9and XLOOKUP?) are ‘volatile functions i.e. always being recalculated – is FILTER the same?

  2. David H Bowman on October 9, 2020 at 8:58 am

    Love this video Jeff. Really well done. Thanks for all you are putting out!

  3. Yvonne L on October 9, 2020 at 10:14 am

    TEXTJOIN/FILTER is going to save my life! I consider myself an advanced Excel user and I have helper columns and complex formulas that will do what this does, but this is going to be a huge time saver and will hopefully open up new layers of analysis. Sadly while working remotely due to COVID, the current version of Excel on our remote platform doesn’t have the new dynamic array functions, but I just tested this on my personal desktop and it worked beautifully. Thank you for sharing this. Even though I’m pretty busy today, I am so glad I took a break to watch this! Thank you!

  4. Alex B on October 10, 2020 at 7:05 am

    That was really interesting, Thank you!
    Given that we now have options what would be your first choice between XLookup vs Filter and for summing values Sumifs vs Filter ?
    I also agree with Yvonne on the TEXTJOIN

  5. Edil on October 11, 2020 at 3:26 pm

    Very Cool, liked it a lot! Awesome, your explanations! Thank You!

  6. Declan Kelly on October 12, 2020 at 2:36 am

    Excellent video Jeff. Very interesting and I will be trying to use the Filter function in the future.

  7. John Zak on October 13, 2020 at 1:35 pm

    Well done! Another excellent video of yours.

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.