 Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Returning Blanks with VLOOKUP.

# Returning Blanks with VLOOKUP by Allen Wyatt
(last updated June 2, 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:

```=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 Excel doesn't actually do a lookup—it forces a blank to be returned. Only if the length is not 0 is the actual VLOOKUP performed.

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. This variation, for example, 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 #N/A error:

```=IF(ISNA(VLOOKUP(B1,D:E,2,0))+(VLOOKUP(B1,D:E,2,0)="")
,"",VLOOKUP(B1,D:E,2,0))
```

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (3075) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Returning Blanks with VLOOKUP.

##### Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

##### MORE FROM ALLEN

Tools to View Field Codes

Fields can be used to add all sorts of dynamic data to your documents. Viewing the field codes, at times, is desirable. ...

Discover More

Finding All Instances of a Value

Searching for information in an Excel worksheet generally goes very smoothly. There can be times, however, when the ...

Discover More

Pasting Clean Text

One of the most helpful tools in Word is the ability to paste straight text into a document. This is used so much on my ...

Discover More Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!

Using the COLUMN Function

Need to know the column number for use in a formula? The worksheet function you want is the COLUMN function, described in ...

Discover More

Understanding the VLOOKUP Function

Functions are at the heart of Excel's power in working with data. One of the most misunderstood functions provided by ...

Discover More

Making VLOOKUP Case Sensitive

The VLOOKUP function, like other lookup functions in Excel, is not case sensitive. In other words, it doesn't matter ...

Discover More
##### Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 3 - 3?

2021-04-24 08:34:25

ROY

If you want to know if ANY cell at all contains a "True NULL" (a real blank, not a formatted value that looks blank, or any other variation on "things that look like blanks but are not actually blank, including formulas that returned what looked blank, then were copied and pasted as that value), then VLOOKUP() has a feature that will tell you.

If you do a VLOOKUP() on the cell itself, returning the cell, and the cell has a blank, it returns an error. For example:

=VLOOKUP(A1,A1,1,FALSE)

returns #N/A. It returns the actual content (better described as "content" for some variations on the "blank" scheme) for ANYTHING other than a real blank, or an error value in the lookup cell. So if you apply that to the returned value that you want to know about, testing first for a couple ways that errors would interfere with the interim result, you can learn exactly, no ifs, ands, or buts, whether you have an actual blank or just something that looks like, or that has been made to look like, a blank.

For instance, say you have a table of data in the range A1:B10. You have a lookup value in D1. You have a lookup formula in E1. If you first test for the lookup value being, in fact, an error itself, then for the lookup value (now known to not be itself an error) not being present in the index column (so there is no match to be had at all), both of these tests checking for errors that would mislead later, and the two tests pass, you may then test the lookup formula's return value against itself, so to speak, and if it passes, it is anything other than a real blank. If that test fails, the return value is a real blank. So, the following:

=IF(ISERROR(D1), "Lookup value is an error",
IF(ISERROR(VLOOKUP(D1,A1:B10,2,FALSE)), "No result found",
IFERROR(VLOOKUP( VLOOKUP(D1,A1:B10,2,FALSE), INDIRECT(ADDRESS( MATCH(D1,A1:A10,0),2) ),1,FALSE), "True blank")))

The first lines tests that the lookup value is not itself an error. If not, the second line tests that the lookup value can be found at all. If it can, the third line comes into play.

The third line uses a VLOOKUP() {the "inner" VLOOKUP()} to do the search one expects. This obtains the result looked for which is then used in the rest of the third line. It is the input lookup value to the "outer" VLOOKUP(). The range for the outer VLOOKUP() is the part beginning with INDIRECT(). The guts are a MATCH() to find the lookup value (the result the "inner" VLOOKUP() found) in the index column of the table. You must use MATCH() rather than XMATCH() because there are some subtle differences between them. MATCH() very exactly looks things up the same way VLOOKUP() does so it can emulate it just about perfectly while XMATCH() is a wee bit more precise and so does NOT properly emulate VLOOKUP(). You want to not risk introducing mismatching here (!) so use MATCH(), not XMATCH(). That matchup finds the row the result you seek is on and you know the column from however you knew it for you VLOOKUP(). So ADDRESS() can use those two things to make a valid address (without any arcane conversions from column NUMBER to column LETTER!) and INDIRECT() will consider it a real cell address and return the value in it.

So now the outer VLOOKUP() is looking up the result the inner one returned in an array of... itself. If it is anything at all besides a real blank, it then returns whatever that is. If, however, it is a TRUE BLANK, it returns an error which the IFERROR() turns into a note about it being a true blank.

Naturally, you would substitute formula elements to handle that fact in the manner of your choice for my simple text string reporting "True blank" and all are happy.

Of course, I am in no way certain that this is completely infallible, or closer to infallible, than Mr. Wyatt's LEN() formula. In no way more certain at all. That formula is vastly easier to type... and with fewer moving parts, FAR less prone to human errors. So one ought to use IT, not this, unless one found it to fail in one's circumstance and this one to work in the same circumstance.

The main difference, conceptually, is that this seems to directly test for a NULL, while the Tip's formula is an indirect test.

2021-03-11 16:44:45

Gaultz

There is a simple solution without having to do an IF.

Use "General;General;;General" as a custom format

1. Ctrl-1 (Format Cells)
2. Number tab > Custom
3. General;General;;General

Enjoy!

2020-08-25 04:51:01

Murali Deepala

Thank you.

2020-04-25 18:12:35

samuel

I have a sheet, which is maintained every month, it has the following formula =IF(AA13=0,0,VLOOKUP(Z13,Repayments!\$B\$2:\$AB\$4800,22,FALSE)), the purpose of this is to return the value of repayments, and return blanks in the event there is no repayment. It is working fine on an earlier version (i.e. last two weeks). But for the new version of the same sheet it keeps returning 0 value for where it should provide a blank. Any tips?

2020-01-16 08:47:18

Martha S

Thanks the file looks a lot cleaner and you saved me lots of time!

2019-10-25 11:38:32

Aaron

Thank you! This is brilliant. It took me quite awhile to figure out how to use VLOOKUP to solve my business problem, and lo and behold, my beautiful formula returned these pesky 0's in all the blank spaces. Your solution has saved me a lot of troubleshooting. Thanks again.

2019-08-05 12:40:41

Bill C

I use a CONCAT to remove the zeros. Also, wrapping everything in an IFERROR removes the #N/A error when there is no VLOOKUP match.

=IFERROR(CONCAT(VLOOKUP(C2,'11g'!\$B\$2:\$P\$347,3,FALSE),""),"")

2019-07-30 08:50:05

Ellybee

Or even easier - change the setting in the worksheet!

1. Click the File menu and then choose Options at the bottom of the pane ...
2. Choose Advanced in the left pane. ...
3. In the Display Options For This Worksheet section, uncheck the Show A Zero In Cells That Have Zero Value.
4. Click OK.

2019-05-13 18:32:02

Richard

Thank you, saved me a lot of time

2019-03-26 03:11:29

Abdias Michael

An easier way to check for empty cells :

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

2019-03-21 11:10:24

David Kelly

How do i create this formula to look through multiple worksheets?

2019-01-31 12:08:19

Michael

I use this formula to get rid of 0s:

=IF(VLOOKUP(A8,Resources!A:B,2,FALSE)=0,"", VLOOKUP(A8,Resources!A:B,2,FALSE))

If the VLOOKUP equals 0, the cell is blank, otherwise it is the actual VLOOKUP value.

2018-10-17 16:54:25

SS

I ended up just using the following in place of the above and it worked great.

=IF(OR(ISBLANK(VLOOKUP(\$A31,'Sheet 5'!\$A:\$D,3,0)),ISNA(VLOOKUP(\$A31,'Sheet 5'!\$A:\$D,3,0))),"",VLOOKUP(\$A31,'Sheet 5'!\$A:\$D,3,0))

2018-10-11 13:15:13

SS

I am using Excel 2013 and the IF(ISNA) formula you provided is not working. I have had many version of Excel in the past and it seems the formulas I used on an older version never work on a newer version. Any suggestions?

=IF(ISNA(VLOOKUP(\$A31,'SHEET 5'!\$A:\$D,3,0))+(VLOOKUP(\$A31,'SHEET 5'!\$A:\$D,3,0)=""),"",VLOOKUP(\$A31,'SHEET 5'!\$A:\$D,3,0))

##### This Site

Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.