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

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 the match type is exact.
• Our formula reduces to → IF(0=0,””,0)
  • Output: (Blank).
  • Here the logical_test is true, so we have the blank output.

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

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

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 the blank cell as the output.

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.