Table of Contents
IFERROR is a function in Google Sheets that allows you to set a specific value or action if a formula results in an error. By using IFERROR, you can specify that the cell should be left blank if the formula returns an error, rather than displaying the error code. This can help keep your data clean and organized, as well as prevent any confusion or misinterpretation of the data. Simply wrap your formula with IFERROR and specify a blank value, and the cell will remain blank if the formula results in an error.
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: