How to Use XLOOKUP in Excel: Syntax and Examples
By Szabó Gergő · Updated
XLOOKUP searches one row or column and returns the value from the same position in another row or column. It is a clearer, more flexible replacement for many VLOOKUP and INDEX/MATCH formulas.
Syntax and arguments
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])- lookup_value
- The value you want to find.
- lookup_array
- The single row or column Excel should search.
- return_array
- The row or column containing the result.
- if_not_found
- Optional value to return instead of #N/A.
- match_mode
- Optional match behavior; 0 is exact match and is the default.
- search_mode
- Optional search direction, such as last-to-first with -1.
XLOOKUP examples
Product codes are in A2:A100, prices are in D2:D100, and the code to find is F2.
=XLOOKUP(F2,A2:A100,D2:D100,"Not found")Excel finds F2 in column A and returns the price from the same row in column D.
Employee IDs are in D2:D50 and names are in B2:B50.
=XLOOKUP(G2,D2:D50,B2:B50)Unlike VLOOKUP, the return column can sit to the left of the lookup column.
Customer IDs repeat in A2:A500 and transaction amounts are in C2:C500.
=XLOOKUP(F2,A2:A500,C2:C500,"No transaction",0,-1)Search mode -1 checks from the bottom and returns the last exact match.
Common mistakes
Lookup and return arrays have different sizes
Use ranges with the same number of rows or columns, such as A2:A100 and D2:D100.
Numbers are stored as text
Convert both the lookup value and source values to the same data type before matching.
Using wildcards without match mode
Set match_mode to 2 when the lookup value contains * or ? wildcards.
XLOOKUP FAQ
Is XLOOKUP better than VLOOKUP?
For modern Excel versions, usually yes. XLOOKUP defaults to exact matching, can return values from either side, and does not rely on a fragile column number.
Why does XLOOKUP return #N/A?
Excel did not find a match. Check spaces and data types, or provide the optional if_not_found argument for a friendly fallback.
Can XLOOKUP return multiple columns?
Yes. Use a multi-column return array, such as C2:E100, and Excel will spill the matching row across adjacent cells.