XLOOKUP vs INDEX/MATCH: which one, and when it actually matters
XLOOKUP is better in almost every case. The exceptions are real, though, and worth knowing before you rewrite a model.
The short answer: use XLOOKUP if everyone opening the file is on Microsoft 365 or Excel 2021. Use INDEX/MATCH otherwise. But the reasoning is worth understanding, because it tells you what to check when a lookup breaks.
What XLOOKUP fixed
=XLOOKUP(A2, $B$2:$B$500, $D$2:$D$500, "Not found")Four things this gets right that VLOOKUP got wrong:
- Exact match by default. No fourth argument to forget.
- Looks in any direction. The return range is independent of the lookup range.
- Column-insertion safe. There is no positional column index to invalidate.
- Built-in fallback. No IFERROR wrapper needed.
What INDEX/MATCH still does well
=INDEX($D$2:$D$500, MATCH(A2, $B$2:$B$500, 0))Compatibility. It works in every Excel version back to 2007, and in Google Sheets and LibreOffice without translation. If your file leaves your organisation, this matters more than elegance.
Reusing one MATCH across many columns. If you are pulling twelve fields for the same key, compute MATCH once into a helper cell and reference it twelve times. That is twelve lookups collapsed into one — a measurable difference on large models. XLOOKUP recomputes its search for each call.
Explicit control. MATCH's third argument makes the match type visible in the formula rather than implied by a default.
Performance
On a 100,000-row lookup range the two are close enough that the difference is not worth restructuring a model for. Both are dramatically faster than VLOOKUP over a wide table range, because VLOOKUP loads the entire table width while both alternatives read only two columns.
What genuinely matters for performance is the *range size*. B:B forces evaluation across a million rows. $B$2:$B$500 does not. That single change outweighs the choice between the two functions.
The migration trap
Rewriting VLOOKUP as XLOOKUP across an existing model introduces a subtle risk: any VLOOKUP that was silently relying on approximate match — a tiered commission table, a tax band lookup — will change behaviour, because XLOOKUP defaults to exact.
Before a bulk rewrite, run Formula Checker over the model and list every VLOOKUP without an explicit FALSE. Those are the ones that need a decision rather than a find-and-replace.
Recommendation
New work, modern Excel: XLOOKUP. Files that travel outside your control: INDEX/MATCH. Existing models: leave working formulas alone and change them only when you are already touching that logic for another reason.