#NUM! is one of the most common errors in Google Sheets. This tutorial explains exactly what causes it, how to diagnose where it originates, and the cleanest patterns to fix it without papering over a real bug. The goal is not to suppress the error — that hides the symptom — but to understand what your sheet is telling you.
What #NUM! means
#NUM! means a numeric calculation produced an out-of-range or invalid result — usually a financial function whose inputs cannot converge, or a square root of a negative number.
Diagnose before you suppress
Before wrapping in IFERROR, find out which formula is producing the error. Click the cell, look at the formula bar, and check each argument's value in turn: hover over the range to see its evaluated content, or paste the inner expressions into scratch cells. In modern Sheets, Tools → Formula evaluator (where available) steps through the calculation. If you suppress the error before understanding it, you will spend the rest of the day debugging the next layer of the sheet that depends on what was supposed to be a real value.
Fix at the source when you can
If the lookup key is consistently mistyped, fix the input column with TRIM/CLEAN/PROPER. If a category is missing from the lookup table, add it. If the imported data lost a column, restore the structure upstream. Treating #NUM! with IFERROR is correct only when you have decided that an empty match is a legitimate state your sheet should handle gracefully.
Suppress only intentionally
=IFNA(VLOOKUP(A2, Lookup!A:B, 2, FALSE), "Not found")Use IFNA rather than IFERROR when you only want to absorb "value not available" — IFNA leaves real syntax errors alone so you still notice them. Reserve IFERROR for cases where any error should produce the fallback ("if anything at all goes wrong, show '—'").
Worked examples
Suppress only NA, surface other errors
=IFNA(VLOOKUP(A2, Lookup!A:B, 2, FALSE), "")Real syntax errors still appear, so you do not lose visibility on them.
Diagnose with type checks
=ISNUMBER(A2) & " / " & ISTEXT(A2) & " / " & LEN(A2)Quickly tells you whether the lookup key is the right type and length.
Use IFERROR sparingly
=IFERROR(VLOOKUP(...), 0)Suppresses every error including #REF and #VALUE — only use when you genuinely want that.
Common mistakes (and how to avoid them)
- Wrapping in IFERROR without checking the underlying problem — the bug stays in the sheet, just invisibly.
- Suppressing
#NUM!with a numeric fallback that downstream formulas treat as a real value (sums turn into garbage). - Confusing
#NUM!with#NAME?— the fixes are entirely different. - Ignoring locale: a date-typed field arriving as text fails lookups silently. Coerce explicitly.
Tips that pay off later
- Add a small "diagnostics" tab with formulas like
=COUNTIF(Source!A:A, "")and=COUNTIF(Source!A:A, "<>"&"")to spot data drift early. - Use conditional formatting to highlight cells equal to
#NUM!red so they cannot hide in a busy sheet. - Document any IFERROR fallbacks with a note (right-click → Insert note) explaining why the error is acceptable here.
When to reach for something else
If #NUM! appears in many places at once, the right fix is upstream — at the import, at the form validation, or at the source sheet — not by sprinkling IFERROR everywhere.