Soumik Dutta
VLOOKUP is a widely used function. Using this function, we can extract data from one dataset to another. Sometimes, we need blank cells at the position of empty cells. However, the VLOOKUP function returns us 0. In this article, we will show seven possible ways to use the VLOOKUP function to return blank instead of 0. If you are curious about it, download our practice workbook and follow us.
Download Practice Workbook
Download this practice workbook for practice while you are reading this article.
7 Quick Ways for Using VLOOKUP to Return Blank Instead of 0 in Excel
To demonstrate the approaches, we consider a dataset of 10 employees of any organization. Our dataset is in the range of cells B5:D14. In the residency entities, we have 3 missing values in cells D8, D10, and D13. We applied the VLOOKUP function in the range of cells G5:G7, and the function returns us a 0 value instead of a blank cell.
Now, we will show you how you get the blank cell from the VLOOKUP function for an empty cell in the original dataset.
1. Utilizing IF and VLOOKUP Functions
In this method, we are going to use the IF and VLOOKUP functions to get blank instead of 0. The steps of this process are given below:
📌 Steps:
- First of all, select cell G5.
- Now, write down the following formula into the cell.
=IF(VLOOKUP(F5,$B$5:$D$14,3,FALSE)="","",VLOOKUP(F5,$B$5:$D$14,3,FALSE))
- Press Enter.
- You will see the formula returns us a blank cell instead of 0.
- Then, double-click on the Fill Handle icon to copy the formula up to cell G7.
- You will get the blank cell for all three values.
Thus, we can say that our formula worked perfectly, and VLOOKUP return blank instead of 0.
🔎 Breakdown of the Formula
We are breaking down the formula for cell G5.
👉 VLOOKUP(F5,$B$5:$D$14,3,FALSE): This function looks for the value of cell F5 in our dataset, which locates in the range of cells B5:D14, and it will print the corresponding value in column 3. As the value in column 3 for the value of F5 is blank, the function will return us 0. Otherwise, it will provide us with that value.
👉 IF(VLOOKUP(F5,$B$5:$D$14,3,FALSE)=””,””,VLOOKUP(F5,$B$5:$D$14,3,FALSE)): The IF function first checks the value of the VLOOKUP function. If the VLOOKUP function returns blank or the logic is true, the IF function returns blank in cell G5. On the other hand, if the logic is false, the function returns the value of the VLOOKUP function.
Read More: How to Use XLOOKUP to Return Blank Instead of 0
2. Using IF, LEN and VLOOKUP Functions
In this process, we will use the IF, LEN, and VLOOKUP functions to get blank instead of 0. The steps of this approach are given as follows:
📌 Steps:
- First, select cell G5.
- After that, write down the following formula into the cell.
=IF(LEN(VLOOKUP(F5,$B$5:$D$14,3,FALSE))=0,"",VLOOKUP(F5,$B$5:$D$14,3,FALSE))
- Press the Enter.
- You will notice the formula returns us a blank cell instead of 0.
- Now, double-click on the Fill Handle icon to copy the formula up to cell G7.
- You will get the blank cell for all three values.
So, we can say that our formula worked precisely, and VLOOKUP return blank instead of 0.
🔎 Breakdown of the Formula
We are breaking down the formula for cell G5.
👉 VLOOKUP(F5,$B$5:$D$14,3,FALSE): This function looks for the value of cell F5 in our dataset, which locates in the range of cells B5:D14, and it will print the value of column 3. As the value in column 3 for the value of F5 is blank, the function will return us 0. Otherwise, it will provide us with that value.
👉 LEN(VLOOKUP(F5,$B$5:$D$14,3,FALSE)): This function counts the character length of the result got from the VLOOKUP function. In this case, the value is 0.
👉 IF(LEN(VLOOKUP(F5,$B$5:$D$14,3,FALSE))=0,””,VLOOKUP(F5,$B$5:$D$14,3,FALSE): The IF function first checks the value of the LEN function. If the result of the LEN function is 0 or the logic is true, the IF function returns blank in cell G5. On the other hand, if the logic is false, the function returns the value of the VLOOKUP function.
Read More: Excel IFERROR Function to Return Blank Instead of 0
3. Combining IF, ISBLANK and VLOOKUP Functions
In this approach, the IF, ISBLANK, and VLOOKUP functions will help us to get blank instead of 0. The steps of this procedure are given below:
📌 Steps:
- At first, select cell G5 and write down the following formula into the cell.
=IF(ISBLANK(VLOOKUP(F5,$B$5:$D$14,3,FALSE)),"",VLOOKUP(F5,$B$5:$D$14,3,FALSE))
- Press the Enter.
- You will see the formula returns us a blank cell instead of 0.
- After that, double-click on the Fill Handle icon to copy the formula up to cell G7.
- You will get the blank cell for all three values.
Hence, we can say that our formula worked effectively, and VLOOKUP return blank instead of 0.
🔎 Breakdown of the Formula
We are breaking down the formula for cell G5.
👉 VLOOKUP(F5,$B$5:$D$14,3,FALSE): This function looks for the value of cell F5 in our dataset, which locates in the range of cells B5:D14, and it will print the value of column 3. As the value in column 3 for the value of F5 is blank, the function will return us 0. Otherwise, it will provide us with that value.
👉 ISBLANK(VLOOKUP(F5,$B$5:$D$14,3,FALSE)): This function checks the result from the VLOOKUP function. If the cell is empty the function will return TRUE. Otherwise, it will return FALSE. In this case, the value is TRUE.
👉 IF(ISBLANK(VLOOKUP(F5,$B$5:$D$14,3,FALSE)),””,VLOOKUP(F5,$B$5:$D$14,3,FALSE)): The IF function first checks the value of the ISBLANK function. If the result of the ISBLANK function is true, the IF function returns blank in cell G5. On the other hand, if the logic is false, the function returns the value of the VLOOKUP function.
Read More: How to Exclude Zero Values with Formula in Excel (3 Easy Ways)
Similar Readings
- How to Remove Zeros in Front of a Number in Excel (6 Easy Ways)
- Hide Rows with Zero Values in Excel Using Macro (3 Ways)
- How to Hide Chart Series with No Data in Excel (4 Easy Methods)
- Hide Zero Values in Excel Pivot Table (3 Easy Methods)
4. Applying IF, ISNUMBER and VLOOKUP Functions
In this procedure, we are going to use the IF, ISNUMBER, and VLOOKUP functions to get blank instead of 0. The steps of this process are explained below:
📌 Steps:
- In the beginning, select cell G5.
- Now, write down the following formula into the cell.
=IF(ISNUMBER(VLOOKUP(F5,$B$5:$D$14,3,FALSE)),VLOOKUP(F5,$B$5:$D$14,3,FALSE),"")
- Press Enter.
- You will see the formula returns us a blank cell instead of 0.
- Afterward, double-click on the Fill Handle icon to copy the formula up to cell G7.
- You will get the blank cell for all three values.
At last, we can say that our formula worked successfully, and VLOOKUP return blank instead of 0.
🔎 Breakdown of the Formula
We are breaking down the formula for cell G5.
👉 VLOOKUP(F5,$B$5:$D$14,3,FALSE): This function looks for the value of cell F5 in our dataset, which locates in the range of cells B5:D14, and it will print the value of column 3. As the value in column 3 for the value of F5 is blank, the function will return us 0. Otherwise, it will provide us with that value.
👉 ISNUMBER(VLOOKUP(F5,$B$5:$D$14,3,FALSE)): This function checks the result got from the VLOOKUP function. If the cell is empty the function will return FALSE. Otherwise, it will return TRUE. In this case, the value is FALSE.
👉 IF(ISNUMBER(VLOOKUP(F5,$B$5:$D$14,3,FALSE)),VLOOKUP(F5,$B$5:$D$14,3,FALSE),””): The IF function first checks the value of the ISNUMBER function. If the result of the ISNUMBER function is FALSE, the IF function returns blank in cell G5. On the other hand, if the logic is TURE, the function returns the value of the VLOOKUP function.
Read More: How to Leave Cell Blank If There Is No Data in Excel (5 Ways)
5. Using IF, IFNA and VLOOKUP Functions
In this case, we are going to use the IF, IFNA, and VLOOKUP functions to get blank instead of 0. The steps of this method are given as follows:
📌 Steps:
- At the beginning of this method, select cell G5.
- Then, write down the following formula into the cell.
=IF(IFNA(VLOOKUP(F5,$B$5:$D$14,3,FALSE),0)=0,"",VLOOKUP(F5,$B$5:$D$14,3,FALSE))
- Press Enter.
- You will notice the formula returns us a blank cell instead of 0.
- After that, double-click on the Fill Handle icon to copy the formula up to cell G7.
- You will get the blank cell for all three values.
In the end, we can say that our formula worked perfectly, and VLOOKUP return blank instead of 0.
🔎 Breakdown of the Formula
We are breaking down the formula for cell G5.
👉 VLOOKUP(F5,$B$5:$D$14,3,FALSE): This function looks for the value of cell F5 in our dataset, which locates in the range of cells B5:D14, and it will print the value of column 3. As the value in column 3 for the value of F5 is blank, the function will return us 0. Otherwise, it will provide us with that value.
👉 IFNA(VLOOKUP(F7,$B$5:$D$14,3,FALSE),0): This function counts the character length of the result got from the VLOOKUP function. In this case, the value is 0.
👉 IF(IFNA(VLOOKUP(F5,$B$5:$D$14,3,FALSE),0)=0,””,VLOOKUP(F5,$B$5:$D$14,3,FALSE)): The IF function first checks the value of the IFNA function. If the result of the IFNA function is 0, the IF function returns blank in cell G5. Otherwise, the function returns the value of the VLOOKUP function.
6. Applying IFERROR and VLOOKUP Functions
In the following method, we will use the IFERROR and VLOOKUP functions to get blank instead of 0. We have to look for that value that doesn’t exist in our dataset. In such a case, the formula will return a blank cell instead of 0. The steps of this method are given as follows:
📌 Steps:
- First of all, select cell G5.
- Now, write down the following formula into the cell.
=IFERROR(VLOOKUP(F5,$B$5:$D$14,3,FALSE)," ")
- Press Enter.
- You will see that the formula returns us a blank cell instead of 0.
- Afterward, double-click on the Fill Handle icon to copy the formula up to cell G6.
- You will get the blank cell for both values.
Finally, we can say that our formula worked effectively, and VLOOKUP return blank instead of 0.
🔎 Breakdown of the Formula
We are breaking down the formula for cell G5.
👉 VLOOKUP(F5,$B$5:$D$14,3,FALSE): This function looks for the value of cell F5 in our dataset, which locates in the range of cells B5:D14, and it will print the value of column 3. As the value in column 3 for the value of F5 is blank, the function will return us 0. Otherwise, it will provide us with that value.
👉 IFERROR(VLOOKUP(F5,$B$5:$D$14,3,FALSE),” “): The IFERROR function first checks the value of the VLOOKUP function. If the result of the VLOOKUP function is 0, the IF function returns blank in cell G5. Otherwise, the function returns the value of the VLOOKUP function.
7. Utilizing IF, IFERROR and VLOOKUP Functions
In the following approach, the IFERROR, IF, LEN, and VLOOKUP functions will help us to get the blank cell instead of 0. The procedure is given below step-by-step:
📌 Steps:
- At first, select cell G5.
- After that, write down the following formula into the cell.
=IFERROR(IF(LEN(VLOOKUP(F5,$B$5:$D$14,3,0))=0,"",VLOOKUP(F5,$B$5:$D$14,3,0)), "")
- Press Enter.
- You will see the formula returns us a blank cell instead of 0.
- Now, double-click on the Fill Handle icon to copy the formula up to cell G7.
- You will get the blank cell for all three values.
Thus, we can say that our formula worked successfully, and VLOOKUP return blank instead of 0.
🔎 Breakdown of the Formula
We are breaking down the formula for cell G5.
👉 VLOOKUP(F5,$B$5:$D$14,3,FALSE): This function looks for the value of cell F5 in our dataset, which locates in the range of cells B5:D14, and it will print the value of column 3. As the value in column 3 for the value of F5 is blank, the function will return us 0. Otherwise, it will provide us with that value.
👉 LEN(VLOOKUP(F5,$B$5:$D$14,3,FALSE)): This function counts the character length of the result got from the VLOOKUP function. In this case, the value is 0.
👉 IF(LEN(VLOOKUP(F5,$B$5:$D$14,3,FALSE))=0,””,VLOOKUP(F5,$B$5:$D$14,3,FALSE): The IF function first checks the value of the LEN function. If the result of the LEN function is 0 or the logic is true, the IF function returns blank in cell G5. On the other hand, if the logic is false, the function returns the value of the VLOOKUP function.
👉 IFERROR(IF(LEN(VLOOKUP(F5,$B$5:$D$14,3,0))=0,””,VLOOKUP(F5,$B$5:$D$14,3,0)), “”): This function checks the decision of the IF function. If the function returns a blank cell, the IFERROR function shows us the blank. Otherwise, the function will show the value of the corresponding cell in column 3.
Conclusion
That’s the end of this article. I hope that this article will be helpful for you and you will be able to use the VLOOKUP function to return blank instead of 0 in Excel. Please share any further queries or recommendations with us in the comments section below if you have any further questions or recommendations.
Don’t forget to check our website ExcelDemy for several Excel-related problems and solutions. Keep learning new methods and keep growing!
