Level 7 · What a lookup returns for a missing key

Every lookup eventually meets a key that isn’t in the table: a discontinued product, a new customer the export doesn’t have yet, an id typed as text. What the formula does then matters more than what it does when everything matches, because that is the case nobody checks.

Three possible outcomes

On a missing key, a lookup can do one of three things:

Outcome	Example	Is it safe?
an error, #N/A	VLOOKUP(…,FALSE), MATCH(…,0)	yes: you see it
a fallback you chose	XLOOKUP(…,"discontinued")	yes, when you know the reason
a wrong value	VLOOKUP without FALSE	no: it looks like a real answer

#N/A means “not available”: the lookup ran and found nothing. It is annoying in a report, but it is honest, and anything that reads the cell (a SUM, a chart) shows the error too, so the problem can’t slip through.

Where the default bites

The dangerous row in the table is the third one. VLOOKUP and MATCH default to an approximate match. On a catalog sorted by id, an approximate lookup for 106 settles for the largest id below it, 105, and returns that product’s name. In the furniture catalog from level 5, that is the Avery Leather Bed Frame. A sales associate who trusts the sheet tells a customer about the wrong product.

XLOOKUP is exact unless you ask for something else, which is the main reason to prefer it for ids, codes and names.

Adding a fallback to the older functions

VLOOKUP and INDEX/MATCH have no if_not_found argument. Wrap them instead:

=IFNA(VLOOKUP(106,$G$2:$H$17,2,FALSE),"discontinued")

IFNA replaces only #N/A. IFERROR replaces every error, including a #REF! from a broken column number or a #VALUE! from bad data, which you usually want to see. Prefer IFNA around lookups. Both work the same in Excel and Google Sheets.

Common mistakes

• Trusting a cell because it shows no error. An approximate lookup on a missing key returns a real-looking value. Write FALSE in VLOOKUP and 0 in MATCH every time.
• A fallback before a count. Count how many keys are missing, then ask why. In level 5, three lines miss the catalog, all of them product 106, which was discontinued: a fallback is the right call. If a third of the lines were missing, the catalog would be the wrong export, or the ids would be text in one table and numbers in the other. A fallback there hides the bug and makes the report look complete.
• IFERROR where IFNA was meant. It also swallows the errors that point at a broken formula.
• A fallback of 0 for a price. A missing price shown as 0 gets added into totals. Text like “discontinued” can’t be added up by accident.