VLookup - finding wrong value

[tomic]

I am trying to use a vlookup formula to lookup data based on a time, but it
is not always giving me the correct value. It seems like if the time entered
is before noon time (12 PM) it returns the wrong value Anything after 12PM is
ok. Here is the formula I'm using. Any help would be appreciated.

Sheet = Lab Results
A B
Time Result
9:30 AM 2
9:45 AM 3
10:00 AM 4
11:30 AM 5
12:30 PM 6
1:30 PM 10

On a seperate sheet an operator enters another time into cell B5, and in C5
I have the following formula.

=vlookup(B5,Lab Results!$A$2:$B$7,2)

If I enter in 10:00 AM into B5, I get 3 in C5, when I would expect to get 4.

Thanks.

 

[pdberger]

Tomic --

Your example may describe one or two issues. First -- how vlookup works.
It counts down the left column until it gets to the first value equal to or
less than the test value, then GOES BACK UP ONE ROW to get the value to
return. The way to fix this problem is to change 10:00AM to 9:59AM, and the
other times as well. That should fix the problem. Check by putting in a
time of 10:07AM, or something not right on a 'dividing line'. If not, then
you may not have your times set up correctly.

HTH

 

[Thomas [PBD]]

tomic,

Are the operators entering the data as 1:30 AM or PM? Simply typing 1:30
will default to AM, so it could error that way. You can check this in the
Formula line of the excel document when clicking on the operator entries.

 

[T. Valko]

How have the times in your table been generated?

Try *manually* re-entering 10:00 AM and see if it works correctly.

If the times have been generated using a fill operation there's a good
chance that you're experiencing a "rounding issue" where the displayed time
like 10:00 AM is not *exactly* 10:00 AM.

 

[tomic]

Thanks.

That seems to work (adding a minute to the data), I can't say as I'm crazy
about that solution, but it works.

Thanks for your help.

 

[muddan madhu]

may be ??

=vlookup(B5,Lab Results!$A$2:$B$7,2,false)