Excel track · Exercise 66 of 86 Paid
IFERROR: catch the error, show your text
Replace a failed lookup with words you chose, and know which faults those words will hide.
Intermediate
Two prices and a missing SKU
Read-only data · answer in B5
In B5, look up the price of the SKU in A4, and show "no price" if the lookup fails.
| A | B | |
|---|---|---|
| 1 | SKU | Price |
| 2 | TX-101 | 19.99 |
| 3 | TX-102 | 14 |
| 4 | TX-109 | |
| 5 | Price |
Reference answer
What it does
IFERROR takes two arguments: the calculation that might fail, and what to show when it does. Anything that goes wrong inside the first argument is replaced by the second. Here the lookup on TX-109 finds nothing in A2:B3 and would return #N/A, so the cell reads no price instead.
IFERROR catches every error the same way, which is wider than a lookup miss. A reference that no longer points anywhere, or arithmetic on text, gets the same two words as a code that is simply absent. A broken formula then reads like missing data.
Put the guard around the part that can fail. The SKU is read from A4, so a different code changes the answer without touching the formula. Arithmetic left outside the IFERROR still returns an error instead of text.
Syntax
=IFERROR(value, value_if_error)Worked example
On Two prices and a missing SKU, =IFERROR(VLOOKUP(A4,A2:B3,2,FALSE),"no price") returns no price.
=IFERROR(VLOOKUP(A4,A2:B3,2,FALSE),"no price")Common errors
| Error | What it means and how to fix it |
|---|---|
#N/A | The lookup finds nothing and no fallback is supplied, so the cell shows the error instead of words. |
#VALUE! | A number is added to the fallback text outside the wrapper, so the whole cell turns into #VALUE!. |
Masked fault | The same fallback answers a broken reference too, so a formula fault prints as a missing price and nobody looks. |
At work
A price list sent to a customer, where a discontinued code has to read as missing instead of #N/A.