Schedule lookup

[Kariba]

I have been given a schedule below and need to link arrival times to next departure time. ie 10:05 arrival linking to next 11:15 departure or 17:40 linking to next 18:55.

This is filtered on one example - number 54 which is the only common thread between them but linked rows could be hundreds of rows apart as it's in time order. I need to be able to reliably move up column B until it finds a match and then return the value in column G. Tried a few ways but not getting required result.

Any suggestions appreciated.

Book1
	A	B	C	D	E	F	G
1	Aircraft Type	Aircraft Identifier	Flight	Departure Airport	Departure Time	Arrival Airport	Arrival Time
2	737	54	4522	ATL	04:35:00	MCO	07:00
3	737	54	4523	MCO	07:35:00	ATL	10:05
4	737	54	4578	ATL	11:15:00	TPA	14:10
5	737	54	4579	TPA	14:45:00	ATL	17:40
6	737	54	5000	ATL	18:55:00	TPA	20:35
Sheet1

 

[Peter_SSs]

First thing might be to look at updating.

 

[Kariba]

It's a business computer so that would be a difficult and lengthy process, but will give it a go. Even if I developed it elsewhere it wouldn't work on this version.

 

[Peter_SSs]

It's a business computer so that would be a difficult and lengthy process,

A business computer with a faulty Excel is not much use so something really needs to be done with it.

 

[Kariba]

Finally got the 365 upgrade and the LET(AT,I$2:I$100,t,MAX(FILTER(AT,(D$2:D$100=V2)*(AT<G2),NA())),IFNA(t-INT(t),"")) is working beautifully.

Thanks for all your help

 

[Peter_SSs]

Good news eventually!!
Thanks for letting us know.