Microsoft has announced the rollout of XLOOKUP – a powerful new function designed to address many of the known limitations of VLOOKUP.
What is XLOOKUP
How it XLOOKUP works
Here is the signature (arguments) for XLOOKUP:
=XLOOKUP(lookup_value, lookup_array, return_array, [match_mode], [search_mode])
It looks a bit scary at first, but only the first three arguments are required. The last two in [square brackets] are optional.
Note: The formulas will only work if you are on a version of Excel that supports
Pros or Advantages of
Here is the list of advantages for XLOOKUP that I shared in the video above.
- Defaults to exact match.
- It only requires three arguments, instead of four for VLOOKUP or INDEX MATCH.
- Works both vertically and horizontally.
- One function instead of two, compared to INDEX MATCH.
- Can do partial match lookups with wildcard characters (4th argument = 2).
- Can do lookups in reverse order (5th argument = -1).
- Returns a range instead of a value (advanced nested formulas).
Cons or Drawbacks of
There are also a few potential issues to be aware of.
- Additional [optional] arguments can make the function look overwhelming to new users.
- Returns a #VALUE! error if the lookup and return arrays are not the same length. I explain this in the video above.
- It can be time-consuming to select two ranges with the mouse, especially when you have thousands of cells in the arrays.
- You must remember to make both the lookup and return ranges absolute references (F4 on the keyboard) if copying the formula down/across.
- You must use nested functions to do a 2-dimensional lookup. Can use two XLOOKUPs or INDEX MATCH.