Help with dynamic formulas to transform bigger sets of data

[locksmith55]

Hello,

I am here working with shipping data. The data shows shipping prices for different routes between China and Japan. The prices itself is not relevant for solving this problem so I have just replaced them all with 0. For certain dates, certain shipping rates will be valid. I have already made a function to determine which rates are valid based on today's date. I am then trying to make dynamic formulas that will populate N3:S3 with correct information associated with the valid routes today.

Would appreciate a solution that is not too complex. Use of helper columns is welcome. I have been trying to solve this problem for days now with little success. I appreciate any help I can get. I use MS365 office.

test 2 mr excel.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1		SHIPPING COMPANY	DHL	DHL	DHL	DHL	UPS	UPS	UPS	UPS		Get todays date		HOW I WANT IT TO LOOK LIKE
2		ROUTE ID	DHL0716US	DHL0716US	DHL0935US	DHL0935US	NYC12A0227	NYC12A0227	NYC12A0228	NYC12A0228		7/24/2022		Date Valid From	Date Valid To	Route From	Route Destination	Shipping Company	Route ID
3		DATE (from, to)	6/15/2022	7/10/2022	7/11/2022	7/31/2022	6/1/2022	6/20/2022	6/21/2022	7/30/2022				7/11/2022	7/31/2022	HONG KONG	Tokyo	DHL	DHL0935US
4		Check if route is valid today	NOT VALID	NOT VALID	VALID	VALID	NOT VALID	NOT VALID	VALID	VALID				7/11/2022	7/31/2022	DALIAN	Tokyo	DHL	DHL0935US
5	From (cities)	Destination (cities)												7/11/2022	7/31/2022	SHANGHAI	Tokyo	DHL	DHL0935US
6	HONG KONG	Tokyo	0	0	0	0	0	0	0	0				7/11/2022	7/31/2022	XINGANG	Tokyo	DHL	DHL0935US
7	DALIAN	Tokyo	0	0	0	0	0	0	0	0				7/11/2022	7/31/2022	NINGBO	Tokyo	DHL	DHL0935US
8	SHANGHAI	Tokyo	0	0	0	0	0	0	0	0				6/21/2022	7/30/2022	HONG KONG	Tokyo	UPS	NYC12A0228
9	XINGANG	Tokyo	0	0	0	0	0	0	0	0				6/21/2022	7/30/2022	DALIAN	Tokyo	UPS	NYC12A0228
10	NINGBO	Tokyo	0	0	0	0	0	0	0	0				6/21/2022	7/30/2022	SHANGHAI	Tokyo	UPS	NYC12A0228
11														6/21/2022	7/30/2022	XINGANG	Tokyo	UPS	NYC12A0228
12		Additional Destinations (cities)	0	0	0	0	0	0	0	0				6/21/2022	7/30/2022	NINGBO	Tokyo	UPS	NYC12A0228
13		Kyoto	0	0	0	0	0	0	0	0				7/11/2022	7/31/2022	HONG KONG	Kyoto	DHL	DHL0935US
14		Osaka	0	0	0	0	0	0	0	0				7/11/2022	7/31/2022	DALIAN	Kyoto	DHL	DHL0935US
15		Sapporo	0	0	0	0	0	0	0	0				7/11/2022	7/31/2022	SHANGHAI	Kyoto	DHL	DHL0935US
16														7/11/2022	7/31/2022	XINGANG	Kyoto	DHL	DHL0935US
17														7/11/2022	7/31/2022	NINGBO	Kyoto	DHL	DHL0935US
18														6/21/2022	7/30/2022	HONG KONG	Kyoto	UPS	NYC12A0228
19														6/21/2022	7/30/2022	DALIAN	Kyoto	UPS	NYC12A0228
20														6/21/2022	7/30/2022	SHANGHAI	Kyoto	UPS	NYC12A0228
21														6/21/2022	7/30/2022	XINGANG	Kyoto	UPS	NYC12A0228
22														6/21/2022	7/30/2022	NINGBO	Kyoto	UPS	NYC12A0228
23														7/11/2022	7/31/2022	HONG KONG	Osaka	DHL	DHL0935US
24														7/11/2022	7/31/2022	DALIAN	Osaka	DHL	DHL0935US
25														7/11/2022	7/31/2022	SHANGHAI	Osaka	DHL	DHL0935US
26														7/11/2022	7/31/2022	XINGANG	Osaka	DHL	DHL0935US
27														7/11/2022	7/31/2022	NINGBO	Osaka	DHL	DHL0935US
28														6/21/2022	7/30/2022	HONG KONG	Osaka	UPS	NYC12A0228
29														6/21/2022	7/30/2022	DALIAN	Osaka	UPS	NYC12A0228
30														6/21/2022	7/30/2022	SHANGHAI	Osaka	UPS	NYC12A0228
31														6/21/2022	7/30/2022	XINGANG	Osaka	UPS	NYC12A0228
32														6/21/2022	7/30/2022	NINGBO	Osaka	UPS	NYC12A0228
33														7/11/2022	7/31/2022	HONG KONG	Sapporo	DHL	DHL0935US
34														7/11/2022	7/31/2022	DALIAN	Sapporo	DHL	DHL0935US
35														7/11/2022	7/31/2022	SHANGHAI	Sapporo	DHL	DHL0935US
36														7/11/2022	7/31/2022	XINGANG	Sapporo	DHL	DHL0935US
37														7/11/2022	7/31/2022	NINGBO	Sapporo	DHL	DHL0935US
38														6/21/2022	7/30/2022	HONG KONG	Sapporo	UPS	NYC12A0228
39														6/21/2022	7/30/2022	DALIAN	Sapporo	UPS	NYC12A0228
40														6/21/2022	7/30/2022	SHANGHAI	Sapporo	UPS	NYC12A0228
41														6/21/2022	7/30/2022	XINGANG	Sapporo	UPS	NYC12A0228
42														6/21/2022	7/30/2022	NINGBO	Sapporo	UPS	NYC12A0228
Input

Cell Formulas
Range	Formula
D1:D2,D6:J6,J1:J2,H1:H2,F1:F2	D1	=C1
L2	L2	=DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY()))
N3:N7,N33:N37,N13:N17,N23:N27	N3	=$E$3
O3:O7,O33:O37,O13:O17,O23:O27	O3	=$F$3
P3:Q7	P3	=A6
N8:N12,N38:N42,N18:N22,N28:N32	N8	=$I$3
O8:O12,O38:O42,O18:O22,O28:O32	O8	=$J$3
P8:Q12	P8	=A6
R3:R7,R13:R17,R23:R27	R3	=$E$1
S3:S7,S13:S17,S23:S27	S3	=$E$2
R8:R12,R18:R22,R28:R32	R8	=$I$1
S8:S12,S18:S22,S28:S32	S8	=$I$2
Q13:Q22	Q13	=$B$13
Q23:Q32	Q23	=$B$14
C4,E4,G4,I4	C4	=IF(AND($L$2>=C3,$L$2<=D3),"VALID","NOT VALID")
D4,F4,H4,J4	D4	=IF(AND($L$2>=C3,$L$2<=D3),"VALID","NOT VALID")
C7:J10,C12:J15	C7	=C6
R33:S37,P33:P42	P33	=P3
Q33:Q37	Q33	=$B$15
Q38:Q42	Q38	=Q33
R38:R42	R38	=R18
S38:S42	S38	=S28