XLOOKUP vs VLOOKUP
XLOOKUP is the modern replacement for VLOOKUP. It defaults to an exact match, can return values to the left or right, lets you set a not-found message and keeps working when columns are inserted. VLOOKUP still works in every Excel version, so it remains useful for files shared with people on Excel 2019 or earlier.
XLOOKUP vs VLOOKUP at a glance
| Feature | XLOOKUP | VLOOKUP |
|---|---|---|
| Default match | Exact match | Approximate match unless you add FALSE (or 0) |
| Lookup direction | Any direction: left, right, up or down | Only to the right of the first column |
| What you return | A return range (return_array) | A column number (col_index_num) |
| Inserting a column | Keeps working, because ranges move with the data | Can return the wrong column if the column number no longer fits |
| Not-found result | Built-in if_not_found argument | #N/A unless you wrap it in IFNA or IFERROR |
| Search from the last row | Yes, search_mode -1 | No |
| Return several columns | Yes, one formula can spill several columns | One column per formula |
| Wildcards | Yes, with match_mode 2 | Yes, in exact-match mode |
| Version support | Microsoft 365, Excel 2021, 2024, web and current Mac | Every Excel version |
The same task written both ways
The examples assume product codes in A2:A100, product names in B2:B100, prices in C2:C100 and the value you are looking for in E2.
| Task | VLOOKUP (or INDEX/MATCH) | XLOOKUP |
|---|---|---|
| Find a price by product code (exact match) | =VLOOKUP(E2,A2:C100,3,FALSE) | =XLOOKUP(E2,A2:A100,C2:C100) |
| Look to the left: find a code from a product name in column C | =INDEX(A2:A100,MATCH(E2,C2:C100,0)) | =XLOOKUP(E2,C2:C100,A2:A100) |
| Show a message when the code is missing | =IFNA(VLOOKUP(E2,A2:C100,3,FALSE),"Not found") | =XLOOKUP(E2,A2:A100,C2:C100,"Not found") |
VLOOKUP can't look left, so the second task needs INDEX/MATCH in older versions.
How to use XLOOKUP
Syntax: =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
- lookup_value: what you are looking for, such as a product code in E2.
- lookup_array: the column or row to search, such as A2:A100.
- return_array: the column or row to return from, such as C2:C100. It must be the same size as lookup_array.
- if_not_found (optional): text or a value to show when there is no match, such as "Not found".
- match_mode (optional): 0 exact (default), -1 exact or next smaller, 1 exact or next larger, 2 wildcard.
- search_mode (optional): 1 first to last (default), -1 last to first, 2 and -2 binary search on sorted data.
When VLOOKUP is still fine
Use VLOOKUP, or INDEX/MATCH, when the workbook must open in Excel 2019 or earlier, for example a file you share with a client or a team on an older version. In those versions XLOOKUP shows #NAME?. For a simple lookup to the right in a stable table, VLOOKUP with FALSE as the last argument works well.
FAQ
What is the difference between XLOOKUP and VLOOKUP?
XLOOKUP searches one range and returns a value from another range, so it can look left or right, defaults to an exact match and lets you set a not-found message. VLOOKUP only looks right from the first column, needs a column number and defaults to approximate match unless you add FALSE.
Is XLOOKUP available in my version of Excel?
XLOOKUP is in Excel for Microsoft 365, Excel 2021, Excel 2024, Excel for the web and current Excel for Mac. It is not in Excel 2019, 2016 or earlier. If you share a workbook with someone on an older version, XLOOKUP cells show a #NAME? error, so use VLOOKUP or INDEX/MATCH there.
Should I stop using VLOOKUP?
For new work in Microsoft 365, XLOOKUP is usually the better choice because it is harder to break when columns are inserted and it handles missing values cleanly. VLOOKUP still works and is fine for simple right-hand lookups or files that must open in older Excel versions.
More functions with examples: Excel formulas cheat sheet. Keyboard shortcuts: Excel shortcuts cheat sheet.
Last updated: