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.

If you are using a newer version of Excel (Excel 2021 or later), you might think that using XLOOKUP could provide a solution. It doesn't, unfortunately. Just like VLOOKUP, XLOOKUP doesn't discriminate between blanks and zero values in what it returns. The reason that VLOOKUP and XLOOKUP don't discriminate is that both functions are designed to return numeric values and a blank is not a numeric value.

This is probably a good time to mention that it can get a little confusing in talking about "blanks." Technically we are talking about three different types of data here. If a cell is empty, then it is just that—empty, meaning that it contains nothing. (I know, "empty" is not a data type. It is simply a condition of a cell.) The problem is that functions and formulas cannot return nothing, they must return something. As already mentioned, the VLOOKUP function is designed to return numeric values. The source being accessed by VLOOKUP can be evaluated and if it is empty, then a blank can be returned, but a "blank" isn't the same as "empty." A blank is a string that contains nothing, meaning it is a text value.

One evaluation 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,FALSE))=0,"",VLOOKUP(B1,D:E,2,FALSE))

In this case, if the length of what VLOOKUP returns is 0, then the formula returns a blank string. 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,FALSE)="","",VLOOKUP(B1,D:E,2,FALSE))

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

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

While these last two formulas work, it is important to understand that they convert 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 for returned numbers.

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.