XLOOKUP in Excel: How to Use It
XLOOKUP finds a value in one column and returns a result from another. Here is the syntax and the examples you will actually use.
XLOOKUP syntax
- lookup_value: what you are looking for.
- lookup_array: the column or row to search.
- return_array: the column or row to return a value from.
- if_not_found: text to show when there is no match.
- match_mode: 0 exact (default), -1 exact or next smaller, 1 exact or next larger, 2 wildcard.
- search_mode: 1 first to last (default), -1 last to first.
Basic example
To find the price of the product in E2 using a product list in A2:A50 and prices in C2:C50:
Show a message when nothing is found
Look up to the left
VLOOKUP can only return values to the right of the lookup column. XLOOKUP does not care about column order, so you can search column C and return column A.
Return several columns at once
This spills the matching row across three columns.
Two-way lookup
XLOOKUP vs VLOOKUP
XLOOKUP defaults to exact match, looks in any direction, has a built-in not-found option and does not break when you insert a column. VLOOKUP needs a column number and an extra FALSE argument for exact match. XLOOKUP is available in Excel 365 and Excel 2021 or later. In older versions use VLOOKUP or INDEX MATCH.
Frequently asked questions
Is XLOOKUP better than VLOOKUP?
In most cases yes. It looks left, defaults to exact match, handles missing values and does not break when columns are inserted.
Which Excel versions have XLOOKUP?
Excel 365 and Excel 2021 or later. Excel 2019 and older do not include it.
How do I use XLOOKUP with multiple criteria?
Join the criteria with an ampersand, for example =XLOOKUP(A2&B2,Sheet2!A:A&Sheet2!B:B,Sheet2!C:C), or use FILTER.
Why does XLOOKUP return #N/A?
No match was found. Add a fourth argument such as “Not found” or check for extra spaces in the data.
Can XLOOKUP search from the bottom up?
Yes. Set the search_mode argument to -1 to find the last match.
Need Excel work done for you?
More free tools: All free calculators · Excel automation services · Excel templates · Budget template