Turn #N/A into Zero or Blank

Excel Formulas › Information

All versionsIFNA

A missing lookup returns #N/A, which then breaks every SUM or chart downstream. IFNA swaps just that error for a 0, a blank, or a friendly message — without hiding other errors.

Quick formula: return 0 instead of #N/A from a lookup:
=IFNA(VLOOKUP(id, table, 2, 0), 0)
IFNA catches only #N/A; a real #VALUE! still surfaces so genuine problems are not masked.

Functions used (tap for the full reference guide):

IFNAISNA

The example

Missing matches become 0, not #N/A.

AB
1LookupResult
2foundvalue
3missing0

The formula

The formula:

=IFNA(VLOOKUP(id, table, 2, 0), 0) // only catches #N/A

How it works

How it works:

  1. IFNA(value, fallback) returns the fallback only when the value is #N/A.
  2. Unlike IFERROR, it lets other errors through — a #DIV/0! or #REF! still shows, so real bugs are not buried.
  3. Use 0 to keep sums working, "" for a clean blank, or text like "Not found".
  4. Pre-2013: wrap with =IF(ISNA(x), 0, x) for the same effect.

Blank vs zero matters for charts and averages. A 0 plots as a point on the axis and pulls an AVERAGE down; "" (blank text) is skipped by AVERAGE but still counts as non-empty for COUNTA. Use NA() itself if you specifically want a line chart to skip the gap rather than drop to zero.

Try it: interactive demo

Live demo

Look up an ID (try a missing one).

Result:

Variations

Blank instead

Empty string:

=IFNA(VLOOKUP(id,t,2,0), "")

Legacy version

Pre-2013:

=IF(ISNA(x), 0, x)

Keep chart gaps

Skip the point:

=IF(missing, NA(), value)

Pitfalls & errors

IFNA ≠ IFERROR. IFNA catches only #N/A; use it so real errors like #REF! still show.

0 vs blank. Zero affects averages and charts; an empty string does not, but counts as text.

2013+ for IFNA. Older Excel needs the IF(ISNA(...)) form.

Practice workbook

📊
Download the free Turn #N/A into Zero or Blank practice workbook
A #N/A-handling sheet with the blank, legacy, and chart-gap variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I replace #N/A with 0 in Excel?
Wrap the lookup in IFNA: =IFNA(VLOOKUP(id, table, 2, 0), 0). It swaps only #N/A for your fallback and lets other errors surface.
Why use IFNA instead of IFERROR?
IFERROR hides every error, including real problems like #REF! or #VALUE!. IFNA catches only #N/A, so genuine bugs still show.
Should I use 0 or blank?
Use 0 to keep sums working; use "" for a clean look. Note 0 lowers averages and plots on charts, while blank text is skipped by AVERAGE.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: IFNA handling · IFERROR handling · Count errors in a range

Function references: IFNA · ISNA