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
- Xlookup can return a value to the right or left of the lookup value
- Xlookup by default performs an exact match
- Xlookup can return more than one value
- Xlookup does not need the lookup column to be in sort order
- Xlookup can search top to bottom or bottom to top
- Xlookup lets you specify a custom message if the lookup value is not found
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.
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.