Help with matching TIME formula from a list

Hi all,

I m a relative newbie to excel and would really appreciate some assistance with a formula I'm playing with.

Example problem: I have a list of train times and am trying to get excel to load up the next train time from the list based on the current time.

The formula I am using to generate the next departure time is:

Formula:

Please Login or Register  to view this content.

$B$12:$B$83 is the source list of times
$B$1 is the current time

Although the formula does match to the nearest train time from the list, I sometimes find that the matched time is in the past (e.g. current time 21:10, but excel highlights 20:56 as the next departure train time as its the closest match to the list, although in the past so not helpful if Im looking to get onto the next train).

Is there a way I can tweak the formula so that it matches to the next greatest timing from the list?

Thanks in advance

E

Do you want to tweak this formula/algorithm or try a different one? The algorithm I would have used, assuming the times in B2:B83 are or can be sorted in descending order, would be to use a simple "approximate match" search (3rd argument of MATCH() is -1).

1) Find the row with the time equal to or just larger than the value in B1 MATCH($B$1,$B$12:$B$83,-1). Again, I will emphasize that B12:B83 must be sorted in descending order.
2) Use an INDEX() function to return the value in that row =INDEX($B$12:$B$83,MATCH($B$1,$B$12:$B$83,-1))

Would that work for you?

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not told us about this. You are required to do so.

Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

(Note: this requirement is not optional. As you are new here, I wil, do it for you this time: https://www.mrexcel.com/board/thread...lease.1209547/ )