How to Replace #N/A with a Blank in Google Sheets (IFNA vs IFERROR)

To show a blank cell instead of #N/A in Google Sheets, wrap the formula in IFNA and use an empty text as the replacement:

=IFNA(VLOOKUP(A2, Products!A:C, 3, FALSE), "")

#N/A means “value not available”: a lookup such as VLOOKUP, MATCH or XLOOKUP didn’t find what it was looking for. IFNA replaces only that error and leaves every other error visible, which is usually what you want.

Tested in Google Sheets. Every formula on this page was entered in Google Sheets on November 10, 2026, including the cases where IFERROR hides a different error. How we test

IFNA or IFERROR?

Both can return a blank, but they catch different things:

IFNA(value, "") IFERROR(value, "")
#N/A (not found) Replaced Replaced
#REF!, #DIV/0!, #VALUE!… Still shown Also replaced
Second argument Required Optional: leaving it out returns a blank

Found in testing: a VLOOKUP that asked for column 3 of a two-column range is a broken formula (#REF!). Wrapped in IFERROR, it showed an empty cell, as if the product wasn’t found. Wrapped in IFNA, the #REF! stayed visible, so the mistake could be noticed and fixed.

Use IFERROR only when any error should produce the same result, for example when dividing by a quantity that may be zero: =IFERROR(B2/C2, 0).

XLOOKUP has the option built in

If you use XLOOKUP, you don’t need IFNA: its fourth argument is the value to return when nothing is found.

=XLOOKUP(A2, Products!A:A, Products!C:C, "")

Show something other than a blank

  • Zero, so totals keep working: =IFNA(VLOOKUP(A2, Products!A:C, 3, FALSE), 0)
  • A message, so missing values are easy to spot: =IFNA(VLOOKUP(A2, Products!A:C, 3, FALSE), "Not found")

A whole column in one formula

With lookup values in A2:A, this fills the column and shows a dash for values that aren’t found:

=ARRAYFORMULA(IF(A2:A = "", "", IFNA(VLOOKUP(A2:A, Products!A:C, 3, FALSE), "-")))

A “blank” from a formula is not empty

The empty text "" looks like a blank cell, but other functions don’t always treat it as one. In our tests, a cell where IFNA returned "" behaved like this:

Formula on that cell Result
ISBLANK FALSE: the cell contains a formula
COUNTA Counts it, so two such cells gave 2
COUNTBLANK Counts it as blank

If you need to count real results, count something that excludes empty text, for example =COUNTIF(B2:B, "?*") for text results or =COUNT(B2:B) for numbers.

Before hiding #N/A, check why it appears

Sometimes the value is in the list and #N/A points to a data problem. Two causes we reproduced:

  • Extra spaces. Looking up "a " (with a trailing space) in a list containing "a" returned #N/A. TRIM fixed it: =VLOOKUP(TRIM(A2), …).
  • Numbers stored as text. Looking up the text "1" in a list of numbers returned #N/A. VALUE fixed it: =VLOOKUP(VALUE(A2), …).

Hiding #N/A in those cases would hide rows that should have matched.

Related guides