Logical checks and errors
IFNA vs IFERROR: handle missing lookups without hiding broken data
A missing lookup key produces #N/A, whereas a zero pack size or broken reference produces a different error. Use IFNA so genuinely missing codes show a friendly label while real arithmetic mistakes stay visible.
Two very different failures in the same price lookup
Suppose a hardware desk looks up a part code in A2:C4 and divides the carton cost (column B) by units per carton (column C) to quote a unit price. Notice row 3: part P-204 is in the catalog, but someone entered 0 units per carton by mistake.
| Part code (A) | Carton cost (B) | Units per carton (C) |
|---|---|---|
| P-101 | 48 | 6 |
| P-204 | 45 | 0 |
| P-309 | 36 | 4 |
Use IFNA when "not found" is the only error you expect
Put the requested part code in E2. Wrapping the unit-price calculation in IFNA replaces #N/A with "Unlisted code" when E2 is absent from column A, while letting every other error pass through untouched.
=IFNA(VLOOKUP(E2,A2:C4,2,FALSE)/VLOOKUP(E2,A2:C4,3,FALSE),"Unlisted code")Compare how IFNA and IFERROR react to row 3
When E2 is P-101, both formulas return 48 / 6 = 8. When E2 is P-999 (a code not in the table), VLOOKUP returns #N/A and both IFNA and IFERROR return "Unlisted code".
Now set E2 to P-204. Both VLOOKUP calls succeed—45 and 0—and the division 45 / 0 produces #DIV/0!. IFNA does not catch #DIV/0!, so the cell immediately alerts you to fix column C. IFERROR catches every error type indiscriminately and displays "Unlisted code", falsely telling the salesperson that P-204 does not exist.
=IFERROR(VLOOKUP(E2,A2:C4,2,FALSE)/VLOOKUP(E2,A2:C4,3,FALSE),"Unlisted code")When IFERROR is still appropriate
Reserve IFERROR for deliberate arithmetic fallbacks where you have already verified why an error occurs—for example, displaying "N/A" on a ratio card when a denominator is legitimately zero and already audited elsewhere. Even there, an explicit IF(C2=0, ..., B2/C2) states the business rule more clearly than catching every error.
Repair the catalog row and verify
Test E2 as P-999, then change E2 to P-204 to observe #DIV/0! under IFNA. Finally, correct C3 from 0 to 5 units per carton: the IFNA formula immediately resolves to 45 / 5 = 9.
Try the example
Use the original values or change them to test your understanding.