Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. 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.

Returning Blanks with VLOOKUP

by Allen Wyatt
(last updated June 27, 2016)

39

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 (12518) applies to Microsoft Excel 2007, 2010, and 2013. You can find a version of this tip for the older menu interface of Excel 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

Strip Trailing Spaces

If you get tired of documents that always seem to have extra spaces at the end of lines, here's a quick way to get rid of ...

Discover More

Fonts Missing in Word

What are you to do if you find that you have no fonts available in Word, but they are available in other programs? There ...

Discover More

Requiring Input

If you distribute a workbook that is used by others for data entry, you may want a way to make sure they fill in certain ...

Discover More

Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!

More ExcelTips (ribbon)

Finding the Date Associated with a Negative Value

When working with data taken from the real world, you often have to determine which certain conditions were met, such as when ...

Discover More

Finding the Nth Root of a Number

Finding a square root is easy because Excel provides a worksheet function for that purpose. Finding a different root may not ...

Discover More

Phantom Counts

Two common worksheet functions used to count things are COUNT and COUNTA. Not understanding how these functions treat cell ...

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 6 - 4?

2017-04-21 15:30:51

Arik

When I clear the data in my sheet, VLOOKUP formula is disappearing...

I want the formula to remain, when I clear data or close the excel and reopen it.

Please help how to achieve this???


2017-02-15 09:18:29

Mustapha

I use the following code in order to get the value of cell according to changes in other column cells ..
my idea is to decrease number of goods at the store according the number soled at invoice side .. also the number is attached with the name if the item ..

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Application.Intersect(Sheet1.Range("C14:C29"), Range(Target.Address)) Is Nothing Then
On Error Resume Next
MsgBox Target.Value
LookedCell = Application.WorksheetFunction.VLookup(Target.Value, Sheet1.Range("B14:B29"), 1)
MsgBox LookedCell
For i = 1 To 200
If Sheet2.Range("B5:B200").Cells(i, 2).Value = LookedCell Then
Sheet2.Range("D5:D200").Cells(i, 4).Value = Sheet2.Range("D5:D200").Cells(i, 4).Value + Target.Value
End If
Next i
End If


2016-11-23 07:55:24

Melvin

this is great help....


2016-10-28 05:55:34

Trevor

@Kit: always a fan of SUMPRODUCT. For example, A1:A4 contain "text numbers", 1, 2, 3, 4 (all with single quote to make them text)

=SUM(A1:A4) = 0
=A1+A2+A3+A4 = 10
=SUMPRODUCT(A1:A4+0) = 10
=SUMPRODUCT(A1:A4*1) = 10
=SUMPRODUCT(--(A1:A4)) = 10

Note that ALL the last four formulae will fail with a #VALUE! error if there are alpha characters in the range. A blank cell is OK though, but a space character is not.


2016-10-28 05:45:53

Michael (Micky) Avidan

@Trevo & Kit,
If you are aware that the result is not 0 - then try:
=SUM(--(A1:A10000))
OR:
=SUM(1*(A1:A10000))
*** Both "Array Formulas" (Confirm with: CTRL+SHIFT+ENTER)
* If you are not certain, than use this (it is only 2 more characters).
--------------------------
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2017)
ISRAEL


2016-10-27 07:52:11

Kit

@Trevor:

Thanks for the info on the Sum vs. A1 + A2! I didn't know that. Do you have a solution if there is a column of #'s for which you would normally use the SUM, as the A1 + Ax... would be too cumbersome?

Thanks for the info!


2016-10-27 07:30:36

Trevor

@Michael (Micky) Avidan

Sure, you can, but equally lots of functions will fail, or error, if they are expecting numbers and get text that looks like numbers.

So long as people are aware of the side effects of any solution, and can work around them, then it's not an issue.

If you use =SUM(A1:A2) and A1 and A2 contain textual numbers, it will return zero. If you use =A1+A2, it will correctly sum the values.


