Table of Contents
IFERROR is a function in Google Sheets that allows users to display a blank cell when an error occurs in a formula. This can be useful in avoiding confusion and making the spreadsheet look more organized. By using IFERROR, users can easily identify and correct the error without the need to manually delete the error message. This function is particularly helpful when dealing with large data sets or complex formulas. By simply wrapping the formula with IFERROR, users can ensure that a blank cell will be displayed instead of an error message, providing a cleaner and more professional appearance to the spreadsheet.
Google Sheets: Use IFERROR Then Blank
You can use the following methods in Google Sheets to return a blank value instead of an error value when a valid value isn’t returned from a formula:
Method 1: IFERROR Then Blank with a Formula
=IFERROR(A2/B2, "")
Method 2: IFERROR Then Blank with VLOOKUP
=IFERROR(VLOOKUP(E2, $A$2:$C$12, 3, FALSE), "")
The following examples show how to use each method in practice.
Example 1: IFERROR Then Blank with a Formula
Suppose we use the following formula to divide the values in column B by the values in column A in this particular Google Sheets spreadsheet:
=A2/B2
For each cell in column C where we attempt to divide by zero, the formula returns #DIV/0! as a result.
To return a blank value instead of an error value, we can type the following formula into cell C2:
=IFERROR(A2/B2, "")
We can then click and drag this formula down to each remaining cell in column C:
Notice that column C now returns a blank value as a result if we attempt to divide by zero.
Example 2: IFERROR Then Blank with VLOOKUP
=VLOOKUP(E2, $A$2:$C$12, 3, FALSE)
Notice that for each cell in column G where we encounter an empty value in the VLOOKUP function, we receive #N/A as a result.
To return a blank value instead of a #N/A value, we can type the following formula into cell F2:
=IFERROR(VLOOKUP(E2, $A$2:$C$12, 3, FALSE), "")
We can then copy and paste this formula down to every remaining cell in column F:
Now for each cell where we encounter an empty value in the VLOOKUP function, we simply receive a blank value as a result.
Note: You can find the complete documentation for the IFERROR function in Google Sheets .
Additional Resources
The following tutorials explain how to perform other common tasks in Google Sheets: