You can use the following basic syntax to replace #N/A values in Excel with either zeros or blanks:

#replace #N/A with zero
=IFERROR(FORMULA, "0")

#replace #N/A with blank
=IFERROR(FORMULA, "") 

The following example shows how to use this syntax in practice to replace #N/A values from a VLOOKUP with zero or blanks.

Related: How to Replace Blank Cells with Zero in Excel

Example: Replace #N/A Values in Excel

Suppose we have the following dataset in Excel:


And suppose we use the VLOOKUP() function to look up points based on team name:

Notice that some of the values returned in the VLOOKUP() are #N/A values.

We can turn these values into zeros by using the IFERROR() function as follows:

#replace #N/A with zero
=IFERROR(VLOOKUP(A2, $A$1:$B$11, 2, FALSE), "0")

The following screenshot shows how to use this function in practice:

Alternatively, we can turn the #N/A values into blanks using the IFERROR() function as follows:

#replace #N/A with blank
=IFERROR(VLOOKUP(A2, $A$1:$B$11, 2, FALSE), "")

The following screenshot shows how to use this function in practice:

Notice that each value that was previously #N/A is now blank.

Using the IFERROR() function, we can replace #N/A values with any value that we’d like.

In the previous examples, we simply chose to replace #N/A values with zeros or blanks because these are the most common replacement values used in practice.

Alternative Method 1: Using IFNA() Function

Excel also provides the IFNA() function, which is specifically designed to handle #N/A errors. Unlike IFERROR() which catches all types of errors, IFNA() only replaces #N/A errors with a specified value.

The syntax for IFNA() is:

=IFNA(value, value_if_na)

Let’s apply this to our VLOOKUP() example to replace #N/A with zeros:

#replace only #N/A with zero
=IFNA(VLOOKUP(A2, $A$1:$B$11, 2, FALSE), "0")

And to replace #N/A with blanks:

#replace only #N/A with blank
=IFNA(VLOOKUP(A2, $A$1:$B$11, 2, FALSE), "")

The advantage of IFNA() is that it only catches #N/A errors, allowing other error types (like #DIV/0! or #VALUE!) to still appear, which can be helpful for troubleshooting.

Alternative Method 2: Using IF() with ISNA() Function

Another alternative uses the combination of IF() and ISNA() functions. This method is straightforward and works well for basic lookups. Here’s how to replace #N/A values with zeros:

#replace #N/A with zero using IF and ISNA
=IF(ISNA(VLOOKUP(A2, $A$1:$B$11, 2, FALSE)), "0", VLOOKUP(A2, $A$1:$B$11, 2, FALSE))

And to replace #N/A with blanks:

#replace #N/A with blank using IF and ISNA
=IF(ISNA(VLOOKUP(A2, $A$1:$B$11, 2, FALSE)), "", VLOOKUP(A2, $A$1:$B$11, 2, FALSE))

While this method requires typing the VLOOKUP() formula twice, it’s easy to understand and works in all versions of Excel.

Wrapping Up

Excel offers multiple ways to handle #N/A values in your worksheets. The IFERROR() function provides a simple solution that works with all error types, while IFNA() specifically targets #N/A errors. For users who prefer more explicit control, the IF() with ISNA() combination offers a clear approach that works in all Excel versions. When deciding which method to use, consider the following:

• Use IFERROR() when you want to catch all possible errors with one function
• Use IFNA() when you only want to replace #N/A errors while letting other error types display
• Consider IF() with ISNA() for compatibility with older Excel versions or when you want more explicit formula logic

In most common Excel tasks, the IFERROR() approach offers the best balance of simplicity and functionality.

Additional Resources

How to Perform a Percentile IF Function in Excel
How to Perform a Median IF Function in Excel