closest next closest time using Index Match Min ABS functions only

[arbabchin]

Hello Everyone

I am trying to return a time at destination in Cell F5 using the following function, but since the absolute difference of Cell D5 and L6 is greater than with that of L7 it returns 18:30:36, which is not intended, required value is in cell L7

=INDEX(L4:L8,MATCH(MIN(ABS(L4:L8-D5)),ABS(L4:L8-D5),0))

i would like to keep using index match CSE for this particular report.

test.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N
1
2									Master Data
3									Vehicle No.	Reporting	Actual Destination	Direction
4		Vehicle No.	Schedule Time	Actual Departure	Actual Destination Time	Dest-Dep			B0032	In	14:39:28	Forward
5		B0031	19:10:00	19:10:24	18:30:36	#######			B0031	In	16:22:28	Forward
6									B0031	In	18:30:36	Forward
7									B0031	In	19:50:21	Forward
8									B0037	In	21:27:46	Forward
9
10
11
Sheet2

Cell Formulas
Range	Formula
F5	F5	=INDEX(L4:L8,MATCH(MIN(ABS(L4:L8-D5)),ABS(L4:L8-D5),0))
G5	G5	=F5-E5
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Fluff]

Why should it return L7? L6-D5 is the minimum difference.

 

[arbabchin]

Why should it return L7? L6-D5 is the minimum difference.

yes it is certainly, the reason i want L7 is because this data is for calulating Journey time of a vehicle and a vehicle can not reach its destination before its departure, F5 should be a greater than E5.

 

[Fluff]

Ok, how about

=AGGREGATE(15,6,L4:L8/(J4:J8=C5)/(L4:L8>=D5),1)

 

[arbabchin]

Ok, how about

=AGGREGATE(15,6,L4:L8/(J4:J8=C5)/(L4:L8>=D5),1)

it worked,thankyou so much for your help.

 

[Fluff]

You're welcome & thanks for the feedback.