Excel: How to Use IFERROR Then Blank


You can use the following methods in Excel 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 Some Formula

=IFERROR(B2/A2, "")

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 Some Formula

Suppose we use the following formula to divide the values in column B by the values in column A in this particular Excel spreadsheet:

=B2/A2

Notice that for each cell in column C where we attempt to divide by a blank value, we receive #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 copy and paste this formula down to every remaining cell in column C:

Now for each cell in column C where we attempt to divide by a blank value, we simply receive a blank value as a result.

Example 2: IFERROR Then Blank with VLOOKUP

Suppose we use the following VLOOKUP formula to look up the player name in column A and return the rebounds value in column C:

VLOOKUP(F2, $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(F2, $A$2:$C$12, 3, FALSE), "")

We can then copy and paste this formula down to every remaining cell in column F:

IFERROR then blank formula in Excel

Now for each cell where we encounter an empty value in the VLOOKUP function, we simply receive a blank value as a result.

Additional Resources

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

How to Ignore #N/A Values When Using Formulas in Excel
How to Replace #N/A Values in Excel
How to Fix the #NAME Error in Excel

Leave a Reply

Your email address will not be published.