2016-10-26 08:43:15

Michael (Micky) Avidan

@Trevor,
I agrre that they will all be converted to text values - but I don't see any problem because you can make all kind of caculations even on a number which is a "Text value".
--------------------------
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2017)
ISRAEL


2016-10-26 08:37:38

Michael (Micky) Avidan

@Thomson,
Just a small comment.
For your (and others) information - IFERROR does not exist in Excel version prior to "2007".
--------------------------
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2017)
ISRAEL


2016-10-26 03:23:03

Kelvin Finapiri

I have just used your tip to return a blank vlookup search and it was greatly helpful, thank you and I am looking forward to more useful tips to enable me do my job.


2016-06-28 07:04:53

Trevor

"If you add &"" after Vlookup than it will not return 0 for blank value."

No, but if the VLOOKUP should be returning numeric values, they will all be converted to text values.

Horses for courses


2016-06-27 19:23:55

Thomson

It is another simple formula you can use, I got it from the web a while ago.

=Iferror(VLOOKUP("A",D80:E80,2,0)&"","")

If you add &"" after Vlookup than it will not return 0 for blank value.


2016-06-27 12:35:37

Stephen

How do you get Excel to return a BLANK instead of an empty cell.
I have a worksheet that does not accept an empty text, only a number or a blank cell.


2016-01-30 05:59:20

Trevor@ExcelAid.co.uk

Did you read my reply? If it finds a match but returns a zero, it's not an error. Excel, for some reason, returns a zero when it finds a blank. Zero, formatted as a date is what is being seen here.

IFERROR was only introduced in Excel 2007. Before that, you had to test using ISERROR or ISNA.


2016-01-29 07:29:08

Kit

What's the difference btw using IFERROR and IF(ISERROR...?

Thanks for all of your awesome help, guys!


2016-01-29 06:47:07

Michael (Micky) Avidan

@Francine Palermo,
All you need is a "little" suffix to your formula.
Try: =IF(ISERROR(VLOOKUP(D59,'DPR'!A:DX,13,FALSE)),"",VLOOKUP(D59,'DPR'!A:DX,13,FALSE))&""
*** I changed the: Delivery Portfolio Reporting, into: DPR just to shorten the formula for better understanding.
--------------------------
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2016)
ISRAEL


2016-01-28 06:27:46

Trevor@ExcelAid.co.uk

Well, first, it's not an error, and it's not really strange.

The VLOOKUP is finding a matching entry and returning the corresponding value which, in this case is a blank/null value. When that happens, for reasons best known to the Excel developers, it returns a zero, rather than a blank. BUT ... you can test if the value returned is actually a blank and take appropriate action.

There are various approaches, some more efficient than others.

You can "double up" the formula, as you already have, to check for the blank:

=IF(IF(ISERROR(VLOOKUP(D59,'Delivery Portfolio Reporting'!A:DX,13,FALSE)),"",VLOOKUP(D59,'Delivery Portfolio Reporting'!A:DX,13,FALSE))="","",IF(ISERROR(VLOOKUP(D59,'Delivery Portfolio Reporting'!A:DX,13,FALSE)),"",VLOOKUP(D59,'Delivery Portfolio Reporting'!A:DX,13,FALSE)))

Or, you could use COUNTIF to see if there is a matching entry rather than using ISERROR:

=IF(COUNTIF('Delivery Portfolio Reporting'!A:A,D59)=0,"",IF(VLOOKUP(D59,'Delivery Portfolio Reporting'!A:DX,13,FALSE)="","",VLOOKUP(D59,'Delivery Portfolio Reporting'!A:DX,13,FALSE)))

You can still double up but move the ISERROR outside the duplicated formula:

