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

Written by Allen Wyatt (last updated June 2, 2018)**This tip applies to** Excel 97, 2000, 2002, and 2003

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

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

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

Discover MoreThe data validation capabilities of Excel are really handy when you want to limit what is put into a cell. However, you ...

Discover MoreNeed to know the column number for use in a formula? The worksheet function you want is the COLUMN function, described in ...

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

2021-04-24 08:34:25

ROY

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

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

Thank you.

2020-04-25 18:12:35

samuel

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

2019-08-05 12:40:41

Bill C

=IFERROR(CONCAT(VLOOKUP(C2,'11g'!$B$2:$P$347,3,FALSE),""),"")

2019-07-30 08:50:05

Ellybee

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

=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

=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

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

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.

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

Copyright © 2022 Sharon Parq Associates, Inc.

## Comments