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
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.
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?
Hi Stuart … FILTER hasn’t shown any performance decrease in the size of workbooks I use; however, I haven’t tested on large data sets.
Volatile functions are those that recalculate even when their dependent cell values do not change. There are only a handful of them and they are listed on Microsoft’s site here:
https://docs.microsoft.com/en-us/office/client-developer/excel/excel-recalculation
Thanks
Jeff
Love this video Jeff. Really well done. Thanks for all you are putting out!
Thank you 🙂
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!
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
Very Cool, liked it a lot! Awesome, your explanations! Thank You!
Excellent video Jeff. Very interesting and I will be trying to use the Filter function in the future.
Thanks Declan!
Well done! Another excellent video of yours.
Thank you!
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.
One way to filter for both rows and columns is to use two FILTER functions, one for rows and the next for columns:
=FILTER(FILTER(array,row_condition),column_condition)
Hope it helps!
Thanks
Jeff
This was SO clear – you’re a gifted teacher! Thank you very much for the information.