Table of Contents
If you’ve ever used VLOOKUP in Excel, you know it can be a powerful tool for finding data in a large spreadsheet. But sometimes, instead of returning the data you’re looking for, VLOOKUP will return an error message like “#N/A”. In this article, we’ll go over how to use VLOOKUP to return a blank instead of this error message. With this knowledge, you’ll be able to make your spreadsheets easier to read and more accurate.
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:
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.