I’ve often called VLOOKUP() the “duct tape of Excel” for its ability to combine data from two sources. It’s a phrase I even included in my book Advancing into Analytics, and I had a graphic commissioned to illustrate it:
While VLOOKUP() has its uses, XLOOKUP() is poised to replace it with its advanced features. As one of Excel’s new dynamic array formulas, XLOOKUP() offers several benefits over the classic lookup function. Check it out in this blog post:
How XLOOKUP() works
In most ways, XLOOKUP() should prove familiar to VLOOKUP() users: it’s used to retrieve data from one table into another given a shared lookup value. That said, XLOOKUP() provides several more flexible, sophisticated search methods. Some of particular interest to new XLOOKUP() users:
| Feature | VLOOKUP() |
XLOOKUP |
|---|---|---|
| Search direction | Can only search vertically | Can search both vertically and horizontally |
| Return direction | Can only return values to the right of lookup value | Can return values from columns to the left and right of lookup |
| Error handling | Returns #N/A if the value is not found | Can specify default value for unmatched items and handle errors |
Due to its more complex nature, the XLOOKUP() takes six possible parameters in total versus VLOOKUP()‘s four. We will focus on the first four parameters, one of which is optional:
XLOOKUP() parameter |
Explanation | VLOOKUP() equivalent |
|---|---|---|
lookup_value |
The value to search for in the lookup array | lookup_value |
lookup_array |
The range of cells to search for the lookup_value | table_array |
return_array |
The range of cells to return the matching value(s) | Not applicable |
[match_mode] |
An optional parameter that specifies the type of match to be performed | range_lookup |
Enough description of how XLOOKUP() can work! Let’s see it in action.
A basic XLOOKUP()
Let’s get started with a simple example in the data: the sales table contains a country_id for which we’d like to find the corresponding country_name. That makes the country_id our lookup array and country_name our return array, respectively:
You might notice that lookups for country 99 returns an error. Returning an #N/A for a missing match in VLOOKUP() is undesirable because it can cause errors in calculations and make it difficult to spot missing data. Additionally, it may not be immediately clear why the #N/A is being returned, which can lead to confusion for users.
To handle #N/A errors with VLOOKUP(), you can use the IFERROR() function to return a specific value or message, such as “Not found” or “Data unavailable” instead of the error message. But rather than combine VLOOKUP() with IFERROR(), we’ll use XLOOKUP, which can handle errors directly within its formula without the need for an additional function.
XLOOKUP() and error handling
To add a custom error message to the XLOOKUP() statement, simply fill in the fourth, optional parameter. In this case, you learned that countries assigned to the number 99 should be marked as “Other:”
Now that we looked country names into our table, let’s try doing so with product names.
XLOOKUP() and looking up to the left
Perhaps the most common complaint about VLOOKUP() is its inability to search to the left of the lookup column (at least without the use of helper functions like CHOOSE()).
By contrast, XLOOKUP() is not limited to looking up based on index columns to the right of the lookup table. Instead, it can look through values of any other Excel range, including a table column to the left of the lookup value:
XLOOKUP() and Modern Excel
With its ability to search in both vertical and horizontal directions, return values from columns to the left and right of the lookup value, and handle errors directly within its formula, XLOOKUP() is quickly becoming the go-to formula for data retrieval in Excel. But are you convinced?
Or are you sticking with VLOOKUP() or another lookup method for now? Let me know in the comments.
Leave a Reply