How to Replace #N/A Values in Google Sheets


You can use the following basic syntax to replace #N/A values in Google Sheets 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 Google Sheets

Example: Replace #N/A Values in Google Sheets

Suppose we have the following dataset in Google Sheets that shows the team name and points scored for various basketball teams:

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:

=IFERROR(VLOOKUP(A2, $A$2:$B$11, 2, FALSE), "0")

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

Google Sheets replace #N/A values

Notice that the VLOOKUP() function now returns 0 instead of #N/A values.

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$2:$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.

It’s worth noting that we can use the IFERROR() function to replace #N/A values with any value that we’d like.

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

Additional Resources

The following tutorials explain how to perform other common tasks in Google Sheets:

How to Ignore Blank Cells with Formulas in Google Sheets
How to Ignore #N/A Values with Formulas in Google Sheets

Leave a Reply

Your email address will not be published.