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]

It's not in a formal table ..

Not too surprising then that a formula referring to a table structure doesn't work.

Thanks for the additional information. See if this works any better.

Kariba.xlsm
	D			G		I												U	V	W
1	Aircraft Registration Identifier			Departure Time		Arrival Time													Identifier	Arrival
2	18			07/09/2023 05:45		07:40:00													18
3	20			07/09/2023 06:00		07:30:00													20
4	13			07/09/2023 08:00		09:10:00													13
5	18			07/09/2023 08:10		10:00:00													18	07:40:00
6	20			07/09/2023 08:25		10:45:00													20	07:30:00
7	13			07/09/2023 09:40		11:25:00													13	09:10:00
8	18			07/09/2023 10:50		13:50:00													18	10:00:00
9	20			07/09/2023 11:20		13:55:00													20	10:45:00
10	13			07/09/2023 11:55		13:35:00													13	11:25:00
11	13			07/09/2023 14:20		15:40:00													13	13:35:00
12	18			07/09/2023 14:20		17:15:00													18	13:50:00
13	13			07/09/2023 16:10		17:25:00													13	15:40:00
14	20			07/09/2023 16:15		19:05:00													20	13:55:00
15	13			07/09/2023 17:55		19:25:00													13	17:25:00
16	18			07/09/2023 18:00		19:40:00													18	17:15:00
17	20			07/09/2023 19:35		22:15:00													20	19:05:00
18	13			07/09/2023 19:55		21:15:00													13	19:25:00
19	18			07/09/2023 20:10		21:45:00													18	19:40:00
Catering Schedule

Cell Formulas
Range	Formula
V2:V19	V2	=D2
W2:W19	W2	=LET(AT,I$2:I$100,t,MAX(FILTER(AT,(D$2:D$100=V2)*(AT<G2),NA())),IFNA(t-INT(t),""))

 

[Kariba]

 

[Peter_SSs]

That would indicate to me that this information may not be valid since FILTER is a standard function in Microsoft 365.

Can you confirm that your version is actually 365?

In a blank cell if you type =FILT do you get these two options appearing?

If you close right out of Excel and then open up with a blank workbook, does this work as shown here?

23 09 07.xlsm
	A	B
1	a	a
2	b	a
3	a
Test

Cell Formulas
Range	Formula
B1:B2	B1	=FILTER(A1:A3,A1:A3="a")
Dynamic array formulas.

.. or for a different approach, try this

Kariba.xlsm
	D			G		I												U	V	W
1	Aircraft Registration Identifier			Departure Time		Arrival Time													Identifier	Arrival
2	18			07/09/2023 05:45		07:40:00													18
3	20			07/09/2023 06:00		07:30:00													20
4	13			07/09/2023 08:00		09:10:00													13
5	18			07/09/2023 08:10		10:00:00													18	07:40:00
6	20			07/09/2023 08:25		10:45:00													20	07:30:00
7	13			07/09/2023 09:40		11:25:00													13	09:10:00
8	18			07/09/2023 10:50		13:50:00													18	10:00:00
9	20			07/09/2023 11:20		13:55:00													20	10:45:00
10	13			07/09/2023 11:55		13:35:00													13	11:25:00
11	13			07/09/2023 14:20		15:40:00													13	13:35:00
12	18			07/09/2023 14:20		17:15:00													18	13:50:00
13	13			07/09/2023 16:10		17:25:00													13	15:40:00
14	20			07/09/2023 16:15		19:05:00													20	13:55:00
15	13			07/09/2023 17:55		19:25:00													13	17:25:00
16	18			07/09/2023 18:00		19:40:00													18	17:15:00
17	20			07/09/2023 19:35		22:15:00													20	19:05:00
18	13			07/09/2023 19:55		21:15:00													13	19:25:00
19	18			07/09/2023 20:10		21:45:00													18	19:40:00
Catering Schedule

Cell Formulas
Range	Formula
V2:V19	V2	=D2
W2:W19	W2	=LET(t,AGGREGATE(14,6,I$2:I$100/((D$2:D$100=V2)*(I$2:I$100<G2)),1),IFERROR(t-INT(t),""))

 

[Kariba]

Hi

Only getting the one option on FILT function.
Getting the #NAME? error on the alternative

 

[Peter_SSs]

Try the other things that I suggested?

If you close right out of Excel and then open up with a blank workbook, does this work as shown here?

.. or for a different approach, try this

 

[Kariba]

Yes, I had done. Getting "That function isn't valid" on first and getting the #NAME? error on the alternative.

 

[Peter_SSs]

getting the #NAME? error on the alternative.

Do you mean that you got a #NAME? error on the formula using the AGGREGATE function? If so, I think something has gone very wrong with your Excel. You may need to do a repair or re-install.

 

[Kariba]

Yes, #NAME? error on the aggregate function. Might explain why I'm having so many issues

 

[Fluff]

What is the build version is it 1908?

 

[Kariba]