Table of Contents
Have you ever wanted to use Excel’s VLOOKUP function to return a blank instead of a zero? If so, this article is for you! This tutorial will show you how to use VLOOKUP to return a blank instead of a zero in your Excel spreadsheet. By following the instructions in this article, you will be able to easily and quickly modify your VLOOKUP formula to return a blank instead of a zero. So let’s get started and learn how to use VLOOKUP to return a blank instead of a zero in Excel.
You can use the following formula to return a blank value instead of a zero when using the VLOOKUP function in Excel:
=IF(LEN(VLOOKUP(D2,A2:B11,2,0))=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 no value is found in the second column, then a blank is returned.
The following example shows how to use this syntax in practice.
Example: Return Blank Instead of Zero 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 “Nets” 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 no value exists in the points column for the Nets, the VLOOKUP function returns a value of zero by default.
However, we can use the following formula to return a blank instead of a zero:
=IF(LEN(VLOOKUP(D2,A2:B11,2,0))=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 a zero using VLOOKUP:
=IF(LEN(VLOOKUP(D2,A2:B11,2,0))=0,"",VLOOKUP(D2,A2:B11,2,0))
Here is how this formula works:
The LEN function counts the number of characters in the output of the VLOOKUP function.
Our formula uses an IF function to check if the number of characters in the output of the VLOOKUP function is zero.
If the length is zero, then the formula returns “” – a blank.
If the length is not zero, then the formula simply returns the output from the VLOOKUP function.