How to Apply VLOOKUP to Return Blank Instead of 0 or NA

Get FREE Advanced Excel Exercises with Solutions!

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.

VLOOKUP Return Blank Instead of 0 or NA


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.

VLOOKUP Return Blank Instead of 0 or NA 2

  • Next, press ENTER.

VLOOKUP Return Blank Instead of 0 or NA 3

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

VLOOKUP Return Blank Instead of 0 or NA 4 1

  • Next, press ENTER.

VLOOKUP Return Blank Instead of 0 or NA 5 1

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


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

VLOOKUP Return Blank Instead of 0 or NA 6

  • Next, press ENTER.

VLOOKUP Return Blank Instead of 0 or NA 7

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

VLOOKUP Return Blank Instead of 0 or NA 8

  • Next, press ENTER.

Sample Dataset

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

Sample Dataset 2

  • Next, press ENTER.

Sample Dataset 3

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.

Practice Dataset


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!


Related Articles

Rafiul Haq

Rafiul Haq

Greetings! I am Rafiul, an experienced professional with an engineering degree and an MBA in finance. Data analysis is my passion, and I find Excel to be an invaluable tool in this domain. My goal is to simplify complex concepts by providing comprehensive and user-friendly guides on Excel and finance topics, aiming to enhance people's productivity and understanding at ExcelDemy.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo