**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.

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**.

**Program Successfully in Excel!** John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out *Excel 2013 Power Programming with VBA* today!

The DSUM function is very handy when you need to calculate a sum based on data that matches criteria you specify. If you ...

Discover MoreNeed to find the absolute value of a number? That's where the ABS function comes into play.

Discover MoreThe INT function allows you to convert a value to an integer. The effect the function has depends on the characteristics of ...

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

2017-04-21 15:30:51

Arik

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

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

=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

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

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

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

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

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

2016-06-28 07:04:53

Trevor

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

=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

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

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

2016-01-29 07:29:08

Kit

Thanks for all of your awesome help, guys!

2016-01-29 06:47:07

Michael (Micky) Avidan

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

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 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

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

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

2015-10-16 04:41:29

TREVOR DAHL

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

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

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

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

2014-10-14 11:23:32

Victor Perez

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

2014-02-14 12:29:38

Cindy

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

=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

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

=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

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

2013-03-13 13:55:42

Don

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

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

= 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

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

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

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 © 2017 Sharon Parq Associates, Inc.

## Comments