mathias1979
Technical User
I'm attempting to use the VLOOKUP function to look up dates, which I've successfully done before. This time around, VLOOKUP does not seem to be finding the dates I'm looking for, even though they exist and are an exact match. So here's a sample.
So my dates look like this:
Date/Time
9/1/2007 13:00
9/1/2007 13:27
9/1/2007 13:58
9/1/2007 14:00
9/1/2007 14:35
9/1/2007 15:07
9/1/2007 15:48
9/1/2007 16:00
Now I only want the values that have been reported on the hour, so my lookup values look like this:
9/1/2007 13:00
9/1/2007 14:00
9/1/2007 15:00
9/1/2007 16:00
My function looks like this:
VLOOKUP(B2,Sheet1!$B$1:$F$12422,3,FALSE)
Excel seems to be finding some dates successfully, but other dates it returns a #N/A. I have used an if statement to test some of the dates that are not being successfully found. So I use the function IF(A1=B1,"YES","NO"), where A1 is the date in the date in the table, and B1 is the matching date I am trying to find...and when I do this, I receive "YES", which to me suggests the dates are exactly the same, formatting and all, so Excel should have no trouble finding the exact match. I have also tried converting the dates to the serial number (i.e. 12/13/2006 20:00 = 39064.83333), and although the numbers match exactly, that has not solved the problem either.
Can anyone help me out?
So my dates look like this:
Date/Time
9/1/2007 13:00
9/1/2007 13:27
9/1/2007 13:58
9/1/2007 14:00
9/1/2007 14:35
9/1/2007 15:07
9/1/2007 15:48
9/1/2007 16:00
Now I only want the values that have been reported on the hour, so my lookup values look like this:
9/1/2007 13:00
9/1/2007 14:00
9/1/2007 15:00
9/1/2007 16:00
My function looks like this:
VLOOKUP(B2,Sheet1!$B$1:$F$12422,3,FALSE)
Excel seems to be finding some dates successfully, but other dates it returns a #N/A. I have used an if statement to test some of the dates that are not being successfully found. So I use the function IF(A1=B1,"YES","NO"), where A1 is the date in the date in the table, and B1 is the matching date I am trying to find...and when I do this, I receive "YES", which to me suggests the dates are exactly the same, formatting and all, so Excel should have no trouble finding the exact match. I have also tried converting the dates to the serial number (i.e. 12/13/2006 20:00 = 39064.83333), and although the numbers match exactly, that has not solved the problem either.
Can anyone help me out?