issue finding a date in vlookup

Members,

what should be a simple vlookup is driving me nuts. I have a field for a date and time input that i want to search for in a column of dates and time. Both the input entry cell and the search cells are formatted as yyyy/mm/dd hh:mm:ss and there are no blank cell in the column of dates/time

A B C D

Date/Time Open High (input field 2013/2/15 15:30:00
2013/2/15 17:00:00 1517.25 1517.75
2013/2/15 16:45:00 1517.25 1517.5
2013/2/15 16:30:00 1516.75 1517.5
2013/2/15 16:00:00 1517.25 1518.5
2013/2/15 15:45:00 1516.5 1517.5
2013/2/15 15:30:00 1514.75 1516.5 **********
2013/2/15 15:15:00 1513 1515
2013/2/15 15:00:00 1512.5 1514

formula: =Vlookup(D1,A1:C9,3,false) This should return a value of 1516.5, correct?

However, all I get in the formula cell is #N/A Am I doing it correctly or is there a better way?

Any assistance wil lbe greatly appreciated. Thanks!

My guess would be that the values are slightly different...check for leading, trailing spaces, dates/times are dates/times, etc...
The #N/A means it did not find a match, so I would think even Match() would return a similar result for you..

Could you upload a sample to look at?..very hard to picture the data in your OP

its to do with rounding try array entered
=VLOOKUP(ROUND(F2,6),ROUND(A3:C34,6),3,FALSE)

Okay, I see what the problem is, just trying to figure out a solution..
The problem is round-off error at the bottom end of the time code..your value in f2 rounds off to 7 at 10th decimal, the matching value rounds off to 6 at tenth decimal.. will keep working on it

Or, use martins solution ...lol

Alternative solution, regular formula :

Formula:

Please Login or Register  to view this content.

This does basically the same thing as Martin's though, so either one should work for you

a did you array enter it with ctrl+shift+enter?
http://office.microsoft.com/en-us/ex...872901033.aspx

Returns the proper result for me???
Not sure why you are getting wrong value...

You are welcome, and thanks for the rep

And that is what both Martin and I said, the issue is the rounding error, it is (basically) caused by trying to store a decimal number(or 'real' number) into a binary format...