lookup time value

[GAIDEN]

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?

 

[Ms-Exl-Learner]

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!

 

[Fred Smith]

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.

 

[Ashish Mathur]

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