Excel track · Exercise 69 of 86 Paid
Error handling: which net catches what
Choose the wide net or the narrow one on purpose, and wrap the whole calculation in it.
Advanced
Code, price and units table with one blank units cell
Read-only data · screen 1 of 2 · answer in B9
In B9, price the code in A8 and say no match when any part of the lookup fails.
| A | B | |
|---|---|---|
| 1 | Code | Price |
| 2 | A-1 | 10 |
| 3 | B-2 | 20 |
| 4 | C-3 | 30 |
| 5 | A-1 | 12 |
| 6 | B-2 | 22 |
| 7 | C-3 | 32 |
| 8 | Q-9 | |
| 9 | Any failure |
In D8, divide the price on row 4 by its units and say no units when the division fails.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Code | Price | Units | Answer |
| 2 | A-1 | 10 | 5 | |
| 3 | B-2 | 20 | 4 | |
| 4 | C-3 | 30 | ||
| 5 | A-1 | 12 | 3 | |
| 6 | B-2 | 22 | 2 | |
| 7 | C-3 | 32 | 6 | |
| 8 | Note |
Reference answer
What it does
IFERROR returns its second argument whenever the first one fails, whatever the reason. IFNA returns the second argument only for #N/A. The wide net catches a missing code and a blank divisor; the narrow one catches the missing code and lets the rest show.
The first screen prices Q-9, which is not in A2:B7, so the lookup misses and the cell reads no match. The second divides B4 by an empty C4: 30 divided by a blank cell raises #DIV/0!, and IFNA would have left it standing.
Choose the net by what the sheet should hide. A wide net keeps a report tidy and turns a broken reference into words that look like missing data. Put either net around the whole calculation, so whatever is added inside it later is covered too.
Syntax
=IFERROR(value, fallback) catches everything; =IFNA(value, fallback) catches only #N/AWorked example
On Code, price and units table with one blank units cell, =IFERROR(VLOOKUP(A8,A2:B7,2,FALSE),"no match") returns no match.
=IFERROR(VLOOKUP(A8,A2:B7,2,FALSE),"no match")Common errors
| Error | What it means and how to fix it |
|---|---|
#DIV/0! | C4 is blank, so the division has no divisor; only the wide net answers it with words. |
#N/A | The lookup found no Q-9, the error IFNA is built to replace. |
Hidden fault | The wide net reports a broken reference as no match, so a real formula fault passes for absent data. |
At work
A month-end report where missing codes and blank divisors both need a word, and a broken reference still needs finding.