[Fixed] Excel VLOOKUP Returning 0 Instead of Expected Value

Get FREE Advanced Excel Exercises with Solutions!

The VLOOKUP function in Excel is very useful to search for matching results within a large dataset. However, 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.


A Quick Solution When Excel VLOOKUP Is Returning 0 Instead of Exact Value

Consider the following VLOOKUP function that returns 0 although the matching result is an empty cell.

=VLOOKUP(E5,B5:C12,2,FALSE)

excel vlookup returning 0

Now, follow the below steps to fix the issue.


Step 1: Using the 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))

vlookup returning blank instead of 0

  • If you change the lookup_value, the formula will work fine.

vlookup formula

Formula Explanation: The formula may look a bit complex, but it is actually quite simple. The VLOOKUP formula has been used twice, first as the logical_test argument, and second as the value_if_false argument of the IF function. Here the IF function returns blanks if VLOOKUP(E5,B5:C12,2,FALSE)=“” returns TRUE. Otherwise, it returns the output of the VLOOKUP formula.

Step 2: Applying the 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)),"")

combine IFERROR and VLOOKUP

Here, the IFERROR function returns blanks if the IF formula results in an error.


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

LEN function to stop VLOOKUP returning 0

  • 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),"")

ISNUMBER function to stop VLOOKUP returning 0


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.

VLOOKUP returning blank instead of value

  • 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))

FILTER function to stop VLOOKUP retuning blank instead of value

Formula Explanation: Here the FILTER function performs the logical test B5:B12=E5 to filter the matching results for the lookup_value in the range B5:B12. If the logical_test of the formula FILTER(C5:C12,B5:B12=E5)=”” returns TRUE meaning empty results, the IF function returns blanks. Otherwise, it returns the output of the FILTER formula.
  • 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)))

TEXTJOIN function to concatenate multiple VLOOKUP results

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.


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.

Download Practice Workbook

You can download the practice workbook from the download button below.


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. Stay with us and keep learning.


Related Articles


<< Go Back to Issues with VLOOKUP | Excel VLOOKUP Function | Excel Functions | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Shamim Reza
Md. Shamim Reza

Md. Shamim Reza, a marine engineer with expertise in Excel and a fervent interest in VBA programming, sees programming as a time-saving tool for data manipulation, file handling, and internet interaction. His diverse skill set encompasses Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning. He holds a B.Sc in Naval Architecture & Marine Engineering from BUET and has transitioned into a content developer role, generating technical content focused on Excel and VBA. Beyond his professional pursuits,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo