Let’s take a look at some of the key features of the XLOOKUP features:
1. XLOOKUP is more straight forward
- The value you are looking for
- The list where this value should be found
- The list from which you want the result
- [optional] value if not found
2. 4th parameter to support value not found
3. XLOOKUP offers additional parameters to VLOOKUP
4. It returns reference as output
(meaning you can combine XLOOKUP outputs in innovative ways with other formulas)
Let’s look at some working examples of how to use the XLOOKUP function:
This example is from the video above, and uses a simple XLOOKUP to look up a country name, then return its telephone country code. It only includes the lookup_value (cell F2), lookup_array (range B2:B11), and return_array (range D2:D11) arguments. It does not include the match_mode argument, as XLOOKUP defaults to an exact match.
Note: XLOOKUP is different from VLOOKUP in that it uses separate lookup and return arrays, where VLOOKUP uses a single table array followed by a column index number. The equivalent VLOOKUP formula in this case would be: =VLOOKUP(F2,B2:D11,3,FALSE)
In this example, we're looking up employee information based on an employee ID number. Unlike VLOOKUP, XLOOKUP is able to return an array with multiple items, which allows a single formula to return both employee name and department from cells C5:D14.
Try it yourself and let us know what you think. If XLOOKUP is not yet appearing in your function library you look our for it in your next update.
View our Excel Courses