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.
IF function will give the condition on the basis of ISERROR function.
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.
Let’s start by using the Vlookup function in data:-
=VLOOKUP($F3,$A$3:$D$13,2,FALSE)
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:-
=IF(ISERROR(VLOOKUP($F3,$A$3:$D$13,2,FALSE)),"",VLOOKUP($F3,$A$3:$D$13,2,FALSE))
Now you can see that instead of showing errors, cells are appearing blank.
To avoid zero, use the function as per the below procedure:-
=IF(VLOOKUP($F3,$A$3:$D$13,2,FALSE)=0,"",VLOOKUP($F3,$A$3:$D$13,2,FALSE)
To avoid zero and error together, apply below function:-
=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)))
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.
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
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.
The null string "" is not the same as an empty cell. This still evaluates to 0 if used in further calculations.