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 August 24, 2015)

28

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

Maintaining Text Formatting in a Lookup

Want to maintain the formatting used in one cell when you use formulas to reference that text in another cell? The answer is ...

Discover More

Converting to Word 6.0 Format

Need to convert your document to a really old format previously used in Word? The solution may already be built into the ...

Discover More

Finding Text in Text Boxes

Want to search for text that may appear in a text box rather than in a regular worksheet cell? You can only perform the ...

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)

Making VLOOKUP Case Sensitive

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

Discover More

Making VLOOKUP Trigger a Macro

VLOOKUP is an oft-used worksheet function to lookup values in a data table. If the function cannot return a value, it ...

Discover More

Referring to the Last Cell

It is not unusual to use worksheets to collect information over time. As you keep adding information to the worksheet, you ...

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 8Mpixels. 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 7 - 0?

2017-08-14 02:27:30

Scott

You could resolve N/A to 0 and then only test for 0 as follows

=IF(IFNA(VLOOKUP(A2,Table3,7,FALSE),0)=0,"",VLOOKUP(A2,Table3,7,FALSE))


2017-02-28 18:22:30

Adam

Thanks! the second solution where you added

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

was exactly what I was looking for.


2016-10-27 06:49:03

David

I am hoping someone can assist as I am unable to find a solution. I am able to show text in a cell where there is an error with the following:

=IFERROR(LOOKUP(G2,DATA!$B$3:$C$64),"Not Found")

However, I have not been able to get the cell to return a blank where no information is contained in the cell G2.

Any help/suggestions would be gratefully received.


2016-10-14 18:25:28

Jon

I am trying to make a data sheet that pulls dates from another sheet. When the other tab has no date in it i want it to stay blank and not put 1/1/1900. This is the formula i have now.

=VLOOKUP(A7,'Student Management'!A:G,5,FALSE)


2016-08-18 06:33:42

Michael (Micky) Avidan

@javi,
Would you be so king to explain what difference did you find and/or for what reason did you present your formula A FULL WEEK(!) after my suggestion ?

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

I do hope you wont suggest =B1+A1 after someone else have suggested: =A1+B1 (Believe me it is a bad habit)

--------------------------
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2016)
ISRAEL


2016-08-17 17:49:52

Colton

Javi, would you mind explaining the logic behind why that works?

Thanks!


2016-05-26 06:45:55

Ashley Soon

Thanks to Javi! His suggestion worked!


2016-05-19 11:39:27

javi

a way working for me is

=""&VLOOKUP(B1,D:E,2,0)


2016-04-12 08:11:59

Michael (Micky) Avidan

@Bob Hartmann,
To the best of my experience - you can use: =VLOOKUP(B1,D:E,2,)&""
for any returned value (Numeric or Text) except DATE.
For dates I, usually, use:
=IFERROR(1/(1/VLOOKUP(B1,D:E,2,)),"")
*** The IFERROR suits all Excel version since "2007".
For earlier versions use:
=IF(VLOOKUP(B1,D:E,2,),VLOOKUP(B1,D:E,2,),"")
--------------------------
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2016)
ISRAEL


2016-04-11 13:26:07

Bob Hartmann

I have used the "IF" variant for a long time. However, after looking at this tip, the fact that VLOOKUP returns a zero length for a blank cell gave me a new idea. If you are expecting a TEXT (string) value to be returned by the VLOOKUP, you can use CONCATENATE(VLOOKUP(...),"") to eliminate the zeros. Also, if you expect a numeric value and you don't care about zero values, just format the cells with a format like <positive format>;<negative format>;<empty>. This will also turn the zeros to blanks.


2016-03-07 16:57:18

Jack

Hello,
Have an issue with a blank cell. Example: C2 have to enter data, D2 contains following VLOOKUP ( =VLOOKUP(C2;'Sounding Tables'!$A$9:$E$361;3;FALSE)), cell E2 (=D2-D1). I am trying to work with "IF" in order keep cell E2 blank when cell C2 has no data entered or blank.
Somebody an idea to help me out?
Thanks in advance.


2016-02-04 14:21:41

Greg Mayer

Very Helpful Page of information.


2015-09-01 21:30:57

Ryan

Thank you for posting this help page!!


2015-07-15 06:51:24

samuel

Hello,

I need to find the first blank cell above specified one how can I make this without entering VBA

Thanks a lot
Samuel


2015-06-26 17:36:23

carlos

currently my formula is:
=vlookup(C10,sheet3!$A$2:$C$1572,2)

I'm receiving N/A's due to blanks in my column C. I think i'm confused as to how i'd create my formula to leave me blanks. CAn anyone please assist me with the formula i have above? Help anyone?


2015-04-23 01:19:19

Amol

& needless to mention this is blilliance.....


2015-04-23 01:07:51

Amol

Heartiest thanks mate & god bless you...!


2015-04-14 07:44:40

Nadeen

Huge thank you brilliant!


2015-02-19 12:57:40

blake

John Wu - that's brilliant, and means there is only 1 VLOOKUP performed, no matter what!


2014-12-29 19:31:57

TERRY MILLAR

As usual, the tips worked miracles. Thank you!!!


2014-12-18 14:17:42

Modern excel

Starting excel 2007 and on you can use =IFERROR(whatever,"") to get a blank in place of #NA and others. Enjoy


2014-12-03 06:21:45

Excel want to be wizard

Like that idea John Wu thanks


2014-06-26 00:08:04

Luke Stevens

<blockquote>
Only if the length is not 0 is the actual VLOOKUP performed.
</blockquote>

Surely this isn't right! Excel has to do one lookup to establish whether the looked up value is blank or not. If it isn't, won't Excel do the lookup again to return the value?

So Excel will do either 1 lookup or 2, not 0 or 1.

Maybe Excel is clever enough to cache somehow the result of the first lookup to reuse for the false condition of the if. Maybe it isn't...


2014-01-15 23:24:00

Jacky

Re john, brilliant!


2013-09-10 09:44:32

John Wu

I will use the formula =VLOOKUP(B1,D:E,2,0) & "" to handle the blank value issue


2013-06-26 19:32:01

Grumpy Excel User

Munish Kumar. Ask nicely.


2013-04-26 11:07:41

new excel learner

Thanks the =IF(VLOOKUP(B1,D:E,2)="","",VLOOKUP(B1,D:E,2))
was very useful


2013-03-30 04:04:22

Munish Kumar

Kindly send us excel formula (if and len).


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.