You can use the following formula to return a blank value instead of #N/A when using the VLOOKUP function in Excel:
=IF(ISNA(VLOOKUP(D2,A2:B11,2,0)),"",VLOOKUP(D2,A2:B11,2,0))
This particular example attempts to look up the value in cell D2 in the range A2:B11 and return the corresponding value in the second column of the range.
If the lookup value is not found, then a blank is returned.
The following example shows how to use this syntax in practice.
Example: Return Blank Instead of #N/A in VLOOKUP
Suppose we have the following dataset in Excel that contains information about various basketball players:
Suppose we use the following formula with VLOOKUP to look up “Jazz” in the dataset and return the corresponding value from the points column:
=VLOOKUP(D2,A2:B11,2,FALSE)
The following screenshot shows how to use this formula in practice:
Since “Jazz” does not exist in the team column, the VLOOKUP function returns #N/A as a result.
However, we can use the following formula to return a blank instead of #N/A:
=IF(ISNA(VLOOKUP(D2,A2:B11,2,0)),"",VLOOKUP(D2,A2:B11,2,0))
The following screenshot shows how to use this formula in practice:
Notice that a blank is now returned instead of #N/A.
How This Formula Works
Recall the formula that we used to return a blank instead of #N/A using VLOOKUP:
=IF(ISNA(VLOOKUP(D2,A2:B11,2,0)),"",VLOOKUP(D2,A2:B11,2,0))
Here is how this formula works:
The ISNA function returns TRUE or FALSE to indicate whether or not the output of the VLOOKUP function is #N/A.
Our formula then uses an IF function to check if the result of ISNA is TRUE or FALSE.
If the result is TRUE (i.e. the lookup value was not found) then the formula returns a blank.
If the result is FALSE (i.e. the lookup value was found) then the formula simply returns the result of the VLOOKUP function.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
Excel: How to Compare Two Lists Using VLOOKUP
Excel: Return 0 Instead of #N/A with VLOOKUP
Excel: VLOOKUP if Cell Contains Word within Text
