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:
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