A typo in a product code or customer ID does not just break one lookup — it breaks every downstream formula that depends on that lookup succeeding, often several sheets away from where the typo actually happened. Validating the code right where it is typed, before any XLOOKUP or VLOOKUP runs against it, turns a confusing #N/A buried in a summary tab into a clear message at the point of entry.
A code that exists in the list returns OK; a mistyped one is flagged immediately, before any price or inventory lookup runs against it.
The example
Four codes typed into an order form; one (SKU-4041) does not exist in the product list because of a transposed digit.
| A | B | |
|---|---|---|
| 1 | Typed code | Validation |
| 2 | SKU-1001 | OK |
| 3 | SKU-2044 | OK |
| 4 | SKU-4041 | Not found — check the code |
| 5 | SKU-3390 | OK |
The formula
MATCH either returns a position (success) or the #N/A error (failure); ISNA turns that into a checkable TRUE/FALSE:
How it works
Working from the inside out:
MATCH(A2,$D$2:$D$8,0)searches for this row's typed code in the reference list. The 0 forces an exact match. If found, it returns the code's position in the list; if not found, MATCH itself returns the #N/A error.ISNA(...)wraps that result and returns TRUE if MATCH errored (code not found) or FALSE if MATCH succeeded (code found) — ISNA is built specifically to test for the #N/A error and nothing else.IF(ISNA(...),"Not found...","OK")turns that TRUE/FALSE into a human-readable message, catching the problem right in the row where the code was typed.
Chain every downstream lookup (price, description, stock level) behind this validation column with an IF, so they only attempt to run once the code has already passed the ISNA guard.
Try it: interactive demo
Type a code and see whether it exists in the sample reference list (SKU-1001, SKU-2044, SKU-3390).
Variations
Use IFERROR for a shorter version when you only care that it failed
IFERROR catches ANY error (not just #N/A), which is fine here since MATCH with these arguments cannot realistically raise a different error type, and it is a shorter formula.
Highlight invalid codes directly with conditional formatting
Use the same ISNA(MATCH(...)) test as a conditional formatting rule on the typed-code cell itself, so an invalid entry turns red the moment it is typed, with no separate validation column needed.
Pitfalls & errors
ISNA only catches the #N/A error specifically. If the underlying MATCH could fail for a different reason (a #REF! from a deleted reference range, for instance), ISNA will not catch it and the error will still show. IFERROR catches every error type if that broader net is what you actually want.
Do the validation on a raw typed value BEFORE any TRIM or UPPER cleanup is applied, in a separate check, if you also want to know whether the user's exact input matched — cleaning the input first will make a code with stray spaces silently pass validation, which may or may not be what you want.
The third argument to MATCH must be 0 for an exact match. Leaving it out defaults to an approximate match (1), which requires the reference list to be sorted ascending and can return a false OK for a code that does not actually exist but happens to sort near one that does.
Practice workbook
Frequently asked questions
Is this different from data validation (the dropdown list feature)?
Can I use this with XLOOKUP's built-in if_not_found argument instead?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class