How to Use XLOOKUP in Excel (with Examples)
XLOOKUP is the modern, all-in-one lookup that replaces VLOOKUP. The syntax is =XLOOKUP(lookup_value, lookup_array, return_array) — it looks in any direction and handles "not found" on its own. Here is exactly how it works.
XLOOKUP is Excel's modern, all-in-one lookup — the function Microsoft built to replace VLOOKUP. It looks in any direction, needs no column number, handles "not found" on its own, and defaults to an exact match. If you have ever fought with VLOOKUP, XLOOKUP will feel like a relief.
The XLOOKUP syntax, explained
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])| Part | Meaning |
|---|---|
| lookup_value | What you are searching for, like a product name |
| lookup_array | The column to search in |
| return_array | The column to return the answer from |
| if_not_found | Text to show when there is no match (optional) |
A real example
Say column A holds product names and column B holds prices. To find the price of the product typed in cell D1:
=XLOOKUP(D1, A2:A100, B2:B100)XLOOKUP searches A2:A100 for the value in D1 and returns the matching price from B2:B100. Notice there is no column number to count and no range-of-columns to select — you point at the search column and the answer column directly.
Handle "not found" without IFERROR
With VLOOKUP you had to wrap the whole formula in IFERROR to avoid an ugly #N/A. XLOOKUP has that built in — just add a fourth argument:
=XLOOKUP(D1, A2:A100, B2:B100, "Not found")Now, if the product is not in the list, the cell shows "Not found" instead of an error. Cleaner and easier to read.
Why use it instead of VLOOKUP?
- It looks in any direction. VLOOKUP can only return values to the right. XLOOKUP can return a value from a column on the left just as easily.
- No column number to count. VLOOKUP breaks when you insert a column, because its column index is hard-coded. XLOOKUP points at real ranges, so it keeps working.
- Exact match by default. VLOOKUP needs a final FALSE for an exact match. XLOOKUP does exact by default, so there is one less thing to forget.
- Built-in not-found handling. No more wrapping everything in IFERROR.
If XLOOKUP is not available in your version, the classic VLOOKUP and the flexible INDEX MATCH do the same job. You can also add logic to any result with an IF function, or do a lookup in Google Sheets with VLOOKUP in Google Sheets.
Frequently asked questions
Is XLOOKUP better than VLOOKUP?
For most work, yes. XLOOKUP looks in any direction, needs no column number, defaults to an exact match, and handles not-found on its own. The only catch is that it requires Microsoft 365 or Excel 2021 — on older versions you still need VLOOKUP or INDEX MATCH.
Why is XLOOKUP not showing up in my Excel?
XLOOKUP only exists in Microsoft 365 and Excel 2021 or later. If you are on Excel 2019, 2016, or earlier, the function is not available and Excel will return a #NAME? error. Use VLOOKUP or INDEX MATCH instead.
Can XLOOKUP return more than one column?
Yes. Point the return_array at several columns, like =XLOOKUP(D1, A2:A100, B2:D100), and XLOOKUP spills the matching values across those columns automatically in Microsoft 365.
Does XLOOKUP do an exact match automatically?
Yes. Unlike VLOOKUP, which needs FALSE at the end, XLOOKUP uses an exact match by default. You only change that with the optional match_mode argument if you want an approximate or wildcard match.