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)

14

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

Beginning a Mail Merge

Performing a mail merge can be intimidating to some people. It needn't be; Word provides step-by-step wizards that lead ...

Discover More

Defining Protected Sections as AutoText

AutoText is a very useful tool. But what if the text you want to use when creating an AutoText entry is protected and you ...

Discover More

Replacing Dashes with Periods

Replacing one character in a text value with another character is easy. All you need to do is use the SUBSTITUTE ...

Discover More

Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!

More ExcelTips (menu)

Using VLOOKUP to Access Information to the Left

One of the most useful function in Excel is VLOOKUP. One thing it won't do, however, is allow you to lookup information ...

Discover More

Looking Up Names when Key Values are Identical

Need to look up some values based upon some key items that may be identical to each other? Depending on the ...

Discover More

Returning Blanks or Asterisks from a Lookup

Want to return more than a value when doing a lookup? Here's one way to do it by adding an IF clause to your formula.

Discover More
Subscribe

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

View most recent newsletter.

Comments

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}] 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 three less than 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.

Newest Tips
Subscribe

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

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.