Use XLOOKUP to easily retrieve data (say goodbye to VLOOKUP!)
Would you like to look up employee information based on their employee number? We could do that with VLOOKUP (and LOOKUP/HLOOKUP) before Microsoft 365. but ran into problems if the columns were moved and if the data to retrieve was to the left of the column containing the lookup value. INDEX with MATCH was more flexible, but that was quite difficult to master.
Enter XLOOKUP! Available in Microsoft 365 and 2019, it is a flexible, modern way of looking up data. In the example below, we will retrieve employee information. Notice the 'key' column that contains the data we are looking up is the 4th column. It can be anywhere in the data!
Step 1. Set up your data correctly, preferably converting it to a table. Notice that two employees are duplicates, as they changed departments.
Step 2. Add the XLOOKUP formula the the Salary, Department and Manager columns for the results below:
Here is a breakdown of the XLOOKUP syntax:
XLOOKUP returns an exact match by default, although we can easily change this to a closest match or next smallest, closest match or next larger, or a wildcard match. Closest match no longer has to be sorted in ascending order.
The order of the columns makes no difference, as the lookup and return arrays are separate.
There is no need to nest an IFERROR function, as this is now an argument included in the XLOOKUP function.
There is now a Search Mode argument which is useful in this example, where we have the same employee number more than once, as employees have changed jobs over time. We can ask Excel to look for the latest date, salary, etc. in that case.
The new XLOOKUP function can also look up data horizontally.
Give the new XLOOKUP function a try, and let us know if you have any questions, or would like to arrange training on this
or other new Excel functions and features!
© 2022 DJH Training & Application Solutions Inc.