IFERROR + VLOOKUP
Safe lookups with a friendly fallback
When to reach for this
Show "Not found" when a customer ID doesn't exist in the reference table.
=IFERROR(VLOOKUP(A2, Products!A:C, 3, FALSE), "Not found")What this pattern does
Wraps a VLOOKUP with IFERROR so missing values don't crash your spreadsheet with #N/A. Instead, you choose what users see: a blank cell, a "Not found" message, or a default value.
Interactive walkthroughWalk through IFERROR
IFERROR wrapped around VLOOKUP is the safest lookup pattern in Excel. Missing rows become readable messages instead of error codes. Watch it handle a missing product, step by step.
VLOOKUP("P999", A2:C6, 3, FALSE)valueThe risky expression, a VLOOKUP that may fail
"Not found"value_if_errorThe friendly fallback shown when VLOOKUP errors out
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Product ID | Name | Price | ||
| 2 | P001 | Laptop | $1,200 | =IFERROR(VLOOKUP("P999", A2:C6, 3, FALSE), "Not found") | |
| 3 | P002 | Phone | $800 | ||
| 4 | P003 | Tablet | $450 | ||
| 5 | P004 | Monitor | $320 | ||
| 6 | P005 | Keyboard | $80 |
You type the combo formula
You want to look up product "P999" but you're not sure if it exists. Bare VLOOKUP would show an ugly #N/A. Wrapping with IFERROR gives a clean fallback.
Key takeaway
Best practice: use "" (empty string) for silent fallback, or a descriptive message like "Not found" for reports. Avoid using 0 as a fallback, it can silently poison downstream sums.