Table of Contents
VLOOKUP is a useful function in Microsoft Excel that allows users to search for a value in a specific column and return a corresponding value from a different column. However, in certain cases, the function may return a zero instead of a blank cell, which can be undesirable. To avoid this, users can add an additional parameter to the VLOOKUP function that specifies what value should be returned if a match is not found. By setting this parameter to an empty string (“”) instead of zero, users can ensure that the function will return a blank cell instead of zero, providing a neater and more accurate representation of the data. This simple adjustment can be helpful in various scenarios, such as when working with numerical data or generating reports.
Excel: Use VLOOKUP to Return Blank Instead of Zero
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.
The following tutorials explain how to perform other common tasks in Excel:
Cite this article
stats writer (2024). How can I use VLOOKUP in Excel to return a blank cell instead of zero?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-can-i-use-vlookup-in-excel-to-return-a-blank-cell-instead-of-zero/
stats writer. "How can I use VLOOKUP in Excel to return a blank cell instead of zero?." PSYCHOLOGICAL SCALES, 23 Jun. 2024, https://scales.arabpsychology.com/stats/how-can-i-use-vlookup-in-excel-to-return-a-blank-cell-instead-of-zero/.
stats writer. "How can I use VLOOKUP in Excel to return a blank cell instead of zero?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-can-i-use-vlookup-in-excel-to-return-a-blank-cell-instead-of-zero/.
stats writer (2024) 'How can I use VLOOKUP in Excel to return a blank cell instead of zero?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-can-i-use-vlookup-in-excel-to-return-a-blank-cell-instead-of-zero/.
[1] stats writer, "How can I use VLOOKUP in Excel to return a blank cell instead of zero?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. How can I use VLOOKUP in Excel to return a blank cell instead of zero?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.