Computer Training & Application Solutions made Simple
Contact us for a free consultation
  • Click to send us an email. We'd love to hear from you!   Ottawa       613-656-4229
  • Click to send us an email. We'd love to hear from you!   Montreal    514-342-9494

MICROSOFT EXCEL XLOOKUP FUNCTION

Courtesy of
DJH Training & Application Solutions Inc.

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, 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 the XLOOKUP syntax:

 

DETAILED EXPLANATION

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!

 



More Newsletter Articles/a>

© 2022 DJH Training & Application Solutions Inc.

WOULD YOU LIKE TO USE THIS ARTICLE IN YOUR NEWSLETTER, EZINE OR WEB SITE? You can, as long as you include this complete blurb with it: An entrepreneur at heart, Deborah J. Sparks established DJH Training & Application Solutions Inc. over 25 years ago, realizing her dream of creating a company known for providing high quality computer software training, application development and support and exercising her passion for teaching. To learn more and to register for DJH's free newsletter, Innovations, please visit www.djhsolutions.ca.