Return to Excel Formulas List

VLOOKUP Returns 0? Return Blank Instead – Excel & Google Sheets

Download Example Workbook

Download the example workbook

This tutorial will demonstrate how to return blank if VLOOKUP’s output is blank. If your version of Excel supports XLOOKUP, we recommend using XLOOKUP instead.

vlookup return blank not zero Main

In Excel, when referring to another cell with a formula, blank values are calculated as zero.

vlookup return blank not zero 01

This can be a problem for some VLOOKUP scenarios where we need to distinguish blanks from zeroes:

vlookup return blank not zero 02

VLOOKUP with IF and ISNA

We can use the combination of VLOOKUP with IF and ISNA to solve this problem:

vlookup return blank not zero 03

Let’s breakdown and analyze the formula:

To return blank if the VLOOKUP output is blank, we need two things:

  1. A method to check if the output of the VLOOKUP is blank
  2. And a function that can replace zero with an empty string (“”), which is the closest to blank that we can return.

 

ISBLANK Function

We can use ISBLANK to check if the result of VLOOKUP is blank.

vlookup return blank not zero 04

Notes:

  1. The ISBLANK Function returns TRUE if a value is blank. Empty string (“”) and 0 are not equivalent to a blank. A cell containing a formula is not blank, and that’s why we can’t use F3 as input for the ISBLANK.
  2. Formulas can return blanks, but they are converted to zero at the final stage of the calculation.
  3. Therefore, we can nest formulas in ISBLANK to check if their results are blanks rather than assigning the formulas to cells before applying ISBLANK to the cells.

 

IF Function

We can then use the IF Function to test whether the combination of ISBLANK and VLOOKUP is TRUE or FALSE. We can set any process (e.g., output an empty string) if the condition is TRUE and another one (e.g., the result of the VLOOKUP if not blank) if the result is FALSE.

vlookup return blank not zero 05

Combining all of these concepts results to our original formula:

 

VLOOKUP with IF and Empty String (“”)

We can use the empty string as a criterion to check if the value of the VLOOKUP is blank instead of using the ISBLANK Function:

vlookup return blank not zero 06

Note: Blank can be equivalent to zero or empty string depending on the calculation, but empty strings and 0s are not blanks.

 

VLOOKUP with IF and LEN

Another alternative to ISBLANK is the by using the LEN Function:

vlookup return blank not zero 07

Let’s dive deeper into this alternative solution:

Check If Blank using LEN

As stated earlier, formulas do return blank results but are converted at the end of the calculations.

Therefore, we can use the LEN Function to count the number of characters of the VLOOKUP’s output:

vlookup return blank not zero 08

 

If the number of characters is 0, this means that the value is blank. We can then use the IF Function to check if the LEN Function is equal to 0, and return an empty string if this is true:

vlookup return blank not zero 09

 

Combining all of these concepts results to our original formula:

 

VLOOKUP Return Blank in Google Sheets

All aforementioned formulas work the same way in Google Sheets, and in fact, we don’t need to implement them in Google Sheets to display a blank-like result because Google Sheets can return blanks.

vlookup return blank not zero GSheet

Note: This is very helpful in scenarios where we need to distinguish blanks from empty strings.

Take note that the formulas above are only applicable if the output is blank, which is different from a “no match” result. If VLOOKUP doesn’t find a match, it will return the #N/A Error instead, and in this case, we need to handle the #N/A Error; please see the article: “VLOOKUP – Fix #NA Error”