Returning Blanks with VLOOKUP

Thank you so much for this wonderful lesson. It worths more than my time and was really helpful.

If you want to know if ANY cell at all contains a "True NULL" (a real blank, not a formatted value that looks blank, or any other variation on "things that look like blanks but are not actually blank, including formulas that returned what looked blank, then were copied and pasted as that value), then VLOOKUP() has a feature that will tell you.

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.

There is a simple solution without having to do an IF.

Use "General;General;;General" as a custom format

1. Ctrl-1 (Format Cells)
2. Number tab > Custom
3. General;General;;General

Enjoy!

Thank you.

I have a sheet, which is maintained every month, it has the following formula =IF(AA13=0,0,VLOOKUP(Z13,Repayments!$B$2:$AB$4800,22,FALSE)), the purpose of this is to return the value of repayments, and return blanks in the event there is no repayment. It is working fine on an earlier version (i.e. last two weeks). But for the new version of the same sheet it keeps returning 0 value for where it should provide a blank. Any tips?

Thanks the file looks a lot cleaner and you saved me lots of time!

Thank you! This is brilliant. It took me quite awhile to figure out how to use VLOOKUP to solve my business problem, and lo and behold, my beautiful formula returned these pesky 0's in all the blank spaces. Your solution has saved me a lot of troubleshooting. Thanks again.

I use a CONCAT to remove the zeros. Also, wrapping everything in an IFERROR removes the #N/A error when there is no VLOOKUP match.

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

Or even easier - change the setting in the worksheet!

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.

Thank you, saved me a lot of time

An easier way to check for empty cells :

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

How do i create this formula to look through multiple worksheets?

I use this formula to get rid of 0s:

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

I ended up just using the following in place of the above and it worked great.

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

I am using Excel 2013 and the IF(ISNA) formula you provided is not working. I have had many version of Excel in the past and it seems the formulas I used on an older version never work on a newer version. Any suggestions?

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