Learn how to use the XLOOKUP function under Excel 2010, Excel 2013, or newer versions. Using a small trick, we keep the backward compatibility. Wait a moment before we take a deep dive into each function. Take a first look at the situation we are in.
In 2020, Microsoft announced that the new XLOOKUP function for Microsoft Excel is now generally available for Microsoft 365 users.
Which version of Excel has XLOOKUP?
As usual, the function is available for the latest Microsoft 365 version. Okay, but what happened with everybody else? Should I upgrade my Excel and Office packages, or is there a compatible function?
We have splendid news for you.
Jump to the next chapter and discover how to use XLOOKUP under Excel 2010, 2013, and above.
XLOOKUP compatibility using a UDF
Many Excel VBA users follow a simple rule: If the given function does not exist, create it! This UDF was built for people who use older Excel versions that do not have access to that function.
The solution provides the chance to play with the new functionality.
How to implement the new function?
We have two choices to implement the function. Insert a VBA code manually or apply a ready-to-use solution with our UDF library (free Excel add-in).
Both solutions have the same output: You can use the XLOOKUP compatible function with earlier Excel versions.
Method 1:
Steps to add an XLOOKUP compatible function to your Workbook:
- Press Alt+F11 to open the Visual Basic Editor
- Right-click on the ThisWorkbook and insert a new (blank) module
- Copy the code below and paste it into the new module
- Close the VBA editor
- Save the Workbook in a .xlsm format
From now on, you can use the XLOOKUP function under Excel 2010 and above.
Method 2:
Install our free Excel UDF library. It can take only seconds. Follow this guide on how to install a new add-in. After the successful installation, you’ll see the DataFX tab on the ribbon.
If you want more info about the function, locate the LOOKUP tab.
If the add-in is installed, you can use the UDF library!