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.TRIMfixed it:=VLOOKUP(TRIM(A2), …). - Numbers stored as text. Looking up the text
"1"in a list of numbers returned #N/A.VALUEfixed it:=VLOOKUP(VALUE(A2), …).
Hiding #N/A in those cases would hide rows that should have matched.