VLOOKUP date without #N/A & 00/01/1900

[Gwill1983]

Hi all,

Really struggling with this one.

I am carrying out a vlookup against cells containing a date. I have established a way to do this to avoid returning #N/A for errors, but this then returns 00/01/1900 for cells containing no data. If I adjust the formula to get rid of the 00/01/1900 then the #N/A appear again!!

The formula I have for removing the #N/A is =IFERROR(VLOOKUP([@[PO Number]],Prev_Report,9,0),"")
The formula I have for removing the 00/01/1900 is =IF(VLOOKUP([@[PO Number]],Prev_Report,9,0)=0,"",VLOOKUP([@[PO Number]],Prev_Report,9,0))

Is there a way of combining these in some way to avoid both?

I only want it to return a result if there is a date in the resulting cell

 

[Sufiyan97]

Is VLOOKUP result in Date Format?

 

[Fluff]

How about

=IFERROR(1/(1/VLOOKUP([@[PO Number]],Prev_Report,9,0)),"")

 

[Gwill1983]

Is VLOOKUP result in Date Format?

Yep. I idiot checked that just before I posted

 

[Gwill1983]

ADVERTISEMENT

How about

=IFERROR(1/(1/VLOOKUP([@[PO Number]],Prev_Report,9,0)),"")

That doesn't return anything now! It did fix the N#A's and the stupid date though!! ?

 

[Fluff]

Are you sure the values the vlookup returns are numbers & not text?

 

[Gwill1983]

ADVERTISEMENT

Are you sure the values the vlookup returns are numbers & not text?

100% positive. Both the column containing the VLOOKUP and the column with the returns (column 9 within the prev_report table) are set to short date

 

[Fluff]

If you format col 9 of the prev_report table what do you see?

 

[Gwill1983]

If you format col 9 of the prev_report table what do you see?

 

[Fluff]

Oops should have said
If you format col 9 of the prev_report table as General what do you see?