IFERROR and Friends: When to Use Each — and When Not to Use Any of Them

TL;DR: IFERROR is the most dangerous function in your Excel toolbox. It catches every error — including #REF! from a deleted sheet, #VALUE! from a corrupted VLOOKUP, and #DIV/0! from a denominator that went to zero. Used carelessly, it turns real data problems into blank cells that the partner signs off on. This post shows you which error-trapping function to use for each scenario in a tax workpaper, when to surface errors instead of hiding them, and how to convert every VLOOKUP in a trial balance from IFERROR to IFNA in under two minutes.

The Problem

You inherit the Henderson M-3 schedule. Someone wrapped every formula in IFERROR. The workpaper shows zeros where the M-1 adjustments should be — zeros that the reviewer signed off on because “the numbers foot.” But the zeros aren’t zeros. They’re hidden #REF! errors from a sheet the prior preparer deleted before leaving the firm. The real adjustments — $47,300 in state tax accrual timing differences — are gone. Not missing. Not flagged. Gone. The return has been filed.

IFERROR didn’t catch the error. It hid it. And because the workbooks you inherit have had three preparers, four review cycles, and five “temporary” fixes applied between them, you have no way of knowing which IFERRORs are protecting you from noisy #N/As and which are burying fatal #REF!s. This post gives you the rules for knowing the difference — and fixing the ones that are wrong.

#How This Post Works

There are four error-handling functions in Excel: IFERROR, IFNA, ISERROR, and ISNA. They look similar. They are not interchangeable. Each has a specific job, and using the wrong one in a tax workpaper produces one of two outcomes:

A noisy workpaper full of #N/A errors that the partner won’t sign
A clean workpaper full of hidden #REF! errors that the partner shouldn’t have signed

This post gives you a rule for each function, a tax workpaper example of when to use it, and — most importantly — a tax workpaper example of when NOT to use it. By the end, you’ll know exactly which IFERRORs in your current workpapers are safe and which need to be replaced with IFNA or ISERROR.

#1. IFERROR — The Double-Edged Sword

#The Syntax

=IFERROR(formula, value_if_error)

Plain English: “Try this formula. If it produces ANY error — #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, #NULL! — return this fallback instead.”

IFERROR catches everything. It is the universal error suppressor. That universality is its only strength and its only weakness.

#When to Use IFERROR: Division Where a Zero Denominator Is Expected

Scenario: You’re computing effective tax rates for 12 entities in a consolidated group. The formula is =TaxExpense/PreTaxIncome. Three of the entities have negative taxable income — the formula returns a meaningless negative rate. One entity has exactly zero pre-tax income — =D16/C16 returns #DIV/0!.

=IFERROR(D16/C16, "N/M")

