Excel: Searching dates using VLOOKUP

[mathias1979]

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?

 

[SkipVought]

hi,

9/1/2007 15:00 does not exist in the first list. Using the FALSE for the range_lookup parameter indicates you want EXACT matches. Hence the #NA!


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[xlhelp]

Try VLOOKUP(B2*1,Sheet1!$B$1:$F$12422,3,FALSE) and see if that makes a difference. (Not Tested)



Vita Brevis

 

[SkipVought]

"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..."

Not necessarily, if you have your calculation set to MANUAL, you might see the same values as the COPIED CELL. Hit F9.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[mathias1979]

In my data, there are some dates that are not in the lookup table, so I would expect the #N/A error. I am experiencing the problem even when the date does exist. So in my sample dates, for instance, 9/1/2007 14:00 returns a #N/A, even though it clearly exists.

And I the F9 update does not explain the results to my IF test.

 

[SkipVought]

Any value between 39326.58333 and 39326.58402 will display 9/1/2007 14:00.


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[mathias1979]

But like I said, the serial numbers match EXACTLY out to 30 decimal places. I must be doing something wrong here.

 

[AnotherHiggins]

On the second sheet (the one containing the hours to be looked up), did you Auto-Fill the hours?

I'd expect you to have seen a difference in the serial numbers, but I have seen auto-filling hours cause problems before.

To see this problem: Type 1:00 in A1 and 2:00 in A2, then drag down to populate down through 23:00. In column B, actually type in each of the times, like 1:00, 2:00, etc. In column C, type in =B1=A1. You can AutoFill that column down. You'll see some FALSEs in column C.

If you look at the serial numbers, you'll see the value for 20:00 in column A is
[tab]0.83333333333333[!]4[/!]
whereas column B is
[tab]0.83333333333333[!]3[/!]

And that will cause a #N/A in your lookup.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

It is difficult to compare any floating point value for an exact match. Just because you SEE displayed, values to so many places, does not guarantee ANYTHING.

You would be better off truncating your values to 5 places, which is ample precision for MINUTES.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[mathias1979]

Yes I did autofill, and that does look like it was the problem. I truncated to 6 places (5 wasn't quite enough precision) and that solved the problem. Thanks for the help guys...that was driving me crazy!

 

[AnotherHiggins]

Glad we could help!

It seems that Skip was right - the serial numbers didn't match.


[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.