How to use XLOOKUP in Excel
XLOOKUP searches one range for a value and returns a result from another range. Unlike VLOOKUP, the search range and result range do not need to be part of the same table, and the result can be to the left or right of the search range.
XLOOKUP formula
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])- lookup_value: the item you want to find.
- lookup_array: the cells Excel should search.
- return_array: the cells that contain the answer.
- if_not_found: optional text to show when there is no match.
Example: find a product price
Enter this table in A1:B4, then type Mouse in D2.
| Row | A: Product | B: Price |
|---|---|---|
| 1 | Product | Price |
| 2 | Keyboard | 25 |
| 3 | Mouse | 12 |
| 4 | Monitor | 180 |
Enter this formula in E2:
=XLOOKUP(D2, A2:A4, B2:B4, "Product not found")The result is 12. If D2 contains a name that is not in A2:A4, Excel displays “Product not found” instead of an error.
Search in the other direction
Because XLOOKUP uses separate search and result ranges, the result range can be to the left. For example, if product IDs are in B2:B4 and product names are in A2:A4, this looks up an ID and returns its name:
=XLOOKUP(D2, B2:B4, A2:A4, "ID not found")Before you use XLOOKUP
- XLOOKUP is available in Microsoft 365 and newer Excel versions, but not in some older versions such as Excel 2016 and Excel 2019.
- Exact matching is the default, so you usually do not need to sort the list.
- If there are repeated values, XLOOKUP returns the first match by default.
For older Excel versions, see the VLOOKUP tutorial or the INDEX and MATCH example. To match two spreadsheet files, use the Excel Lookup tool.