This is the correct use of IFERROR. The error you’re catching (#DIV/0!) is the ONLY error this formula can produce. There’s no VLOOKUP embedded in it. No sheet references that could become #REF!. No text values that could produce #VALUE!. The formula is D16/C16 — pure division. The only thing that can go wrong is division by zero. IFERROR is safe here because the scope of possible errors is exactly one.

Rule: IFERROR is safe when the formula it wraps CANNOT produce any error other than the one you expect. =A1/B1 can only produce #DIV/0!. A simple arithmetic expression with no lookups, no text conversion, and no sheet references is safe to wrap in IFERROR.

#When NOT to Use IFERROR: Workpaper Formulas with External References

Scenario: Your state apportionment workpaper pulls data from three sheets — the federal trial balance, the state modification schedule, and the prior-year apportionment worksheet. A preparer wraps the entire thing in IFERROR because “the N/As were annoying.”

=IFERROR(
  VLOOKUP(A2, 'Federal TB'!A:H, 5, FALSE) *
  VLOOKUP(A2, 'State Mods'!A:K, 8, FALSE) /
  VLOOKUP(A2, 'PY Apportionment'!A:D, 4, FALSE),
  0
)

What can go wrong:

Someone deletes the 'Federal TB' sheet → every cell shows 0 instead of #REF!. The state return is filed with zero apportionment factors.
Someone renames 'State Mods' to 'State Adjustments' → every cell shows 0 instead of #REF!. The modifications don’t flow through.
The PY apportionment formula returns #N/A because this is a new entity → correctly returns 0 (that’s what you wanted). But IFERROR also swallows the other two errors.

Rule: IFERROR is dangerous when it wraps a formula that references other sheets, uses VLOOKUP/XLOOKUP, or could produce errors from sources you haven’t anticipated. In these cases, use IFNA to catch only the #N/As while letting #REF! and #VALUE! errors surface.

#The IFERROR Conversion Rule

Every IFERROR in your workpaper falls into one of three categories. Check each one:

What the IFERROR wraps	Safe?	Fix
A simple arithmetic formula (e.g., `A1/B1`, `A1*B1`)	Yes	Leave it
A VLOOKUP/XLOOKUP where you only want to suppress #N/A	No	Replace with IFNA
A complex formula with multiple operations and sheet references	No	Break it apart — wrap each lookup individually
A formula where you genuinely want to suppress ALL errors	Maybe	Use only if you understand every possible error

The third case is the most common in tax workpapers. You find something like:

=IFERROR(VLOOKUP(A2, 'PY TB'!A:H, 5, FALSE) + VLOOKUP(A2, 'PY TB'!A:H, 6, FALSE), 0)

This formula sums two VLOOKUPs. IFERROR wraps the sum. If the first VLOOKUP returns #N/A and the second returns $47,300, the sum should be $47,300. But IFERROR on the sum returns 0 — because #N/A + any number = #N/A, which triggers the IFERROR wrapper. The correct fix:

=IFNA(VLOOKUP(A2, 'PY TB'!A:H, 5, FALSE), 0) + IFNA(VLOOKUP(A2, 'PY TB'!A:H, 6, FALSE), 0)

Each VLOOKUP gets its own IFNA wrapper. If one lookup fails, the other still contributes to the sum. And if the sheet is deleted, #REF! surfaces on both lookups — which is what you want.

#2. IFNA — The Safe Replacement for Lookup Errors

#The Syntax

=IFNA(formula, value_if_na)

Plain English: “Try this formula. If it produces #N/A (and ONLY #N/A), return this fallback instead. If it produces any other error, let that error through.”

IFNA catches exactly one error: the “not found” error from VLOOKUP, HLOOKUP, XLOOKUP, and MATCH. It is the surgical version of IFERROR. It suppresses what you expect (#N/A from missing lookup values) and surfaces what you don’t (#REF! from deleted sheets, #VALUE! from type mismatches).

#Tax Workpaper Example 1: Trial Balance Account Mapping

Your trial balance has 487 account codes. You need to map each one to a summary line item. 23 accounts are new this year and don’t appear in the mapping table. You want those to show "Not mapped" — but you do NOT want to hide #REF! if someone deletes the mapping sheet.

Wrong (IFERROR hides everything):

=IFERROR(VLOOKUP(A2, Mapping!A:C, 3, FALSE), "Not mapped")

Right (IFNA catches only #N/A):

=IFNA(VLOOKUP(A2, Mapping!A:C, 3, FALSE), "Not mapped")

Now if Mapping!A:C doesn’t exist, you get #REF! — visible, unmissable, correct. If the account code 10010 isn’t in the mapping table, you get "Not mapped" — visible, intentional, also correct.

#Tax Workpaper Example 2: Prior-Year Balance Lookup

You’re building a CY vs. PY comparison column. Some accounts are new this year and don’t exist in the PY trial balance. You want those to show 0 for the PY balance — not #N/A.

=IFNA(VLOOKUP(A2, 'PY TB'!A:D, 4, FALSE), 0)

New accounts show $0 in the PY column. But if the 'PY TB' sheet is renamed or deleted, you get #REF! — and the foot-cross check on your summary page catches it before the return goes out.

#Tax Workpaper Example 3: XLOOKUP with a Fallback

XLOOKUP has a built-in if_not_found argument that handles #N/A without any wrapper at all:

=XLOOKUP(A2, Mapping!A:A, Mapping!C:C, "Not mapped")

This is even better than IFNA + VLOOKUP because there’s no wrapper to forget. The "Not mapped" argument only fires when the value isn’t found — other errors like #REF! still surface. But if your lookup formula involves multiple operations (like the SUM-of-two-lookups example above), use IFNA on each XLOOKUP individually.

#The Conversion Checklist: IFERROR → IFNA

Walk through your workpaper and find every formula that contains IFERROR. For each one:

Does the formula contain VLOOKUP, HLOOKUP, XLOOKUP, or MATCH? If yes, the IFERROR is almost certainly there to suppress #N/A. Replace IFERROR with IFNA.
Is the formula a simple division (A1/B1) or multiplication? If yes, leave IFERROR in place. It’s catching #DIV/0! or #VALUE!, not #N/A.
Does the formula combine multiple lookups or arithmetic operations? If yes, remove the outer IFERROR and wrap each lookup or division individually.
Do you genuinely want to suppress ALL errors regardless of source? If yes — and you’ve understood every possible error this formula can produce — leave IFERROR. This is rare in tax workpapers. The only common scenario is pure division where a zero denominator is expected.

Bulk conversion trick: If your trial balance has an entire column of =IFERROR(VLOOKUP(...), 0), you can convert all of them at once with Find and Replace (Ctrl+H). Find: IFERROR(VLOOKUP(. Replace with: IFNA(VLOOKUP(. This works because the opening structure is identical — only the IFERROR vs. IFNA part changes. Test on one cell first, then Replace All.

#3. ISERROR and ISNA — Flag Errors Without Hiding Them

#The Syntax

=ISERROR(value)
=ISNA(value)

Plain English: ISERROR returns TRUE if the value is ANY error, FALSE otherwise. ISNA returns TRUE only if the value is #N/A.

Unlike IFERROR and IFNA, these functions don’t suppress the error — they report on it. The original formula stays in its own cell, producing whatever result (or error) it produces. The ISERROR/ISNA formula sits in a separate cell and flags the presence of an error.

#Tax Workpaper Example 1: Audit-Friendly Error Flags

You’re building a workpaper for a partner who wants to SEE every error, not have them hidden. But you also want the errors to be findable — a column full of #N/A values blends in, but a column that says "CHECK" on the rows with problems doesn’t.

Put your lookup formula in column C as usual. In column D, add:

=IF(ISERROR(C2), "CHECK", "OK")

Now the partner can filter column D for “CHECK” and instantly see only the problem rows. The original formulas are untouched — the #N/A and #REF! values are still visible in column C. Column D is a navigation aid, not a suppression tool.

Advanced version — distinguish between error types:

=IF(ISNA(C2), "Not found", IF(ISERROR(C2), "CHECK — see formula", "OK"))

This shows "Not found" for missing lookup values (expected, need mapping) and "CHECK — see formula" for real errors like #REF! or #VALUE! (unexpected, needs investigation). The partner can triage at a glance.

#Tax Workpaper Example 2: Conditional Formatting Based on Error Status

Instead of an error flag column, use conditional formatting to highlight error cells visually. Select your formula column → Conditional Formatting → New Rule → “Use a formula to determine which cells to format”:

=ISERROR(C2)

Set the format to red fill with white text. Now every error cell in column C is bright red. The partner can’t miss them — but the original values are still there, and you haven’t wrapped anything in IFERROR. This combines the visibility of error flagging with the audit trail of keeping formulas intact.

Version that distinguishes #N/A from other errors:

=AND(ISERROR(C2), NOT(ISNA(C2)))

This highlights only the “real” errors (#REF!, #VALUE!, #DIV/0!) and leaves #N/A alone. #N/A from missing lookups is expected — don’t light it up. #REF! from a deleted sheet is an emergency — make it red.

#Tax Workpaper Example 3: Count Errors for a Summary Dashboard

The partner wants to know at a glance how many “real” errors vs. “expected” errors are in the workpaper.

=COUNTIF(D:D, TRUE)           ' Total error cells (from ISERROR helper column)
=COUNTIF(D:D, TRUE) - COUNTIF(C:C, "#N/A")  ' Real errors only (exclude #N/A)

Put these on a summary sheet. Before every review cycle, check the counts. If “real errors” is nonzero, something broke — investigate before the partner sees it.

#4. The Error Trap Pattern — Surface Errors Where They Matter

The core insight behind this post: you want errors to be visible, not silent. The IFERROR habit that tax preparers develop — “wrap everything in IFERROR so the partner doesn’t see red” — is exactly backwards. The partner NEEDS to see errors. What the partner doesn’t need is to see error types that don’t indicate a problem.

Here’s a design pattern that separates harmless #N/As from dangerous everything-else, in a single formula:

#Pattern: Two-Level Error Handling

=IF(ISNA(formula),
    "Not found",          ' #N/A → expected, show a friendly message
    IF(ISERROR(formula),
        "ERROR — CHECK",  ' any other error → flag aggressively
        formula           ' no error → show the result
    )
)

This formula:

Returns "Not found" when the value doesn’t exist in the lookup table (clean, expected, not alarming)
Returns "ERROR — CHECK" when the formula produces #REF!, #VALUE!, #DIV/0!, or any other error (visible, alarming, exactly what the reviewer needs to see)
Returns the actual result when everything works

Applied to a VLOOKUP:

=IF(ISNA(VLOOKUP(A2, Mapping!A:C, 3, FALSE)),
    "Not mapped",
    IF(ISERROR(VLOOKUP(A2, Mapping!A:C, 3, FALSE)),
        "ERROR — CHECK",
        VLOOKUP(A2, Mapping!A:C, 3, FALSE)
    )
)

Note: this evaluates the VLOOKUP twice (once for ISNA, once for ISERROR). For large workbooks this can slow things down. If performance matters, use a helper column that computes the VLOOKUP once, then reference the helper in the error-trap formula.

#When to Use This Pattern vs. Simpler Wrappers

Your situation	Use
You’re the only preparer and you trust the workbook structure	IFNA(VLOOKUP(…), 0) — simple, clean
Multiple preparers touch the workbook, sheets get added/deleted	Two-level error trap — flags structural problems
Partner reviews the workpaper and hates #N/A	IFNA for #N/A, conditional formatting for #REF!
Workpaper goes to the client (external recipient)	Paste values before sending — strip all formulas
Performance-critical (20,000+ lookup rows)	Helper column + IF referencing helper

#5. The Three Most Common IFERROR Mistakes in Tax Workpapers

#Mistake 1: IFERROR on a Formula That Contains VLOOKUP

=IFERROR(VLOOKUP(A2, 'PY TB'!A:D, 4, FALSE), 0)

This worked when you built it. Then the junior staff accountant renamed the PY TB sheet from 'PY TB' to '2024 TB'. Every cell in the column now says 0. The workpaper shows no errors. The numbers foot perfectly — with zeros where the real PY balances should be.

The fix: =IFNA(VLOOKUP(A2, 'PY TB'!A:D, 4, FALSE), 0). Now the renamed sheet produces #REF! — visible, correct, unmissable.

#Mistake 2: IFERROR on a SUM of Multiple Lookups

=IFERROR(VLOOKUP(A2, Data!A:D, 3, FALSE) + VLOOKUP(A2, Data!A:D, 4, FALSE), 0)

If EITHER lookup fails, the entire formula returns 0 — even if the other lookup has a valid value. Account 40010 should have $325,000 from column 3 and $0 from column 4 (column not mapped yet). Instead it shows $0 total — because the #N/A from column 4 poisoned the entire formula.

The fix:

=IFNA(VLOOKUP(A2, Data!A:D, 3, FALSE), 0) + IFNA(VLOOKUP(A2, Data!A:D, 4, FALSE), 0)

Each lookup gets its own wrapper. A failure in one doesn’t kill the other.

#Mistake 3: IFERROR on a Formula That References Multiple Sheets

=IFERROR('Federal'!B12 * 'State Mods'!C14 / 'Appt'!D5, 0)

This formula pulls from three sheets. If any one of them is deleted, renamed, or moved, every dependent cell shows 0. There is no way to know which sheet is missing or whether the resulting zero is real or an error mask.

The fix: Don’t IFERROR-wrap formulas with multiple sheet references. Instead, use a helper cell for each sheet reference and error-trap each individually. Or — better — don’t trap errors on cross-sheet references at all. If a reference breaks, you want to know.

#6. Decision Tree — Which Function for Which Error

Is the formula a simple arithmetic expression (A1/B1, A1*B1, A1-C1)?
├── YES → IFERROR is fine. The only possible error is #DIV/0! or #VALUE!,
│         and both are expected edge cases.
└── NO → Continue

Does the formula contain VLOOKUP, HLOOKUP, XLOOKUP, or MATCH?
├── YES → Use IFNA on EACH lookup individually. Never wrap the whole
│         formula.
└── NO → Continue

Does the formula reference other sheets?
├── YES → Do NOT IFERROR-wrap it. Let errors surface. Use ISERROR in
│         a helper column if you need to flag them.
└── NO → Continue

Are you suppressing #N/A from a lookup (not-found) or hiding an actual error?
├── Suppressing #N/A → Use IFNA, or use XLOOKUP's built-in if_not_found
├── Hiding any other error → Reconsider. Do you really want to hide this?
│         If yes, use IFERROR but document WHY in a cell comment.

#Quick Decision Table

Scenario	Use	Why
VLOOKUP not-found (#N/A)	IFNA	Catches only #N/A, lets #REF! through
XLOOKUP not-found	Built-in 4th argument	Cleaner than IFNA — no wrapper needed
Division by zero (#DIV/0!)	IFERROR	Division can’t produce #REF!
Flagging errors without hiding them	ISERROR / ISNA	Keep formulas intact, show status separately
Complex formula, multiple sheets	Neither — let errors show	You need to know when sheets are deleted
Sending workpaper to client	Paste values first	External recipients shouldn’t see formulas at all
Partner review version	IFNA on lookups, ISERROR flags on critical cells	Clean output + audit trail

#Adapt It

Convert an entire trial balance column from IFERROR to IFNA. Find and Replace is the fastest method:

Select the column containing =IFERROR(VLOOKUP(...), 0)
Ctrl+H → Find: IFERROR(VLOOKUP( → Replace with: IFNA(VLOOKUP(
Replace All

This works because the opening of the formula is identical — only the function name changes. The closing , 0) stays the same. But TEST ONE CELL FIRST. If your IFERROR wraps a formula that isn’t just VLOOKUP(...), this replacement could break it.

Instead of IFNA, switch to XLOOKUP and use its built-in if_not_found.

Old: =IFNA(VLOOKUP(A2, Mapping!A:C, 3, FALSE), "Not mapped")
New: =XLOOKUP(A2, Mapping!A:A, Mapping!C:C, "Not mapped")

XLOOKUP’s fourth argument handles the not-found case without any wrapper. If the mapping sheet is deleted, you get #REF! — exactly what IFNA was trying to preserve. If you’re on Excel 2021 or Microsoft 365, switch from IFNA+VLOOKUP to XLOOKUP. It’s cleaner, safer, and requires less typing.

Build a two-level error flag column next to your VLOOKUP column.

Column C: =VLOOKUP(A2, Mapping!A:C, 3, FALSE) (raw formula — errors show) Column D: =IF(ISNA(C2), "Not mapped", IF(ISERROR(C2), "CHECK", "OK"))

Now filter column D for “CHECK” to find structural problems, or “Not mapped” to find accounts that need mapping. The partner can verify at a glance.

Add a summary count to your dashboard sheet.

In a cell on your summary sheet:

="CY Account Mapping: "&COUNTIF('TB'!D:D, "Not mapped")&" unmapped, "&COUNTIF('TB'!D:D, "CHECK")&" errors"

This produces a string like "CY Account Mapping: 12 unmapped, 0 errors". The partner reads one line and knows the workpaper status.

Use conditional formatting instead of IFERROR for visual error flagging.

If you want errors to be visible without an extra column, apply conditional formatting to the formula column:

Select the formula column
Conditional Formatting → New Rule → “Use a formula”
Formula: =AND(ISERROR(C2), NOT(ISNA(C2)))
Format: red fill, white text, bold

This highlights #REF!, #VALUE!, and #DIV/0! in red — but leaves #N/A alone (they’re expected). No IFERROR wrapper. No hidden problems. The formulas stay pure and the errors are visible.

Create a “no-IFERROR” rule for your team’s templates.

The simplest way to prevent IFERROR abuse is to never put IFERROR in a template. Instead:

Every VLOOKUP in the template uses IFNA or XLOOKUP with a fallback
Every division formula gets its own IFERROR (if needed)
Any formula touching multiple sheets gets no error wrapper at all
An error-check column next to critical formula columns uses ISERROR to flag

Once the template is clean, anyone who copies it inherits the right patterns. This is more effective than trying to train people not to use IFERROR — removing the temptation from the starting template is easier than policing every workpaper.

See also: 5 Functions Every Tax Preparer Should Know — includes IFERROR in context with SUMIFS, XLOOKUP, EOMONTH, and TEXT. For the formula-only approach to error detection without macros, see Self-Checking Workpaper Design.

The Problem

#How This Post Works

#1. IFERROR — The Double-Edged Sword

#The Syntax

#When to Use IFERROR: Division Where a Zero Denominator Is Expected

#When NOT to Use IFERROR: Workpaper Formulas with External References

#The IFERROR Conversion Rule

#2. IFNA — The Safe Replacement for Lookup Errors

#The Syntax

#Tax Workpaper Example 1: Trial Balance Account Mapping

#Tax Workpaper Example 2: Prior-Year Balance Lookup

#Tax Workpaper Example 3: XLOOKUP with a Fallback

#The Conversion Checklist: IFERROR → IFNA

#3. ISERROR and ISNA — Flag Errors Without Hiding Them

#The Syntax

#Tax Workpaper Example 1: Audit-Friendly Error Flags

#Tax Workpaper Example 2: Conditional Formatting Based on Error Status

#Tax Workpaper Example 3: Count Errors for a Summary Dashboard

#4. The Error Trap Pattern — Surface Errors Where They Matter

#Pattern: Two-Level Error Handling

#When to Use This Pattern vs. Simpler Wrappers

#5. The Three Most Common IFERROR Mistakes in Tax Workpapers

#Mistake 1: IFERROR on a Formula That Contains VLOOKUP

#Mistake 2: IFERROR on a SUM of Multiple Lookups

#Mistake 3: IFERROR on a Formula That References Multiple Sheets

#6. Decision Tree — Which Function for Which Error

#Quick Decision Table

#Adapt It

Get the next macro in your inbox