How to find the next nearest time from array to given time

[Paul21]

Hello,

I am trying to solve the following problem and your expertise and help would be greatly appreciated :
Ho to find the next nearest time from array B5:B36 to date entered by user in E4. (User should enter a time when the freight is ready and formula should pick the next closest date and flight number from the array)
I was experimenting with this formula =INDEX(B5:B36,MATCH(MIN(ABS(B5:B36-E4)),ABS(B5:B36-E4),0))
but it's not working properlyL. I am not sure whether I can use this technique only for time without having dates.
How midnight 00:00:00 will be interpreted.

Really appreciate all the answers and time spent on this!!!

 

[Kamolga]

What format do you use? In te array you have dates as well? So dd/mm/yy hh:mm in E4? If yes,

You could have index match on time(hour(E4),...)​

Note that to find the next value, you can use

match(value,array,-1)

instead of match(,,0)

 

[Paul21]

Hi Kamolga,
Array and E4 should contain time values in hh:mm or HH/MM AM/PM format.
Can you pelase be more specific about the formula to use? Was not able to integrate your propsoal.
Does this dormula can work with values containing only time?
Thank you,

 

[Kamolga]

If the times in B5:B36 are sorted

=INDEX(B5:B36,MATCH(E4,B5:B36)+1)

For some reason, if you asked the time before, it works with

=INDEX(B5:B36,MATCH(E4,B5:B36,1))

​

So the next greater time should simply be

=INDEX(B5:B36,MATCH(E4,B5:B36,-1))

but I have an unexpected error. ​

 

[Kamolga]

This works with unsorted data in B5:B36

=SMALL(IF($B$5:$B$36>E4,$B$5:$B$36),1)

This is an array formula, so need CTRL+SHIFT+Enter when you enter it. This should bring {} around it