Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, and 2016. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Returning Blanks with VLOOKUP.
by Allen Wyatt
(last updated January 20, 2018)
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. One solution relies on the fact that even though VLOOKUP returns a 0, it will correctly report the length of the source cell. Thus, if you use the LEN function on what is returned, if the source cell is empty the LEN function returns 0, but if the source contains a 0 then LEN returns 1 (the 0 value is 1 character in length). This means that you could use the following formula in place of a standard VLOOKUP:
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:
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):
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (12518) applies to Microsoft Excel 2007, 2010, 2013, and 2016. You can find a version of this tip for the older menu interface of Excel here: Returning Blanks with VLOOKUP.
Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!
Looking for a great reference that you can use to help figure out the various worksheet functions available in Excel? ...Discover More
If you need to count the number of blank cells in a range, the function to use is COUNTBLANK. This tip discusses the ...Discover More
Two common worksheet functions used to count things are COUNT and COUNTA. Not understanding how these functions treat ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.