XLOOKUP and Filter Functions in Excel – how can I use these newer functions in my spreadsheets? Watch the video to learn more

This week’s hint and tip is about XLOOKUP and Filter functions in Excel. This looks at these newer functions in Excel and how they can be used in spreadsheets. These aren’t covered in great detail in any of our courses so we decided to do a hint and tip on them.Dependent list data validation in Excel: Excel icon We are going to go through them now below.

 

XLOOKUP

XLOOKUP is the long awaited new function in Excel that everyone has been talking about! This new function allows you to match a value against a column and then return a value or multiple values on the same row to the left or right of the matched column. It has taken the VLOOKUP and made it a little more flexible in what it can do. Take a look in the video below to learn more!

 

Filter Functions

This FILTER function allows you to filter your data based on criteria you set but show the results separate to your raw data. This can be very useful as it allows you to still see all your data as it is, but also have your filtered results showing at the same time. Some examples of how this is done can be seen in the video below.

 

Newer functions added into Microsoft 365

Both of these new functions are only available to those with Microsoft 365 subscriptions and with the latest update. If you are unable to find them in your spreadsheets, try checking if there is an update pending.

 

 

The video below shows you how to use both of these functions in Excel. It goes through some explanation into them both and then also demonstrates how they work.

Take a look below at the video to find out more and then try it out on your own computer!

We hope you have enjoyed this hint and tip on XLOOKUP and Filter functions in Excel. Why not take a look at our previous one on different ways in which you can view a formula in Excel?

Cookie Control

Cookie control

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

I'm fine with this

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

You can use this tool to change your cookie settings. Otherwise, we’ll assume you’re OK to continue.

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