Rafiul Haq
Whenever we lookup values using the VLOOKUP values, if there is no match, then it will show a “#N/A” error, and when there are 0 values, it will show the value zero. Are you looking for ways to use the VLOOKUP function to return a blank instead of 0 or NA in Excel? Then this is the right article for you. In this write-up, we will show five formulas to achieve just that.
Download Practice Workbook
5 Handy Ways to Apply VLOOKUP to Return Blank Instead of 0 or NA
We have taken a dataset with 2 columns: “Employee” and “Height(cm)” to demonstrate our methods. Moreover, there is another dataset to show the output. We can see that the value “Ross” is not listed on the primary dataset. Therefore, when we try to use the VLOOKUP function for that value, we get the “N/A” error in cell C14. However, we have modified the formula to show blank values instead of that error in cell C16.
We have shown 5 formulas to return blank with the VLOOKUP function.
- The first two methods show blank instead of 0.
- Then, method 3,4 shows a blank instead of the “#N/A” error.
- Lastly, method 5 returns blank for both the “#N/A” error and the 0 value.
1. Combining IF and VLOOKUP Functions to Return Blank
This section will combine the IF and VLOOKUP functions to return a blank instead of 0 in Excel. Here, we have meant blank cell with the value 0.
Steps:
- At first, type the following formula in cell C13.
=IF(VLOOKUP(B13,$B$5:$C$10,2,0)=0,"",VLOOKUP(B13,$B$5:$C$10,2,0))
If we did not add the IF function, then this function would have returned zero.
- Next, press ENTER.
Formula Breakdown
- Firstly, this formula has two identical VLOOKUP functions. The first one has a condition attached to it that checks if it equals 0. If it is not, then the second VLOOKUP function executes.
- VLOOKUP(B13,$B$5:$C$10,2,0)
- Output: 0.
- This function looks for the value from cell B13 in the range of B5:C10. If there is a match, then it returns the value from the respective C5:C10 range as indicated by 2 inside the function. The 0 means at the end of this function means the match type is exact.
- Our formula reduces to → IF(0=0,””,0)
- Output: (Blank).
- Here the logical_test is true, so we have got the blank output.
Read More: How to Leave Cell Blank If There Is No Data in Excel (5 Ways)
2. Incorporating IF, LEN, and VLOOKUP Functions to Return Blank
This second method will incorporate the IF, LEN, and VLOOKUP functions to return a blank instead of 0 or NA.
Steps:
- At first, type the following formula in cell C13.
=IF(LEN(VLOOKUP(B13,$B$5:$C$10,2,0))=0,"",VLOOKUP(B13,$B$5:$C$10,2,0))
- Next, press ENTER.
Formula Breakdown
- Again, this formula has two VLOOKUP functions. Moreover, we have used the first VLOOKUP function inside a LEN function, which returns the length of a string. Now, the length of a blank cell is 0. So, we have set this in the logical_test criteria.
- Now, VLOOKUP(B13,$B$5:$C$10,2,0)
- Output: 0.
- This function looks for the value from cell B13 in the range of B5:C10. If there is a match, then it returns the value from the respective C5:C10 range as indicated by 2 inside the function. The 0 means at the end of this function means the match type is exact.
- Our formula reduces to → IF(LEN(0)=0,””,0)
- Output: (Blank).
- The LEN function returns 0. So, the first portion of the IF function executes and we get the blank cell as the output.
Read More: How to Use XLOOKUP to Return Blank Instead of 0
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)
3. Merging IF, ISNUMBER, and VLOOKUP Functions to Return Blank
In the third method, we will use the IF, ISNUMBER, and VLOOKUP functions to return blank instead of the “#N/A” error.
Steps:
- At first, type the following formula in cell C13.
=IF(ISNUMBER(VLOOKUP(B13,$B$5:$C$10,2,0)),VLOOKUP(B13,$B$5:$C$10,2,0),"")
- Next, press ENTER.
Formula Breakdown
- This formula has two VLOOKUP functions. Moreover, we have used the first VLOOKUP function inside an ISNUMBER function, which returns true for a number and false for a non-numerical output. Now, if the first VLOOKUP function returns an error, then it will not be a number. So, we have set this in the logical_test criteria, and when that happens the false portion of the IF function will execute.
- Now, VLOOKUP(B13,$B$5:$C$10,2,0)
- Output: #N/A.
- This function looks for the value from cell B13 in the range of B5:C10. If there is a match, then it returns the value from the respective C5:C10 range as indicated by 2 inside the function. Moreover, the value “Ross” cannot be found in the specified cell range, hence it has shown the error. The 0 means at the end of this function means the match type is exact.
- Our formula reduces to → IF(ISNUMBER(#N/A),#N/A,””)
- Output: (Blank).
- The ISNUMBER function returns 0, which means false. So, the second portion of the IF function executes and we get the blank cell as the output.
Read More: Excel IFERROR Function to Return Blank Instead of 0
4. Combining IFERROR and VLOOKUP Functions
This section will combine the IFERROR and VLOOKUP functions to return a blank in Excel.
Steps:
- At first, type the following formula in cell C13.
=IFERROR(VLOOKUP(B13,$B$5:$C$10,2,FALSE),"")
- Next, press ENTER.
Formula Breakdown
- This formula has a single VLOOKUP function, and we have used this inside an IFERROR function, which returns modified output in the case of an error. Now, the modified output is set to a blank cell.
- Now, VLOOKUP(B13,$B$5:$C$10,2,FALSE)
- Output: 0.
- This function looks for the value from cell B13 in the range of B5:C10. If there is a match, then it returns the value from the respective C5:C10 range as indicated by 2 inside the function. The FALSE at the end of this function means the match type is exact.
- Our formula reduces to → IFERROR(#N/A,””)
- Output: (Blank).
- This function modifies any errors and returns us the blank cell as the output.
Read More: How to Exclude Zero Values with Formula in Excel (3 Easy Ways)
5. Using Combined Functions to Return Blank Instead of 0 or #N/A! Error
In the final method, we will combine the IF, IFNA, and VLOOKUP functions to create a formula to return blank instead of 0 or NA. Up to this point, every method was specific to a single value. However, this single formula will work for both conditions. Here, we have meant blank cell with the value 0.
Steps:
- At first, type the following formula in cell C13.
=IF(IFNA(VLOOKUP(B13,$B$5:$C$10,2,FALSE),0)=0,"",VLOOKUP(B13,$B$5:$C$10,2,FALSE))
- Next, press ENTER.
Formula Breakdown
- Again, this formula has two VLOOKUP functions. Moreover, we have used the first VLOOKUP function inside an IFNA function, which checks for the “#N/A” error. If it finds the error, then it will return 0. Otherwise, it will return the original output. However, we have set it so that when it finds 0, it will return a blank cell.
- Now, VLOOKUP(B13,$B$5:$C$10,2,FALSE)
- Output: 0.
- This function looks for the value from cell B13 in the range of B5:C10. If there is a match, then it returns the value from the respective C5:C10 range as indicated by 2 inside the function. The FALSE at the end of this function means the match type is exact.
- Our formula reduces to → IF(IFNA(0,0)=0,””,0)
- Output: (Blank).
- The IFNA function returns 0, which means the logical_test is true. So, the first portion of the IF function executes and we get the blank cell as the output.
Practice Section
We have added a practice dataset for each method in the Excel file. Therefore, you can follow along with our methods easily.
Conclusion
We have shown you five formulas to use VLOOKUP to return blank instead of 0 or NA in Excel. If you face any problems regarding these methods or have any feedback for me, feel free to comment below. Moreover, you can visit our site ExcelDemy for more Excel-related articles. Thank you for reading, and keep doing well!