=IF(ISERROR(IF(VLOOKUP(D59,'Delivery Portfolio Reporting'!A:DX,13,FALSE)="","",VLOOKUP(D59,'Delivery Portfolio Reporting'!A:DX,13,FALSE))),"",IF(VLOOKUP(D59,'Delivery Portfolio Reporting'!A:DX,13,FALSE)="","",VLOOKUP(D59,'Delivery Portfolio Reporting'!A:DX,13,FALSE)))

But, if you are using Excel 2007 and above, you can use IFERROR rather than IF(ISERROR(...)):

=IFERROR(IF(VLOOKUP(D59,'Delivery Portfolio Reporting'!A:DX,13,FALSE)="","",VLOOKUP(D59,'Delivery Portfolio Reporting'!A:DX,13,FALSE)),"")

Your choice :)

Regards, Trevor


2016-01-27 16:05:55

Francine Palermo

I'm hoping someone can help me with this one.

I have several very large data tables that return a date. In the cells where there is not date entered yet, I get a strange date: 1/0/1900. I think this is because the cell is formatted for a date (which I need formatted as a date).

What can I do to stop this from happening. Here is my vlookup formula:

=IF(ISERROR(VLOOKUP(D59,'Delivery Portfolio Reporting'!A:DX,13,FALSE)),"",VLOOKUP(D59,'Delivery Portfolio Reporting'!A:DX,13,FALSE))

Thank you!!


2015-10-20 06:31:04

Michael (Micky) Avidan

@Kit,
That will present a ZERO in ALL(!) kind of ERRORS including the N/A.
---------------------------
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2016)
ISRAEL


2015-10-19 07:34:14

Kit

@Micky, What would that give you?


2015-10-17 06:31:05

Michael (Micky) Avidan

@kit,
I would prefer the following formula - during error handling especially when the average end user can hardly anticipate the kind of error a formula will return:

=IFERROR(VLOOKUP(A2,H:H,1,),)

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


2015-10-16 07:32:32

Kit

If you are doing a VLOOKUP on numbers and need a zero in place of an N/A error, use =IFNA(VLOOKUP(A2,H:H,1,FALSE),0)


2015-10-16 04:41:29

TREVOR DAHL

