Returning Blanks with VLOOKUP

When you use VLOOKUP to return a value from a data table, the function does not differentiate between blanks and zero values in what it returns. If the source value is zero, then VLOOKUP returns 0. Likewise, if the source is blank, then VLOOKUP still returns the value 0. For some purposes, this may not do—you need to know whether the cell being looked up is blank or if it really contains a 0.

There are many different solutions that could be pursued. For users of Excel 2019, Excel 2021, Excel 2024, or Excel in Microsoft 365, the best practice is to use the XLOOKUP function, which handles blank cells correctly without any workarounds. For those using earlier versions of Excel, there are several ways to work around this VLOOKUP behavior.

Use XLOOKUP Instead

If you are using a recent version of Excel, the XLOOKUP function is the recommended solution. It is more powerful and flexible than VLOOKUP and, for the purpose of this tip, it correctly returns a blank when the source cell is blank.

A standard XLOOKUP formula would look like this:

=XLOOKUP(B1,D:D,E:E)

This formula looks for the value in cell B1 within column D and returns the corresponding value from column E. Unlike VLOOKUP, if the corresponding cell in column E is empty, XLOOKUP will return an empty cell, not a zero.

Traditional Solutions for VLOOKUP

If you need to maintain compatibility with older versions of Excel, there are several ways to force VLOOKUP to return a blank when the source cell is empty.

One solution relies on the fact that even though VLOOKUP returns a 0 for a blank cell, it will correctly report the length of the source cell. If you use the LEN function on what is returned, an empty source cell results in a length of 0, while a source cell containing a 0 results in a length of 1. This means you could use the following formula:

=IF(LEN(VLOOKUP(B1,D:E,2,0))=0,"",VLOOKUP(B1,D:E,2,0))

In this case, if the length of what VLOOKUP returns is 0, then the formula returns a blank. Only if the length is not 0 is the result of the VLOOKUP returned.

There are other variations on this same concept, each testing a different characteristic of the data being referenced and then making the decision as to whether to actually look up that data. (As you can surmise, the variation you develop for your needs will depend on the "different characteristics of the data being referenced.")

Here's a variation, for example, that directly tests to see if the source is blank:

=IF(VLOOKUP(B1,D:E,2)="","",VLOOKUP(B1,D:E,2))

A common shortcut to force a blank return is to append an empty string to the end of the formula, like this:

=VLOOKUP(B1,D:E,2,0)&""

While this works, it is important to understand that this method converts all returned values—including real numbers—into text. This can cause problems in later calculations. For this reason, the IF or LEN methods are generally preferred if you need to preserve the numeric data type.

The formula can also be modified to check the source cell for multiple conditions. For instance, this variation returns a blank if the source is blank or if the source contains an error value (such as #N/A):

=IFERROR(TRIM(VLOOKUP(B1,H:H,1,FALSE)),"")

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (12518) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, 2021, 2024, and Excel in Microsoft 365. You can find a version of this tip for the older menu interface of Excel here: Returning Blanks with VLOOKUP.