Side-by-side syntax, the four VLOOKUP failure modes XLOOKUP removes, and when older workbooks still force your hand.
VLOOKUP is the function most people learn first and the one that breaks most often. XLOOKUP was designed to remove its structural weaknesses, and in current versions of Microsoft 365 it should be your default.
Syntax side by side
=VLOOKUP(A2, Staff!A:D, 3, FALSE)
=XLOOKUP(A2, Staff[ID], Staff[Department], "Not found")The XLOOKUP version names the column it returns instead of counting to it, so inserting a column in the source table does not silently corrupt the result.
Four VLOOKUP problems XLOOKUP removes
| Problem | With VLOOKUP | With XLOOKUP |
|---|---|---|
| Column inserted in source | Returns the wrong column | Unaffected |
| Lookup value to the right | Not possible directly | Works normally |
| No match found | #N/A unless wrapped in IFERROR | Built-in if_not_found argument |
| Exact match | Must remember FALSE | Exact match by default |
When VLOOKUP still appears
- Files shared with users on older Excel versions where XLOOKUP is unavailable
- Inherited workbooks where a rewrite carries more risk than it removes
- Assessments and interviews that still test VLOOKUP explicitly
Errors and what they mean
- #N/A — the value genuinely is not there, or one side is text and the other is a number
- #REF! — the column index points outside the range, usually after a deletion
- A blank-looking mismatch — trailing spaces; wrap the lookup value in TRIM
Where you need many matching rows rather than one, neither function is right — use FILTER instead.
Frequently asked questions
- Is XLOOKUP faster than VLOOKUP?
- On typical business datasets the difference is not what you notice. The real gain is reliability: XLOOKUP does not break when columns move and handles missing values without extra wrapping.
- Why does my XLOOKUP return #N/A when the value looks identical?
- Almost always a type or spacing mismatch — an ID stored as text on one side and a number on the other, or a trailing space from an export. TRIM and a value-type check resolve most cases.
Learn this properly, with a trainer
Advanced Excel with Raj Singh — classroom in Vasai or live online. Batch timings are confirmed when you enquire.
See course options & fees