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:

Notice that a blank is now returned instead of a zero.

**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.

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