FILTER vs VLOOKUP Function in Excel – have you used both? Which do you prefer?
This week’s hint and tip is about the FILTER vs VLOOKUP function in Excel. We are also looking at how you can take a simple XLOOKUP a step further through nesting it inside another. We cover XLOOKUP in our Master Class Excel Silver training course but these nesting examples aren’t specifically covered on our Excel course, so we decided to do a hint and tip on it. We are going to go through it now below.
VLOOKUP and XLOOKUP Functions in Excel
For years the VLOOKUP function has been a real favourite of the Excel community and is used to great effect in business who want to retrieve data from tables by matching values.
When XLOOKUP came along this ramped up the level a few notches as it added even greater flexibility by allowing matching to any column not just the first column of a table array and has the extra benefit of returning more than one value of that matched row.
However in both these cases only the first record’s data is shown so this is not great if you wish to have all the rows of a matching value show.
FILTER Function in Excel
The video we’ve recorded sows this in action by use of =FILTER.
The structure of this new function is: =FILTER(Array,Include,If Empty)
- Array the whole table is selected so that all columns can be shown
- Include is where you do a test for inclusion (a bit like the first part of a VLOOKUP)
- If Empty – for example if a value cannot be matched and found this could show a textual error
To explain the difference between these three functions, please look at the enclosed workbook ‘FILTER vs VLOOKUP Function’ (clicking here will download a copy to your computer so you can try it out!).
The video below goes through the FILTER, VLOOKUP and XLOOKUP function examples that you can see in the spreadsheet above. It talks you through how the FILTER function can give you more information than the VLOOKUP and XLOOKUP functions can. We hope that you find the video useful and enjoy learning about it!
Take a look below at the video to find out more!
We have another video on this showing Dynamic array with =SORT and =SORTBY also.
For more information please contact us on johnlegge@jplcomputer.co.uk and for all our videos please subscribe to our YouTube channel here.
We hope you liked this hint and tip on the FILTER vs VLOOKUP Function in Excel, why not take a look at our previous one on Word Column and Section breaks?