How to change error result calculation when using Vlookup formula to 0 or empty cell in Microsoft Excel

Sometimes, we get error or zero as result while applying the Vlookup formula. We get #N/A error when all the lookup values are not available in the data, and we get zero when the lookup value is available but the pick cell is blank.

In this article, we are going to learn how we can change result while applying the Vlookup formula in Microsoft Excel.

To solve this problem, we will use IF, ISERROR and Vlookup functions.

ISERROR function helps us to check the error and will return result as true or false.

image 1

 

IF function will give the condition on the basis of ISERROR function.

image 2

 

Let’s take an example to understand:-

We have 2 data sets of employees. In the first data, we have details, like date of joining, employee name, and designation, and in 2nd data, we have only employee codes and we want to pick the date of joining through Vlookup function.

 

image 3

 

Let’s start by using the Vlookup function in data:-

  • Enter the formula in cell G2
  • =VLOOKUP($F3,$A$3:$D$13,2,FALSE)
    
  • Press Enter.
  • Copy the formula in the rest of cells.

 

image 4

 

Now you can see in the above image that few cells are having the result, but few cells are having #N/A error and first cell is showing 0.

To avoid error, use the function as per the below procedure:-

  • Enter the formula in cell H2
  • =IF(ISERROR(VLOOKUP($F3,$A$3:$D$13,2,FALSE)),"",VLOOKUP($F3,$A$3:$D$13,2,FALSE))
    
  • Press Enter
  • Copy the formula in the rest of cells.

 

image 5

 

Now you can see that instead of showing errors, cells are appearing blank.

To avoid zero, use the function as per the below procedure:-

  • Enter the formula in cell H2
  • =IF(VLOOKUP($F3,$A$3:$D$13,2,FALSE)=0,"",VLOOKUP($F3,$A$3:$D$13,2,FALSE)
    
  • Press Enter
  • Copy the formula in the rest of cells.

 

image 6

 

To avoid zero and error together, apply below function:-

  • Enter the formula in cell H2
  • =IF(ISERROR(VLOOKUP($F3,$A$3:$D$13,2,FALSE)),"-",IF(VLOOKUP($F3,$A$3:$D$13,2,FALSE)=0,"-",VLOOKUP($F3,$A$3:$D$13,2,FALSE)))
    
  • Press Enter
  • Copy the formula in the rest of cells.

 

image 7

 

In this way, we can use functions to solve our queries about Microsoft Excel.

Note: - In later version of Microsoft Excel 2003, we have IFERROR function in order to get the same result. This formula is compatible with all Microsoft Excel’s version.

home button
Previous Lookup Comparisons
Next How to use VLOOKUP function as an alternative of Nested IF function

 

image 29

 

If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook.
We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at info@exceltip.com

Users are saying about us...

Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Youtube