Formula Issue

[drew.parker]

I'm doing vlookup between two different spreadsheets to pull up a date of when a person schedules. The formula is: =VLOOKUP(B4,'[New Referral Spreadsheet.xlsx]New Referral Record'!$B:$P,15,FALSE)

Sometimes the formula pulls over the correct date and sometimes it pulls over a date of 1/00/00. Any reason why it works for some but not for others?

Thanks!

[Jdevil]

Could it be that the vlookup is returning the value of an empty cell? A 0 value has a date of "1900-01-00"

[JohnTopley]

Date of 01/01/1900 is a returned value of 0 so column P is blank for B4 value.

[Jonmo1]

Yep, that means it found a matching value for B4 in Column B
But the corresponding cell in column P is either blank, or actually 0.

When the cell with your formula is formatted as a date, 0 is shown as 1/00/1900 ( or 1/00/00 if you have only yy shown)

[Jonmo1]

To resolve it, and make it return a blank instead..

=IFERROR(1/(1/VLOOKUP(B4,'[New Referral Spreadsheet.xlsx]New Referral Record'!$B:$P,15,FALSE)),"")

This has the benefit of also hiding the #N/A errors when there is no match for B4.

[drew.parker]

Thanks all! Is there a way to alter the formula so the cell is blank unless there is a date in column P to avoid the 1/1/00?

[drew.parker]

This formula didn't seem to do the trick. Any other ideas?

[Jonmo1]

Works for me, in what way does it not work for you?

Is it still showing 1/00/00 ?

That would indicate the value in P is NOT blank, and is also NOT exactly 0.

Can you post a sample book?

[drew.parker]

Spreadsheet 1 is an ongoing spreadsheet of visits from people.
Spreadsheet 2 is a separate spreadsheet for different visits of a different kind.

I want spreadsheet 2 to pull up the date in the "ATC APPOINTMENT" column from "atc_date" in spreadsheet 1. I also want it to be an empty cell unless it has a date.

Hope that makes sense.

[Jonmo1]

So in spreadsheet 2
In N2 and filled down I put
=VLOOKUP(B2,'[formula-issue-spreadsheet-1.xlsx]Sheet1'!$B:$P,15,0)

Most of them returned #N/A, Im sure because you deleted alot of sensitive data, and just put bogus stuff in.
But the ones that did work (N5 N17 and N31), they returned proper date values.
I didn't get any 1/00/00 values.


I'm still not really clear on what "This formula didn't seem to do the trick." means.
Can you elaborate ?