VLOOKUP, INDEX/MATCH or XLOOKUP: which to use when the file has to open everywhere

The same lookup three ways

An items list on a Setup sheet — name in column A, unit price in column C — and an invoice line that should fill the price from the name in B12:

=VLOOKUP(B12, Setup!$A$5:$C$60, 3, FALSE)
=INDEX(Setup!$C$5:$C$60, MATCH(B12, Setup!$A$5:$A$60, 0))
=XLOOKUP(B12, Setup!$A$5:$A$60, Setup!$C$5:$C$60, "")

Same result. The differences are in what happens next.

VLOOKUP: fine until someone inserts a column

3 means “the third column of the table”. Insert a column between A and C on Setup and every VLOOKUP now returns the wrong thing, silently. It also can’t look left — the key must be the first column of the table — and the whole table is passed even though one column is read, which matters on Google Sheets with big ranges.

The one thing to always do with it: the fourth argument. FALSE (or 0) means exact match. Leave it out and VLOOKUP assumes the list is sorted and returns the nearest value below — which for a name list means a plausible-looking wrong price.

INDEX/MATCH: two functions, no surprises

MATCH finds the row number of the key in one column; INDEX reads that row from another column. Because the two columns are named separately, you can insert or move columns freely, look in any direction, and the formula reads the two ranges it needs and nothing else.

The 0 in MATCH is the exact-match switch, same as VLOOKUP’s FALSE. Don’t omit it.

MATCH once in a helper cell, then INDEX twice:
E12: =MATCH(B12, Setup!$A$5:$A$60, 0)
C12: =IFERROR(INDEX(Setup!$C$5:$C$60, $E12), 0)      price
D12: =IFERROR(INDEX(Setup!$D$5:$D$60, $E12), "")     unit

This is the pattern in every workbook we ship — the invoice pulls client details and item prices this way, the rota reads hours and rates from a code table this way.

XLOOKUP: the nicest syntax, the narrowest reach

XLOOKUP(key, lookup_range, return_range, if_not_found) is INDEX/MATCH in one call with the not-found case built in, exact match by default, and it looks any direction. If everyone who opens your file has it, use it.

They may not. XLOOKUP arrived in Excel 365 and Excel 2021; Excel 2016 and 2019 show #NAME?. Google Sheets has it; Numbers added it in 2020; LibreOffice in 24.8. A template listing that says “Excel” is promising Excel 2016 users a working file, and XLOOKUP breaks that promise for them with no warning until they open it.

Not found: IFERROR, not IFNA everywhere

A name not in the list gives #N/A. Wrap the lookup: =IFERROR(INDEX(…), 0) for numbers, "" for text. IFNA is more precise (it only catches #N/A) but isn’t in Numbers; IFERROR is everywhere. In a numeric column, return 0, not "" — a "" in the middle of a price column breaks the arithmetic downstream, as we found out the hard way.

Two keys without an array formula

“The second event on 15 September”, “the shift for this person on this day”: two conditions. The array version — MATCH(1, (A=x)*(B=y), 0) — needs Ctrl+Shift+Enter in older Excel and behaves differently in Numbers. Build a key column instead:

Events sheet, helper columns:
D5: =IF(A5="",0,COUNTIF($A$5:A5,A5))          1 for the first event on that date, 2 for the second
E5: =IF(A5="",0,A5*10+D5)                      date serial × 10 + number → one unique number per event

Calendar cell (date in B8), first event of the day:
=IFERROR(INDEX(ev_text, MATCH(B8*10+1, ev_keys, 0)), "")
second:  MATCH(B8*10+2, …)

Numeric keys, plain MATCH, works in Excel 2016 and Numbers. For text pairs, A5&"|"&B5 as the key and x&"|"&y in the lookup does the same.

Approximate match, on purpose

The one case for a non-exact lookup: bands. Tax brackets, shipping by weight, grade boundaries. MATCH(value, thresholds, 1) with thresholds ascending returns the last band the value clears — no nested IFs. Keep the 1 explicit and the thresholds sorted, and write a note next to the table saying so.

What we do

INDEX/MATCH with 0, wrapped in IFERROR, reading named ranges on a Setup sheet. It’s a few characters longer than XLOOKUP and it opens in everything. The Formulas sheet in each Pro workbook shows every lookup in the file written out this way.