Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLookup - finding wrong value
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLookup - finding wrong value
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 "tomic" wrote: 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLookup - finding wrong value
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. -- --Thomas [PBD] Working hard to make working easy. Answered your question? Click ''''Yes'''' below. "tomic" wrote: 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLookup - finding wrong value
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. -- Biff Microsoft Excel MVP "tomic" wrote in message ... 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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLookup - finding wrong value
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. "pdberger" wrote: 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 "tomic" wrote: 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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLookup - finding wrong value
may be ??
=vlookup(B5,Lab Results!$A$2:$B$7,2,false) On Oct 6, 10:20*pm, tomic wrote: * 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP gets wrong value | Excel Discussion (Misc queries) | |||
Vlookup-Wrong Sheet! | New Users to Excel | |||
Vlookup - can't figure out what's wrong | Excel Worksheet Functions | |||
vlookup, what am I doing wrong? | Excel Discussion (Misc queries) | |||
VLOOKUP returning wrong row | Excel Worksheet Functions |