XLOOKUP
How to use XLOOKUP in Excel (with examples)
XLOOKUP (Excel 365 / 2021+) combines the power of VLOOKUP and INDEX+MATCH in one simple function.
Syntax
=XLOOKUP(lookup_value, lookup_range, return_range, [if_not_found])
- lookup_value — The value to find.
- lookup_range — The range to search in.
- return_range — The range of values to return.
- if_not_found — Optional. The text or number to show if the value isn't found.
Example
Formula:
=XLOOKUP("P01",A2:A4,C2:C4)→ result: 8000
Advantages
Exact match by default, it can search to the left, you can show your own message instead of #N/A, and there's no need to count column numbers.
Which versions have it
Microsoft 365, Excel 2021 and Excel 2024. It is not available in Excel 2016/2019 — use INDEX+MATCH there.
Put it into practice
Do interactive exercises on this topic in the Excel simulator.
Start exerciseFrequently asked questions
Your version of Excel doesn't have XLOOKUP. Use INDEX+MATCH or upgrade to Microsoft 365.
Yes, if the 6th argument (search mode) is -1, XLOOKUP searches from the bottom up.