find closest match to a given value

Good morning

In the attached sheet, I am using !Staff times columns N and O to nominate a mid point break time relevant to the start,lunch break and finish times of a shift.

What I would like to do is match the result to the closest time from !Race Times Column D (highlighted green). So if for example the mid point of the shift is 10:37 (as in !Staff Times cell N23), it actually comes back with the !Race Times result of 10:35 which is the closest actual race.

Ideally I would like it to look both sides of the midpoint time until it came to the closest match, rather than always looking at an earlier time, or always a later time (eg the cell N79 has a value of 11:36, however the 2 race times are 11:35 and 11:40. Preference is it shows 11:35). If however the N79 result was 11:38 or 11:39, I would prefer it went to 11:40).

However if this is not possible then picking the next time in either direction will do.

Thanks
Darren

Darren, the issue is that the times on WTTN sheet are actually DateTime values, eg: D3: 1st Jan 1900 10:10:00 ... so you need to extract just the Time element for your formula such that:

T15:
=INDEX(WTTN!$D$3:$D$164,MATCH(MIN(ABS(MOD(WTTN!$D$3:$D$164,1)-$Q15)),ABS(MOD(WTTN!$D$3:$D$164,1)-$Q15),0))
committed with CTRL + SHIFT + ENTER
copy down as required

in the above we've added a MOD to the function - this will extract just the TIME element from the WTTN values.