IFERROR, with VLOOKUP examples
Catches a formula that would return an error and returns something else instead.
Most common use, replacing #N/A from a lookup: =IFERROR(VLOOKUP(A2, Prices!A:B, 2, FALSE), "Not found")
IFERROR has exactly one job: test a formula, and if it would return an error (#N/A, #VALUE!, #REF!, any of them), return something else instead. If the formula doesn't error, IFERROR just passes the real result through unchanged, with no side effect at all.
In practice it's almost always wrapped around a lookup (VLOOKUP, XLOOKUP, or MATCH) specifically to turn a raw #N/A from a missing match into something a reader can actually understand, like "Not found" instead of a cryptic error code sitting in the middle of a report.
Syntax
| Argument | Description |
|---|---|
| value | Required. The formula or value to test, often a lookup that might come back empty. |
| value_if_error | Required. What to return instead, if value evaluates to an error. |
IFERROR with VLOOKUP, step by step
With an exact match, VLOOKUP returns #N/A whenever the value you are looking for is not in the first column of the table. In a report that is ugly, and worse, one #N/A breaks every SUM or AVERAGE that touches it. Wrapping the lookup in IFERROR fixes both.
1. Write the lookup on its own first and check it works for a value you know exists:
2. Wrap it. Put IFERROR( in front, and add the fallback before the final bracket:
3. Choose the fallback on purpose. Each option behaves differently further down the sheet:
| Fallback | Looks like | Watch out for |
|---|---|---|
| "Not found" | A clear label | It is text: SUM skips it, but a formula like =C2*1.2 pointing at it returns #VALUE!. |
| "" | An empty cell | Also text, not a true blank: COUNTA counts it and arithmetic on it returns #VALUE!. |
| 0 | A real zero | Totals still work, but a missing item now looks exactly like an item that costs nothing. |
For a report people read, use a label. For a column that feeds more calculations, use 0 only if a missing item really should count as zero; otherwise keep the label and fix the missing items.
| A | B | |
|---|---|---|
| 1 | SKU | Price |
| 2 | SKU-1 | 10 |
| 3 | SKU-7 | Not found |
Here the Prices sheet lists SKU-1 but not SKU-7, so row 2 returns the price and row 3 returns the label instead of #N/A.
IFNA vs IFERROR: which one for lookups?
IFERROR catches every error type. IFNA (Excel 2013 and later) catches only #N/A, which is the one error a lookup returns when a value is genuinely missing.
That makes IFNA the safer choice around a lookup: a missing SKU still shows "Not found", but if someone deletes a column and the formula starts returning #REF!, you see the #REF! instead of a sheet full of misleading "Not found" labels. Keep IFERROR for formulas where several error types are expected, such as a division that can hit an empty cell.
Look in a second table if the first has no match
Because the fallback can be any formula, you can chain lookups: try the current price list, then the archive, then give up.
Excel only evaluates the second lookup when the first one fails. Two levels is easy to read; past three, a single lookup on a combined table is easier to maintain.
IFERROR not working? Five common causes
- The formula shows as text. The cell was formatted as Text before you typed, or the leading = is missing. Set the format to General, then press F2 and Enter to re-enter it.
- There is no error to catch. If VLOOKUP’s last argument is TRUE or left out, it does an approximate match and returns a wrong value instead of
#N/A. Use FALSE. - Every row says "Not found". IFERROR is hiding the real problem: the values do not actually match. The usual culprits are numbers stored as text and trailing spaces. Remove IFERROR for a moment, then test with
=A2=Prices!A5; fix with TRIM or VALUE. - The error appears in the next cell instead. IFERROR only covers the formula inside it. A text fallback such as "Not found" makes any formula that multiplies or adds that cell return
#VALUE!. - A green triangle is still there. That marker is Excel’s error checking (for example "number stored as text"), not an error value. IFERROR does not remove it.
IFERROR with other formulas
The same wrapper works around any formula. Three you will meet often:
For the division, =IF(C2=0, 0, B2/C2) is often better: it handles the one case you expect (an empty or zero divisor) and still shows any other error. XLOOKUP has its own if_not_found argument, so it does not need IFERROR at all.
The mistake almost everyone makes
Wrapping IFERROR around a formula that has a genuine bug, rather than one that might legitimately have no match. IFERROR doesn't distinguish between “this row just doesn't exist in the lookup table, which is fine” and “this formula is broken and I don't know why”: it catches both identically and silences both identically. That's exactly backwards from what you want in a model someone else will rely on: a real bug should be loud, not swallowed into a tidy fallback value that makes the sheet look fine while being wrong.
A second, subtler version of the same mistake: using a fallback value that looks like real data, like 0, instead of a blank or a clear label. A hidden 0 from a failed lookup doesn't look like an error in a downstream SUM; it just quietly understates the total, with nothing on screen to suggest why.
A real example: flagging unmatched items in a bank reconciliation
Say you're reconciling a bank statement against your internal ledger by transaction ID, and some ledger entries genuinely won't have a matching bank line yet (checks still in transit, for instance):
This is the right use of IFERROR: an unmatched line is an expected, normal outcome of a reconciliation, not a bug, so labeling it clearly (rather than letting a raw #N/A sit there, or, worse, letting it silently become a 0 that understates your reconciling items) is exactly what the function is for. If this same formula were instead hiding a typo in a range reference, that's the case IFERROR should not be used to paper over.
Worked examples
| A | B | |
|---|---|---|
| 1 | SKU | Price |
| 2 | SKU-1 | 10 |
| A | B | |
|---|---|---|
| 1 | SKU | Price |
| 2 | SKU-1 | 10 |
| A | B | |
|---|---|---|
| 1 | SKU | Price |
| 2 | SKU-1 | 10 |
| A | B | |
|---|---|---|
| 1 | SKU | Price |
| 2 | SKU-1 | 10 |
Common mistakes
- Wrapping IFERROR around a formula that has a genuine bug. It hides the error instead of surfacing it, which can mask a real problem.
- Using a fallback value that looks like real data (like 0) when a blank or a clear label would be more honest: a hidden 0 understates totals downstream.
- Reaching for IFERROR when XLOOKUP's own built-in if_not_found argument would do the same job in fewer characters.
Related functions
Try it yourself
A free, graded practice exercise using this exact function: instant feedback, no signup required.
Practice IFERROR →