XLOOKUP Returns 0? Return Blank Instead – Excel

Download Example Workbook

Download the example workbook

This tutorial will demonstrate how to return blank instead of zero when using the XLOOKUP Function. If your version of Excel does not support XLOOKUP, read how to use the VLOOKUP instead.

xlookup-return blank not zero Main

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

xlookup return blank not zero 01

This is also true when looking up values with VLOOKUP or XLOOKUP:

=XLOOKUP("2021-D",B3:B7,C3:C7)

xlookup return blank not zero 02

This can be very problematic in some scenarios.

XLOOKUP with IF and ISNA

In order to return a blank value instead of zero, we can add the IF and ISBLANK Functions to a formula:

=IF(ISBLANK(XLOOKUP(E3,B3:B7,C3:C7)),"",XLOOKUP(E3,B3:B7,C3:C7))

xlookup return blank not zero 03

 

Let’s walkthrough the formula:

XLOOKUP Function

First we have our original XLOOKUP Function:

=XLOOKUP("2021-D",B3:B7,C3:C7)

xlookup return blank not zero 05

 

ISBLANK Function

Next, we can use ISBLANK to check if the result of the XLOOKUP Function is blank.

=ISBLANK(XLOOKUP(E3,B3:B7,C3:C7))

xlookup return blank not zero 06

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 XLOOKUP 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 XLOOKUP if not blank) if the result is FALSE.

=IF(G3,"",F3)

xlookup return blank not zero 07

 

Combining all of these concepts results to our original formula:

=IF(ISBLANK(XLOOKUP(E3,B3:B7,C3:C7)),"",XLOOKUP(E3,B3:B7,C3:C7))

 

XLOOKUP with IF and Empty String (“”)

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

=IF(XLOOKUP(E3,B3:B7,C3:C7)="","",XLOOKUP(E3,B3:B7,C3:C7))

xlookup return blank not zero 08

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

 

XLOOKUP with IF and LEN

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

=IF(LEN(XLOOKUP(E3,B3:B7,C3:C7))=0,"",XLOOKUP(E3,B3:B7,C3:C7))

xlookup return blank not zero 09

 

Let’s dive deeper into this alternative solution:

Check If Blank using LEN

We can use the LEN Function to count the number of characters of the XLOOKUP’s output:

=LEN(XLOOKUP(E3,B3:B7,C3:C7))

xlookup return blank not zero 10

 

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:

=IF(G3=0,"",F3)

xlookup return blank not zero 11

 

Combining all of these concepts results to our original formula:

=IF(LEN(XLOOKUP(E3,B3:B7,C3:C7))=0,"",XLOOKUP(E3,B3:B7,C3:C7))

 

 

Take note that the formulas above are only applicable if the output is blank, which is different from a “no match” result. If XLOOKUP 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: “XLOOKUP – Fix #NA Error”