Data Analyst · #041 · September 2, 2026 · 2 min read
VLOOKUP, INDEX/MATCH or XLOOKUP: which should you use?
The three spreadsheet lookups side by side: what each one does, which to use where, the IFERROR and locked-range pro moves, and the approximate-match default that quietly fills reports with wrong rows.
Get the free PDF
One page, print-ready, free to share. No signup needed.
Every broken spreadsheet has a lookup at the bottom of it. Here are the three lookups side by side, which one to reach for, and the default that fills reports with wrong rows. The print-ready A4 PDF is at the bottom.
VLOOKUP
VLOOKUP(id, A:D, 3, 0): return column 3 of the range.- Last argument
0: exact match. Always. - Looks right only: the key must be in column 1.
INDEX / MATCH
MATCH(id, A:A, 0): find the row.INDEX(C:C, row): read any column.- Looks in any direction: the key can be anywhere.
XLOOKUP
XLOOKUP(id, A:A, C:C): one clean call.- 4th argument: your own "not found" text.
- Exact match by default. Finally.
Same lookup, three ways
=VLOOKUP(A2, Data!A:D, 4, 0): counts to column 4 by hand, breaks if a column is inserted.=XLOOKUP(A2, Data!A:A, Data!D:D, "?"): points at the column itself, survives inserts, has a fallback.- INDEX/MATCH is XLOOKUP for workbooks older than 2020: same idea, two calls.
Choosing
- Modern Excel or Sheets: XLOOKUP.
- Old workbooks: INDEX/MATCH.
- Quick lookup, key in column 1: VLOOKUP is fine.
Pro moves
IFERROR(lookup, ""): no more #N/A walls.- Lock the ranges:
$A:$Dsurvives copy-paste. - Two keys? Build a helper column:
A2&"|"&B2.
The trap: the TRUE default
| You wrote | What happens | Write instead |
|---|---|---|
VLOOKUP(id, A:D, 3) | nearest match below: wrong row | VLOOKUP(id, A:D, 3, 0) |
| lookup on unsorted data | TRUE mode needs sorted keys | exact match, always |
| #N/A in a SUM column | the whole SUM turns #N/A | IFERROR(lookup, 0) |
Forget the last argument and VLOOKUP does not fail. It guesses. Approximate match exists for tax brackets and grade bands; for ids it is a silent data bug.
Frequently asked questions
What is the difference between VLOOKUP and INDEX/MATCH?
VLOOKUP(id, A:D, 3, 0) counts to a column number and can only look to the right of the key column. INDEX/MATCH splits the job: MATCH(id, A:A, 0) finds the row, INDEX(C:C, row) reads any column, in any direction. That makes INDEX/MATCH survive inserted columns and keys that are not in column 1.
Should I use XLOOKUP instead of VLOOKUP?
Yes, if your Excel or Sheets version has it. XLOOKUP(id, A:A, C:C) points at the lookup and return columns directly, defaults to exact match, survives column inserts, and its fourth argument is a built-in not-found fallback. Keep INDEX/MATCH for old workbooks; VLOOKUP is fine for a quick lookup with the key in column 1.
Why is my VLOOKUP returning the wrong value?
Almost always the last argument. VLOOKUP(id, A:D, 3) without the final 0 uses approximate match by default: it returns the nearest value below your key, which on unsorted data means a wrong row with no error. Two other classics: an inserted column shifting your hardcoded column number, and text "42" never matching the number 42.
How do I stop #N/A errors from breaking my spreadsheet?
Wrap the lookup: IFERROR(lookup, "") for display, IFERROR(lookup, 0) when the column feeds a SUM, because a single #N/A turns the whole SUM into #N/A. Fixing the fallback per formula beats hiding errors sheet-wide, since a wall of #N/A is sometimes telling you the keys do not match.
Get the free PDF
One page, print-ready, free to share. No signup needed.