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?

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?

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?

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?