Md. Shamim Reza
The VLOOKUP function in Excel is very useful to search for matching results within a large dataset. But this function has a few limitations by design. For example, if the matching result points to a blank cell, the VLOOKUP function will return a 0 instead of an empty value. This becomes problematic when you need to know whether the matching result is really 0 or empty. In this article, we will show a quick solution to the “Excel VLOOKUP returning 0″ issue.
Download Practice Workbook
You can download the practice workbook from the download button below.
A Quick Solution When Excel VLOOKUP Is Returning 0 Instead of Exact Value
Consider the following VLOOKUP formula that returns 0 although the matching result is an empty cell.
=VLOOKUP(E5,B5:C12,2,FALSE)
Now, follow the below steps to fix the issue.
Step 1: Using IF Function to Stop VLOOKUP Returning 0
- You can use the IF function to create the following formula to return blanks instead.
=IF(VLOOKUP(E5,B5:C12,2,FALSE)="","",VLOOKUP(E5,B5:C12,2,FALSE))
- If you change the lookup_value, the formula will work fine.
Read More: [Fixed!]: VLOOKUP Function Is Returning Same Value in Excel
Step 2: Applying IFERROR Function to Stop VLOOKUP Returning Errors
- You can combine the above formula with the IFERROR function to return blanks in case of errors.
=IFERROR(IF(VLOOKUP(E5,B5:C12,2,FALSE)="","",VLOOKUP(E5,B5:C12,2,FALSE)),"")
Here, the IFERROR function returns blanks if the IF formula results in an error.
Read More: [Fixed!] Excel VLOOKUP Returning #N/A Error (6 Possible Solutions)
Step 3: Utilizing LEN and ISNUMBER functions
- You can also use the LEN function to check if the VLOOKUP formula points to a blank cell as follows.
=IF(LEN(VLOOKUP(E5,B5:C12,2,FALSE))=0,"",VLOOKUP(E5,B5:C12,2,FALSE))
- Alternatively, you can use the ISNUMBER function as follows if you need to return numbers only.
=IF(ISNUMBER(VLOOKUP(E5,B5:C12,2,FALSE)),VLOOKUP(E5,B5:C12,2,FALSE),"")
Read More: [Fixed!] Excel VLOOKUP Function Not Calculating Automatically
How to Fix VLOOKUP Returning Blank Instead of Value in Excel
Sometimes VLOOKUP may return blank even if there are other matching values in case the first matching value points to an empty cell. Because VLOOKUP returns the first matching result only.
- You can use the IF function with the FILTER function to return all matching values instead.
=IF(FILTER(C5:C12,B5:B12=E5)="","",FILTER(C5:C12,B5:B12=E5))
- You can use the TEXTJOIN function to ignore the blanks and concatenate other matching results in the formula cell.
=TEXTJOIN(",",TRUE,IF(FILTER(C5:C12,B5:B12=E5)="","",FILTER(C5:C12,B5:B12=E5)))
The TEXTJOIN function concatenates all the values in the array (ignoring empty results) returned by the IF formula into a single cell separated by a comma.
Read More: Excel VLOOKUP Returning Column Header Instead of Value
Things to Remember
- You need to press CTRL + SHIFT + Enter to apply the array formulas if you are not using Microsoft 365.
- The VLOOKUP function always picks up the first matching result in the lookup_array ignoring all other results.
Conclusion
Now you know how to stop the VLOOKUP function in Excel from returning 0 instead of a matching value. Do you have any further queries or suggestions? Please let us know in the comment section below. You can also visit our ExcelDemy blog to explore more about Excel. Stay with us and keep learning.
