fbpx
What is Xlookup in Excel?

What is Xlookup in Excel?

Did you know there is a new lookup function available in Excel? If you love the VLOOKUP or HLOOKUP functions, then you really need to check out the new XLOOKUP.

The Vlookup (vertical lookup) and Hlookup (horizontal lookup) functions in Excel have long been a go-to option whenever we need to look up information from a table of data. The new Xlookup function builds on the functionality of Vlookup and even replaces many of the workarounds you would previously need to utilise to get Vlookup to function in certain scenarios. 

XLOOKUP Syntax

=XLOOKUP(Lookup_value, Lookup_array, Return_array, If_not_found, Match_mode)

Who can access XLOOKUP?

The new XLOOKUP function is available to all Microsoft 365 (M365) users’. Unfortunately if you have an older version of Excel including Excel 2019, you will not have access to this new function unless you upgrade to a Microsoft 365 subscription. This might just be the function that pushes you towards M365.

Compare Xlookup and Vlookup

When comparing the functionality of both the Xlookup and Vlookup/Hlookup functions a few things stand out. Here I’ve compiled some definitive benefits of using the new Xlookup over the Vlookup or Hlookup.

Benefits of Xlookup

Example

Here is an example of how to use Xlookup. In this case, I can enter a Staff ID into cell B2 which then performs an Xlookup on the Lookup_Value and returns the result for Staff Name and Department.

A noticeable difference in this data table is that my lookup values, being the staff ID’s, are not in ascending order which would not work if I was using Vlookup.

What is Xlookup in Excel?

Compatibility with other Excel versions

For the time being, Xlookup is only available to Microsoft 365 subscribers. If you are collaborating with other users within Excel who are using older versions of Excel, it may be safer to utilise the good-ole Vlookup or Hlookup. 

Want to learn more?

If you are interested in upskilling yourself or your team to utilise the newest features of Microsoft Excel, or any Microsoft 365 product, be sure to check out our online or face to face course options. 

Not sure which options would be suit your team? Contact us to discuss your needs and begin the process to undertake our free skills analysis.

Call Now Button