FILTER vs VLOOKUP Function in Excel – which one should I use?

FILTER vs VLOOKUP Function in Excel – have you used both? Which do you prefer?

FILTER vs VLOOKUP Function in Excel: Excel iconThis week’s hint and tip is about the FILTER vs VLOOKUP function in Excel. We are looking at how you can take a simple XLOOKUP a step further through nesting it inside another. This isn’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 thesis ramped up the level a few notches as 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?

Cookie Control

Cookie control

We have placed cookies on your device to help make this website better.

I'm fine with this

We use cookies to give you the best online experience.

Please let us know if you agree to all of these cookies.

Some of the cookies we use are essential for the site to work.

We also use some non-essential cookies to collect information for making reports and to help us improve the site. The cookies collect information in an anonymous form.

To control third party cookies, you can also adjust your browser settings.

I'm fine with this
(One cookie will be set to store your preference)
(Ticking this sets a cookie to hide this popup if you then hit close. This will not store any personal information)
Information and Settings Cookie policy