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.


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.

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?

Our training programs start at $29 and will help you learn Excel quickly.


  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.

  8. yosef elbaum on November 17, 2020 at 5:13 pm

    What if the second condition I have is going across the columns as headings (e.g. columns are my departments with accounts and transaction #’s going down) I want to sum up the account balances by the department columns and those columns populate by the filter formula.

    • Jeff Lenning on November 18, 2020 at 9:15 am

      One way to filter for both rows and columns is to use two FILTER functions, one for rows and the next for columns:
      Hope it helps!

  9. Amie on January 3, 2021 at 4:57 am

    This was SO clear – you’re a gifted teacher! Thank you very much for the information.

Leave a Comment