If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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,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
|
|||
|
|||
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,1) it should be like this =VLOOKUP(B19,D,1,FALSE) OR = VLOOKUP(B19,D,1,0). But this also will not get the next highest time value. You can use =LARGE(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,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,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
|
|||
|
|||
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,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
|
|||
|
|||
lookup time value
Hi,
if you want the next highest value, please array enter this formula (Ctrl+Shift+Enter) =MIN(IF((D2528-$B$19)=0,D2528)) -- 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,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? |
Thread Tools | |
Display Modes | |
|
|