Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup time value
I'm trying to lookup the time value 4:05:00 PM or the next highest time value.
I have: B19 = 4:05:00 PM C19 = VLOOKUP(B19,D:D,1) D25 = 4:05:13 PM D26 = 4:05:10 PM D27 = 4:05:09 PM D28 = 4:00:06 PM I thought I'd get 4:00:06 PM as my answer but all I keep getting is 8:02:49 AM. What am I doing wrong? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup time value
You have mentioned that you would like to get the next highets time value for
4:05:00 PM, but at the same time you are saying that you would like to get 4:00:06 PM as your result. How it's possbile? Becuase the value you want to get as a result should be greater than 4:05:00 PM, but the value you are mentioning as your desired result is 4:00:06 PM . Just have a look in these times you will notice that 4:00:06 PM is lower value when comparing to 4:05:00 PM. Becuase the 06 is seconds not minutes. Apart from this the Vlookup formula is not perfect. = VLOOKUP(B19,D:D,1) it should be like this =VLOOKUP(B19,D:D,1,FALSE) OR = VLOOKUP(B19,D:D,1,0). But this also will not get the next highest time value. You can use =LARGE(D:D,1) for first highest time value in D Column, and you Can change the value 2 instead of 1 to get the second highest value (i.e.) =LARGE(D:D,2) like this you can get your desired Results. All the Best! If this post helps, Click Yes! -------------------- (MS-Exl-Learner) -------------------- "GAIDEN" wrote: I'm trying to lookup the time value 4:05:00 PM or the next highest time value. I have: B19 = 4:05:00 PM C19 = VLOOKUP(B19,D:D,1) D25 = 4:05:13 PM D26 = 4:05:10 PM D27 = 4:05:09 PM D28 = 4:00:06 PM I thought I'd get 4:00:06 PM as my answer but all I keep getting is 8:02:49 AM. What am I doing wrong? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup time value
My guess is that your problem is your column is not sorted. You need to have
your times in ascending order for Vlookup to work. Regards, Fred. "GAIDEN" wrote in message ... I'm trying to lookup the time value 4:05:00 PM or the next highest time value. I have: B19 = 4:05:00 PM C19 = VLOOKUP(B19,D:D,1) D25 = 4:05:13 PM D26 = 4:05:10 PM D27 = 4:05:09 PM D28 = 4:00:06 PM I thought I'd get 4:00:06 PM as my answer but all I keep getting is 8:02:49 AM. What am I doing wrong? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
lookup time value
Hi,
if you want the next highest value, please array enter this formula (Ctrl+Shift+Enter) =MIN(IF((D25:D28-$B$19)=0,D25:D28)) -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "GAIDEN" wrote in message ... I'm trying to lookup the time value 4:05:00 PM or the next highest time value. I have: B19 = 4:05:00 PM C19 = VLOOKUP(B19,D:D,1) D25 = 4:05:13 PM D26 = 4:05:10 PM D27 = 4:05:09 PM D28 = 4:00:06 PM I thought I'd get 4:00:06 PM as my answer but all I keep getting is 8:02:49 AM. What am I doing wrong? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
LOOKUP and Time Calc | Excel Discussion (Misc queries) | |||
LOOKUP FUNCTION? (LOOKUP VALUE BEING A TIME RENERATED FROM A FORMU | Excel Discussion (Misc queries) | |||
Lookup and offset at the same time? | Excel Worksheet Functions | |||
Lookup function one row at a time | Excel Worksheet Functions | |||
time lookup | New Users to Excel |