Apr 3, 2021
How to use XLOOKUP in Excel?
XLOOKUP is the replacement to VLOOKUP and HLOOKUP function in Office 365.
Note: Formulas in this article require Office 365
It’s a powerful replacement to one of Excel’s most popular functions. This new function solves some of VLOOKUP’s limitations and can do much more too.
Lets see first how the function works.
The XLOOKUP function searches for an item in a range or table, and then returns the item corresponding to the first match it finds.
=XLOOKUP( lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
The XLOOKUP function requires just three pieces of information. And other three are optional.
1. Lookup_value: Value you want to look for
2. Lookup_array: Range where you want to look this value
3. Return_array: Range from where you want the result
4. [optional] [if_not_found]: Value if item not found
5. [optional] [match_mode]: Type of match: exact or next smaller or next larger or wild card
6. [optional] [search_mode]: Direction of search: from top or from last
Here are few practical examples to explain how XLOOKUP works and how it overcomes some of the limitations of its predecessor VLOOKUP.
No more counting of columns!
In VLOOKUP we are used to counting the number of columns where the resulting data is located, and also entering the 4th argument to specify exact or approximate match.
Using XLOOKUP we just have to specify three arguments. And by default the function matches exact value.
This is indeed neat.
In the XLOOKUP formula in below example:
- B4 — Value you want to look for
- B7:B10 — Range where you want to look for B4
- G7:G10 — Range from where you want the result
and you are done! As simple as 1–2–3
No more IFERROR or IFNA!
XLOOKUP handles error through its 4th argument. So no more extra IFERROR or IFNA function required to handle #N/A values.
On your left? No problem!
With VLOOKUP the solution to lookup on the left is complicated. Other option is to use INDEX & MATCH. But why bother?
XLOOKUP works just fine whether the result column is on left or right.
Want approximate match? No need to sort!
Yes VLOOKUP can be used to find approximate match and does pretty good job in doing so.
And XLOOKUP also can do the same using its 5th argument — [match_mode]
Values [match_mode] argument can take are:
0 — Exact match. If none found, return #N/A. This is the default.
-1 — Exact match. If none found, return the next smaller item.
1 — Exact match. If none found, return the next larger item.
2 — A wildcard match where *, ?, and ~ have special meaning.
Here is VOOKUP & XLOOKUP in action on same set of data
But, where XLOOKUP excels over VLOOKUP is where table is not sorted lowest to highest.
See here XLOOKUP gives correct result in reverse sorted table too.
Go ahead now and replace all your VLOOKUPs with XLOOKUPs!
