This week, Avram Piltch discusses a new feature in Microsoft Excel: XLOOKUP. Now, we don't usually show off things like Microsoft Office functions, but this one is truly special. The predecessor to XLOOKUP, which is VLOOKUP, is the third most used function in Excel, and XLOOKUP expands on its capabilities by removing a number of limitations.
Both functions allow you to combine data from different sheets or files by joining on common data. For example, if you have one document with employee IDs and names, and another with employee IDs and addresses and phone numbers, these functions will allow you to join on the common data item, the employee IDs, and show the combined data. However, there are some significant differences between the old and new methods.
The most important change is in how you match data. With VLOOKUP, you could only do exact matches between sheets. With XLOOKUP, however, there are several ways of joining data. You can do the exact match, but you can also have it choose values above and below the closest match, or, most importantly, use wildcards. For example, if you want to return the first employee whose last name starts with S, you can search for "S*" rather than using an entire cell value.
Equally important is the amount of data that can be returned. With the older VLOOKUP, you could only return a single column worth of data per query. This means that if you wanted to return first name, last name, and address from one sheet, you would have to do three separate lookups. That means processing power and additional scripting to return everything you want. With the new XLOOKUP, however, you can return multiple columns in a single query. So, one lookup could return the first, last, and address.
In addition to all of this, the function call is actually shorter for XLOOKUP, making it easier to use. If you're about to learn about joining data in Excel, XLOOKUP is definitely the way to go.
