How do I stop vlookup from returning '1/0/1900'?

[Galena1]

Ok, I know most of you are going to recommend I reformat my cells to accomplish this, but I'm being stubborn.

Here's my vlookup, that is working perfectly, except that it returns '1/0/1900' in some blank cell instances.

=IF(ISNA(VLOOKUP($A4,Pivot!$A:$AB,COLUMN()+0,0)),"",IF(VLOOKUP($A4,Pivot!$A:$AB,COLUMN()+0,0)="(blank)","",VLOOKUP($A4,Pivot!$A:$AB,COLUMN()+0,0)))

Isn't there some other formulaic error 'suppression' I can append to it, in order to accomodate this '1/0/1900' condition?



Thanks!

 

[shg]

Assuming the result of the lookup is #N/A, a number, or a blank, then,

=LOOKUP(9E+307, CHOOSE({1,2}, 0, VLOOKUP($A4, Pivot!$A:$AB, COLUMN()+0, FALSE)))

Then format the cell to suppress display of 0, e.g, m/d/yyyy;;

 

[Norie]

Why don't you reformat the cells with the formulas in them to not show a date if they are blank?

That's what I think the problem is, though I do notice you might be using a pivot table as the range to lookup.

Perhaps that makes a difference.

 

[WaxonWaxov]

Ok, I know most of you are going to recommend I reformat my cells to accomplish this, but I'm being stubborn.

Here's my vlookup, that is working perfectly, except that it returns '1/0/1900' in some blank cell instances.

=IF(ISNA(VLOOKUP($A4,Pivot!$A:$AB,COLUMN()+0,0)),"",IF(VLOOKUP($A4,Pivot!$A:$AB,COLUMN()+0,0)="(blank)","",VLOOKUP($A4,Pivot!$A:$AB,COLUMN()+0,0)))

Isn't there some other formulaic error 'suppression' I can append to it, in order to accomodate this '1/0/1900' condition?



Thanks!

Well, 1/0/1900 is a zero value.

I did...

=if((vlookup statement)=0,"",(vlookupstatement))

Make sense? If the result of the vlookup statement is equal to zero then display an empty cell, otherwise display the results of the vlookup.

If the cell containing the formula is formatted as a date the "" value will show up blank.

It's that old thing of the difference between null and zero.

 

[Jon Peltier]

What if the cell really contains zero? You don't want to hide it, do you?

Use

=if(LEN(vlookup statement)=0,"",(vlookupstatement))

So only the apparent blanks in the lookup table appear blank in the results.

 

[shg]

Why evaluate the VLOOKUP unnecessarily twice?

 

[Norie]

Why the +0 after COLUMN()?

 

[Jon Peltier]

Why evaluate the VLOOKUP unnecessarily twice?

It's kind of necessary, isn't it, if you want to show zero values as zero and blanks as blank? The alternative is one column for the VLOOKUP and a second for the displayed value.

 

[shg]

My thinking was that since the result is formatted as a date, it seemed likely that the column contains only dates, blanks, and spurious text, and dates are rarely zero, so it is the blanks that are returning zeros, so suppressing display of zeros should be sufficient.

I reckon that's a lot of assumptions.

 

[Jon Peltier]

Shg -

Good point. If the results are in fact dates, then assuming that a zero is undefined is not a bad assumption.

Sometimes, though, cells are formatted unexpectedly.