IF( Vlookup>Date) displays 1/0/1900 instead of blank

[ThePangloss]

So I'm trying to use

=IFERROR(VLOOKUP(D2,'Sheet2l'!$A$2:$M$2467,5,FALSE)>P2,VLOOKUP(D2,'Sheet2'!$A$2:$M$2467,5,FALSE))

I tried using it with IF instead of IFERROR as well and it didn't work

using

=IF(VLOOKUP(D2,'Sheet2'!$A$2:$M$2467,6,FALSE)<=P2,VLOOKUP(D2,'Sheet2'!$A$2:$M$2467,6,FALSE),"")


which produces 1/0/1900 for anything that has a blank in Sheet2

Any way to fix the 1/0/1900's and the #N/A's that come with the second formula?

 

[Michael M]

Maybe this

=IF(ISNA(VLOOKUP(D2,Sheet2!$A$2:$M$2467,5,FALSE))>P2,"",VLOOKUP(D2,Sheet2!$A$2:$M$2467,5,FALSE))

 

[ThePangloss]

Maybe this

=IF(ISNA(VLOOKUP(D2,Sheet2!$A$2:$M$2467,5,FALSE))>P2,"",VLOOKUP(D2,Sheet2!$A$2:$M$2467,5,FALSE))

I tried it and it just gives me no result for the whole column when there are actually a few that should come up.

 

[Michael M]

Are you referencing the correct column....ie, 5

 

[ThePangloss]

I meant to use 6 but yeah, I referenced from the correct one.

 

[Michael M]

OK, what is in col "D" and what is in the first Col of the table array "Sheet2!$A$2:$M$2467" ??
AND it is in Sheet2 ??

 

[ThePangloss]

OK, what is in col "D" and what is in the first Col of the table array "Sheet2!$A$2:$M$2467" ??
AND it is in Sheet2 ??

In Col D is a unique reference number, which is in both sheets(Col A in Sheet 2). In the first Column of Sheet 2 is the list of reference #'s and yes it's in Sheet 2.

When I use the second formula I posted, just with the IF(VLOOKUP....) I get matches but the other ones that should be blank end up as #N/A's and 1/0/1900's with the latter being way more common. I guess it has to do with the formatting of the cells in Sheet2 and the fact that some look blank but probably aren't.

 

[Michael M]

If it's a unique reference number, why are looking for a greater than the value in P2 in the formula ??

 

[ThePangloss]

If it's a unique reference number, why are looking for a greater than the value in P2 in the formula ??

I need to pull a date from the 6th column(sheet 2) that is less than the date in Column P(sheet 1) so I figured if VLOOKUP finds a date, and it's less than the date in P2 it should paste it into the cell(Which is why i did IF(VLOOKUP<P). I'm trying to chronologically sort dates for this but it's not just as simple as chronologically sorting because some dates are in a group that go together. I used the IFERROR to use > than because that'd give an error, and in the IF i used less than because that's what I wanted.

 

[Aladin Akyurek]

If you are retrieving dates (numbers), try:

=IFERROR(1/(1/VLOOKUP(D2,'Sheet2l'!$A$2:$M$2467,5,0)),"")