The modern lookups, and what each error means

Two things VLOOKUP cannot do

VLOOKUP searches only the table's first column and counts its return column by hand. Two later tools remove both limits. Both exist in Google Sheets and in Excel 365 / 2021 / 2024 — XLOOKUP is not in Excel 2019 or earlier, which is why INDEX/MATCH is still taught.

Worked example — the price lookup, three ways

The lines sheet again: SKU in B2, products in H2:J4.

XLOOKUP names the search column and the return column separately:

=XLOOKUP(B2, $H$2:$H$4, $J$2:$J$4, "missing")

Arguments: the value to find, the lookup array (where to search), the return array (what to bring back), and an optional if-not-found value. The default match mode is exact (mode 0), so there is no FALSE to remember, and because the two arrays are named independently the return column can sit to the left of the search column. With "missing" supplied, line 5's D400 shows missing instead of #N/A; leave the argument out and #N/A is what comes back.

INDEX/MATCH does the same in two functions. MATCH(value, range, 0) returns the position of the value in a one-column range — 0 means exact match (1 and -1 are approximate, ascending and descending) — and INDEX(range, position) returns the cell at that position:

=INDEX($J$2:$J$4, MATCH(B2, $H$2:$H$4, 0))

MATCH("C300", H2:H4, 0) is 3; INDEX(J2:J4, 3) is 180. Because INDEX takes any range, it looks left as easily as right, and it works in every spreadsheet version. Notice that it is R's products$uni{t}_{p}rice[match(lines$sku, products$sku)] from Lesson 6.3, function for function.

IFNA wraps a lookup that may miss:

=IFNA(VLOOKUP(B2, $H$2:$J$4, 3, FALSE), "missing")

IFNA(value, value_if_na) returns the lookup's result unless it is #N/A, in which case it returns the substitute. Its older sibling IFERROR(value, value_if_error) swallows every error — including a #REF! from a deleted column or a #NAME? from a typo — so a broken formula reads as a tidy "missing". Use IFNA for lookups; keep the other errors visible.

The five error values

Each error value names its cause, and a quiz will ask you to match them:

• #N/A — not available: a lookup found nothing (or, under approximate match, the value is below the first band).
• #DIV/0! — division by zero, including an AVERAGEIFS over no rows.
• #REF! — the formula points at a cell that no longer exists: the row or column it referenced was deleted. Typing over the reference does not fix it; the formula must be rebuilt.
• #VALUE! — the wrong type of value in an operation: text in arithmetic, such as ="abc"+1 or a number stored as text in a SUM operand.
• #NAME? — the formula uses a name Sheets does not know: a misspelled function (VLOKUP), a missing quote around text (IF(D2>500, Large, Small)), or an undefined named range.

Sheets and Excel add a few more (#NUM! for an impossible number, #CALC! when a dynamic array is empty), but these five are the ones on the paper.

Type three, then match errors to causes

Type the XLOOKUP, the INDEX/MATCH and the IFNA wrapper. The questions match errors to their causes.

CRISP-DM: a lookup is data preparation → integrate data; reading its errors is verify data quality.