=IF(IFERROR(VLOOKUP(B5,'New Description'!$B:$B,1,FALSE),0)=0,IFERROR(VLOOKUP(B5,Alternative!$A:$C,3,FALSE),0),IFERROR(VLOOKUP(B5,'New Description'!$B:$B,1,FALSE)

Check this out what if the situation is you didn't found the string that you want to match with the data. You better need 2 data to find one.


2015-09-25 06:02:52

Trevor

@Teresa: you refer to Sheets #1 and #2 but you I'm not sure which has the VLOOKUP formula or which is "Prospect Accounts".

Whatever, I suspect that this: =IFERROR(VLOOKUP('Prospect Accounts'!A2,'Prospect Accounts'!A:B,2,FALSE), "")

Needs to be this: =IFERROR(VLOOKUP(A2,'Prospect Accounts'!A:B,2,FALSE), "")

Regards


2015-09-24 11:40:25

Teresa Whelan

I am having a really hard time with VLOOKUP and have tried the formulas for returning a blank or 0 if the lookup does not match. The scenario is that I have an ID number and company name on worksheet #1 in columns 1 and 2 respectively (my lookup table); I have the same ID number on worksheet #2 but need to match it with the company name from worksheet #1. So my formula is as follows: =IFERROR(VLOOKUP('Prospect Accounts'!A2,'Prospect Accounts'!A:B,2,FALSE), "")
Unfortunately, it is returning the same list of company names as in column 2 on worksheet #1 - not matching the ID's at all. HELP!!!!!!


2015-08-18 17:48:37

simon

stupid microsoft


2014-10-14 11:23:35

Victor Perez

Thank you Don! Just what I needed for my formula. This really worked:

=IFERROR(TRIM(VLOOKUP(B1,H:H,1,FALSE)),"")


2014-10-14 11:23:32

Victor Perez

Thank you Don! Just what I needed for my formula. This really worked:

=IFERROR(TRIM(VLOOKUP(B1,H:H,1,FALSE)),"")


2014-02-14 12:29:38

Cindy

Please disregard my last comment.
It is working now. I used Don's sample:

=IFERROR(TRIM(VLOOKUP(B1,H:H,1,FALSE)),"")


Thank you so much.


2014-02-13 19:50:21

Cindy

Hi. I tried this :
=IF(ISNA(VLOOKUP(B1,D:E,2,0))+(VLOOKUP(B1,D:E,2,0)=""),"",VLOOKUP(B1,D:E,2,0))

for my EXCEL 2010 and it is not working for if #N/A is true. Still returned #N/A. Also I tried TRIM, which is on your March 9 comment and not working too. Anyway you can help me?

Thank you so kind.


2013-07-28 06:56:28

Brian Hershman

K-Li-Ch,
If you read the earlier correspondence, you will see why your formula is not working, how much extra time it takes to achieve nothing, and what much simpler formula will work
Try my formula of 9 March - wth only one VLOOKUP!


2013-07-27 22:26:47

K-Li-Ch

The statement

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

isn't working for me: when ISNA is true , IF result is #N/A, not "".

Am I missing something?

Thanks.


2013-03-15 16:44:42

Don

Correction to the last line of my previous submission. That format string should be

_(* #,##0_);_(* (#,##0);_(* ""_);_(@_)


2013-03-13 13:55:42

Don

Kudos to Brian's TRIM(Vlookup(...)). I'm guilty of writing some of the longest formulas I've seen. My way of dealing with this has been to check the length of the result, after checking is there is an error. The Vlookup is in there at least 3 times...because much was written before the IFERROR function was available.

Here's something built on Brian's idea and taking advantage of the IFERROR function
=IFERROR(TRIM(VLOOKUP(B1,H:H,1,FALSE)),"")

An option that means no change to the formulas that return 0s would be to apply a Custom number format to the cells that displays nothing if the value is 0. The 0 is still there, it just looks like it isn't. Here's one I have in one of my spreadsheets:

_(* #,##0_);_(* (#,##0);_(* "-"_);_(@_)


2013-03-11 07:43:30

KT

awesome tips!


2013-03-10 10:00:52

Trevor shuttleworth

The problem is that, if the lookup item is found but the cell/value to be returned is blank, the VLOOKUP will return 0. Hence the reason for wanting to differentiate between a true zero and a blank cell being returned as zero.

If the column which contains the values to be returned is always alphanumeric or blank, you can use the formula:

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

This will coerce a text response and give you a blank if the cell is blank.

Regards, Trevor


2013-03-09 09:51:49

Brian Hershman

Second thoughts - much easier solution:
= TRIM( Vlookup(......))
If VLOOKUP actually returns either an empty or a blank value, TRIM will display it as EMPTY!


2013-03-09 09:00:21

Brian Hershman

Yes, I agree with Trevor. The VLOOKUP has to be evaluated twice in order to get the (hopefully) real value, if any. This means that the more cells that contain the VLOOKUP formula,the more time the duplicated formula will take. Also the formula itself is considerably longer and more difficult to read.

The time-saving (but uglier) alternative is to put the VLOOKUP formula into an additional auxiliary/hidden cell/column and to have the IF formula applied to the auxiliary result.

What would be ideal would be a function like IFERROR which would evaluate VLOOKUP once only and return a user-specified constant if the the value found is empty or blank or....

Such a function macro is very simple to write. VBA can use worksheetfunction.vlookup, so it requires only 2 VBA statements for the whole macro.


2013-03-09 05:10:30

Trevor Shuttleworth

I'm not sure I follow the statement: "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."

Surely, it has to do the VLOOKUP to know what has been returned in order to determine its length. Then it decides whether to display it or not ... using the repeated VLOOKUP function.

Personally, I prefer the second option.

Regards, Trevor


This Site

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.

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.