Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and 2021. 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.
Written by Allen Wyatt (last updated September 7, 2021)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and 2021
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:
=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))
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, and 2021. You can find a version of this tip for the older menu interface of Excel here: Returning Blanks with VLOOKUP.
Program Successfully in Excel! This guide will provide you with all the information you need to automate any task in Excel and save time and effort. Learn how to extend Excel's functionality with VBA to create solutions not possible with the standard features. Includes latest information for Excel 2024 and Microsoft 365. Check out Mastering Excel VBA Programming today!
The LET function provides an easy way to define and use variables within a formula. This tip shows you how you can start ...
Discover MoreWhen you need to count a number of cells based upon a single criteria, the standard function to use is COUNTIF. This tip ...
Discover MoreNeed to know the column number for use in a formula? The worksheet function you want is the COLUMN function, described in ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)
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.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2026 Sharon Parq Associates, Inc.
Please Note:
This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and 2021. 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:
Comments