Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, and 2016. 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 January 18, 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 the formula returns a blank. Only if the length is not 0 is the result of the VLOOKUP returned.

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. (As you can surmise, the variation you develop for your needs will depend on the "different characteristics of the data being referenced.")

Here's a variation, for example, that 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 error value (such as #N/A):

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

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (12518) applies to Microsoft Excel 2007, 2010, 2013, and 2016. 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

Hiding Excel in VBA

Want to have you macro completely hide the Excel interface? You can do so by using the Visible property for the Excel ...

Discover More

Generating a Table of Authorities

With the entries for your table of authorities marked throughout your document, you are ready to actually generate the table. ...

Discover More

Date Last Edited

When adding headers or footers to your worksheets, you may want to include the date that the workbook was last edited. Excel ...

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)

Median of Selected Numbers

Need to find a median value in a series of values? It's easy with the MEDIAN function. What isn't as easy is to derive the ...

Discover More

Counting with Formulas

When you need to count a number of cells based upon a single criteria, the standard function to use is COUNTIF. This tip ...

Discover More

Making PROPER Skip Certain Words

The PROPER worksheet function is used to change the case of text so that only the first letter of each word is uppercase. ...

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}] 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 eight more than 2?

2017-11-22 11:21:25

Don Blaylock

This is how I return blanks on a VLOOKUP:

=IFERROR(VLOOKUP(A1, Z:Z, 1, FALSE), "")

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.

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)),"")

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

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.