XLOOKUP vs VLOOKUP: Which Should You Use?
Choosing between XLOOKUP and VLOOKUP is one of the highest-traffic Excel questions. This guide compares behavior, error handling, and when to keep VLOOKUP for compatibility.
Side-by-side comparison
| Topic | VLOOKUP | XLOOKUP |
|---|---|---|
| Lookup direction | Right of key only | Any column / row |
| Default match | Approximate unless FALSE | Exact |
| If not found | #N/A | Custom if_not_found |
| Column insert safety | Breaks with column index | Return range is explicit |
| Excel version | All modern Excel | Microsoft 365 / 2021+ |
XLOOKUP patterns
=XLOOKUP(E2, A2:A1000, B2:B1000, "Not found")
=XLOOKUP(E2, A2:A1000, B2:D1000)
=XLOOKUP(E2, A2:A1000, B2:B1000, , -1)Prefer a single return column on dense sheets to avoid #SPILL!.
When VLOOKUP is still useful
- Files must open in Excel 2016 or older
- Shared templates on mixed versions
=IFNA(VLOOKUP(E2, A:B, 2, FALSE), "Not found")Migration checklist
- Replace approximate VLOOKUP with intentional XLOOKUP match modes
- Stop using column numbers; select the return range
- Use if_not_found instead of blanket IFERROR
Related errors
XLOOKUP not found · VLOOKUP hidden characters · Error directory
ExcelFormulaAI · Nikhil Kappagantula