Trouble combining If(Countif & Vlookup criteria to omit 1/0/1900 values

[eNovice22]

Hello. I am using Windows 7 and Excel 2007.

I want to see results if the alpha-numeric value of column B appears in column A of Sheet3. The result I am looking for is a date value from Sheet3 listed in column W corresponding to the row matching the value in the current sheet's column B. If the value in row B is not a match in Sheet3, I want output to read "not found". The formula works like a charm; however, I need to eliminate date values of 1/0/1900 using a formula vs. conditional formatting on the current sheet.

Here is the formula which is working currently, but displaying the 1/0/1900 value:

=IF(COUNTIF(Sheet3!$A$2:$A$100,B2)<>0,VLOOKUP(B2,Sheet3!$A$2:$W$100,23,0),"not found")


I checked Sheet3 and the ones giving that date value are actually blank cells. This was my latest in a series of feeble attempts, none of which render accurate results:

=IF(COUNTIF(Sheet3!$A$2:$A$100,B2)<>0,IF(LEN(VLOOKUP(B2,Sheet3!$A$2:$W$100,23,0)=0),"",IF(VLOOKUP(B2,Sheet3!$A$2:$W$100,23,0),"not found")))

I'm not sure if I can actually have the "value if true" & "value if false" criteria to the original IF statement be a different vlookup with it's on true/false values. If it is possible, can you please help me achieve the right results.

Thanks!!!

 

[Aladin Akyurek]

Does this work for you?

=IF(COUNTIFS(Sheet3!$A$2:$A$100,B2),IFERROR(1/(1/VLOOKUP(B2,Sheet3!$A$2:$W$100,23,0)),""),"not found")

 

[eNovice22]

Sorry, I may have replied to you individually, but want the world to know... YOU are magic! Thanks so much for your help. Problem solved!!!

 

[Aladin Akyurek]

Sorry, I may have replied to you individually, but want the world to know... YOU are magic! Thanks so much for your help. Problem solved!!!

You are welcome. Thanks for the kind words.