XLOOKUP is the only lookup you need to learn in 2026
VLOOKUP breaks when someone inserts a column, can’t look left and defaults to the wrong match type. Here’s how XLOOKUP fixes all three, with worked examples you can copy.
Retire VLOOKUP for good
On this page
Why VLOOKUP keeps breaking
VLOOKUP has been the first “real” formula most people learn, and it has three habits that cause most lookup bugs in shared workbooks.
- It counts columns.
=VLOOKUP(E2,A2:C5,3,FALSE)returns the 3rd column. Insert a column and it silently returns the wrong field. - It only looks right. The key must be the leftmost column of the range, so you end up rearranging data to suit the formula.
- It defaults to approximate match. Forget the
FALSEand you get a plausible-looking, wrong answer instead of an error.
The XLOOKUP syntax in one line
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])Instead of a column number you point at the column to return. Because both are real ranges, inserting or moving columns can’t break the formula. And it matches exactly by default.
Handle missing values without IFERROR
The fourth argument is what to show when nothing matches. No more wrapping everything in IFERROR, which also hides genuine mistakes like a typo in a range.
VLOOKUP vs XLOOKUP at a glance
| VLOOKUP | XLOOKUP | |
|---|---|---|
| Look to the left | No | Yes |
| Default match | Approximate | Exact |
| Survives inserted columns | No | Yes |
| Built-in not-found value | No (needs IFERROR) | Yes |
| Search bottom-up | No | Yes (search_mode −1) |
| Return several columns | No | Yes (spills) |
Try it in a live spreadsheet
Practise these formulas in interactive lessons with instant grading.
Priya Nair
Lead instructor at FluentExcel