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!

 

[barry houdini]

The original formula also converts a VLOOKUP return of "(blank)" to "" so, to retain that and convert blank returns to blank why not just introduce an OR into that part?

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

That will return a blank if the return value is blank - if you want any zeroes to also return a blank then you could replace the {"(blank)",""} part with {"(blank)","",0}

 

[shg]

Yo, Barry, why you addin' 0 to COLUMN()?

 

[morleyuk]

When you Vlookup the blanks are read as 0 and in date format field they display as 00/01/1900 (dd/mm/yyyy).

Your formula needs to be (also picking up on some of the others points):

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

This should stop the dates.

 

[barry houdini]

Yo, Barry, why you addin' 0 to COLUMN()?

I didn't start it, shg......

I assume the formula was originally using COLUMN()+2 or similar.....not required though.......it's not particularly robust in case of column deletion or insertion, perhaps better to use COLUMNS (without +0)

 

[Jonmo1]

Perhaps you can keep your original formula, and just go to

Tools - Options - View
UNcheck "Zero Values"

 

[barry houdini]

One more....

If return values are restricted to blanks, text like "(blank)" and dates then this formula would suppress errors like #N/A and also suppress text and blanks, only returning the dates....

=IF(COUNT(VLOOKUP($A4,Pivot!$A:$AB,COLUMN(),0)&""),VLOOKUP($A4,Pivot!$A:$AB,COLUMN(),0),"")

 

[Msadiqrajani]

try

e4 = value to find
C column of values in lookup table
D column of dates in lookup table


=IF(OR(ISBLANK(VLOOKUP(E4,C4:D9,2,0)),VLOOKUP(E4,C4:D9,2,0)="(blank)"),"",VLOOKUP(E4,C4:D9,2,0))