Choose next package if the package starts with a specific number.

[CaraEL]

Hi,

I need some help

My formula below in column C looks into the sheet Program and finds the first best transport after the delivery date in column B. If duplicate matches occur the formula chooses the next best package instead in order to avoid the same ppackage being booked for two or several transports. So far this is correct, however I would like to add a condition to it and that condition is that if the package that is choosen is a package starting with 2 (the 2000 package series) I would like the formula to ignore that package and instead choose the first best package that starts with a 1 (the 1000 series). Is this possible?

Regards
C

Sheet 1 - Delivery
Package no	Ready for Delivery	Match against transport
Column A * see formula below	Column B	Column C ** see formula belox
1031	2021-05-18	2021-05-21
2001	2021-09-28	2021-10-16 want package number 1038 instead
2002	2022-01-19	2022-02-01 want package number 1042 instead
1037	2022-04-11	2022-05-14
1056	2022-06-28	2022-07-26
2006	2023-06-14	2023-06-22 want package number 1073 instead
1074	2023-08-09	2023-08-22

* =VLOOKUP(C2;'Program'!$R:$S;2;FALSE)

**=IF(ISNUMBER(MATCH(INDEX('Program'!$R$9:$R$105;MATCH(B3;'Program'!$R$9:$R$105;1)+3);$C$1:C2;0));INDEX('Program'!$R$9:$R$105;MATCH(B3;'Program'!$R$9:$R$105;1)+4);IF(ISNUMBER(MATCH(INDEX('Program'!$R$9:$R$105;MATCH(B3;'Program'!$R$9:$R$105;1)+2);$C$1:C2;0));INDEX('Program'!$R$9:$R$105;MATCH(B3;'Program'!$R$9:$R$105;1)+3);IF(ISNUMBER(MATCH(INDEX('Program'!$R$9:$R$105;MATCH(B3;'Program'!$R$9:$R$105;1)+1);$C$1:C2;0));INDEX('Program'!$R$9:$R$105;MATCH(B3;'Program'!$R$9:$R$105;1)+2);INDEX('Program'!$R$9:$R$105;MATCH(B3;'Program'!$R$9:$R$105;1)+1))))

Sheet 2 - Program
Delivery Pick up	Package no
Column R	Column S
2021-03-25	1030
2021-05-21	1031
2021-07-02	1033
2021-08-31	1036
2021-10-16	2001
2021-11-04	1038
2022-01-06	1032
2022-02-01	2002
2022-02-17	1042
2022-03-29	1043
2022-05-14	1037
2022-05-19	2004
2022-06-22	1055
2022-07-26	1056
2022-08-11	1057
2023-05-25	1069
2023-06-24	2006
2023-08-08	1073
2023-08-22	1074

 

[CaraEL]

Much, thank you.

See if this is it. Note that my dates are d/m/y format.

CaraEL.xlsm
	A	B
1	Start trp	Package no
2	17/07/2019	1011
3	5/11/2019	1012
4	19/12/2019	1014
5	24/01/2020	1015
6	19/02/2020	1016
7	1/04/2020	1017
8	16/10/2020	1018
9	27/11/2020	1019
10	30/12/2020	1020
11	29/01/2021	1021
12	19/02/2021	1022
13	10/03/2021	1023
14	25/03/2021	1030
15	21/05/2021	1031
16	2/07/2021	1033
17	22/07/2021	1034
18	13/08/2021	1035
19	31/08/2021	1036
20	16/10/2021	2001
21	4/11/2021	1038
22	19/11/2021	1039
23	7/12/2021	9082
24	25/12/2021	1040
25	6/01/2022	1032
26	1/02/2022	2002
27	17/02/2022	1042
28	10/03/2022	2003
29	29/03/2022	1043
30	14/05/2022	1037
31	19/05/2022	2004
32	22/06/2022	1055
33	26/07/2022	1056
34	11/08/2022	1057
35	27/08/2022	1058
36	6/09/2022	1041
37	13/09/2022	1059
38	5/10/2022	1060
39	21/10/2022	1061
40	18/11/2022	1062
41	7/12/2022	1063
42	22/12/2022	1064
43	11/01/2023	1044
44	1/02/2023	1065
45	10/02/2023	1066
46	28/02/2023	2005
47	18/03/2023	1067
48	5/04/2023	1068
49	25/05/2023	1069
50	31/05/2023	9084
51	22/06/2023	2006
52	24/06/2023	1070
53	25/07/2023	1072
54	19/12/2019	1073
55	22/08/2023	1074
56	1/09/2023	2007
57	5/09/2023	1075
58	14/09/2023	2008
59	11/10/2023	1077
60	25/10/2023	1078
61	8/11/2023	1079
62	6/12/2023	1081
63	20/12/2023	1082
64	5/01/2024	1083
65	3/02/2024	1085
66	21/02/2024	1086
67	27/02/2024	1071
68	6/03/2024	1087
69	22/03/2024	1088
70	16/05/2024	1076
71	25/05/2024	1090
72	8/06/2024	1091
73	6/07/2024	1093
74	16/07/2024	1080
75	3/08/2024	1095
76	17/08/2024	1096
77	31/08/2024	1097
78	14/09/2024	1098
79	17/09/2024	1084
80	15/10/2024	1099
81	29/10/2024	1100
82	12/11/2024	1101
83	26/11/2024	1102
84	29/11/2024	1089
85	10/12/2024	1103
86	24/12/2024	1104
87	7/01/2025	1105
88	23/01/2025	1106
89	6/02/2025	1092
90	8/02/2025	1107
91	22/02/2025	1108
92	13/03/2025	1109
93	25/03/2025	1094
94	28/03/2025	1110
95	17/05/2025	1111
96	31/05/2025	1112
97	14/06/2025	1113
98	28/06/2025	1114
Program

CaraEL.xlsm
	A	B	C
1	Package no	Ready for Delivery	Matching against transport
2	1031	18/05/2021	21/05/2021
3	1038	28/09/2021	4/11/2021
4	1042	19/01/2022	17/02/2022
5	1037	11/04/2022	14/05/2022
6	1056	28/06/2022	26/07/2022
7	1041	2/09/2022	6/09/2022
8	1062	9/11/2022	18/11/2022
9	1065	31/01/2023	1/02/2023
10	1068	4/04/2023	5/04/2023
11	1070	14/06/2023	24/06/2023
12	1074	9/08/2023	22/08/2023
13	1077	5/10/2023	11/10/2023
14	1082	12/12/2023	20/12/2023
15	1071	26/02/2024	27/02/2024
16	1076	23/04/2024	16/05/2024
17	1090	24/04/2024	25/05/2024
18	1091	25/04/2024	8/06/2024
19	1093	26/04/2024	6/07/2024
Delivery

Cell Formulas
Range	Formula
A2:A19	A2	=VLOOKUP(C2,Program!A$2:B$98,2,0)
C2:C19	C2	=AGGREGATE(15,6,Program!A$2:A$98/((Program!A$2:A$98>B2)*ISNA(MATCH(Program!A$2:A$98,C$1:C1,0))*(Program!B$2:B$98<2000)),1)

Works perfectly! ? Thank you soooo much for all the help, highly appreciated ?.

 

[Peter_SSs]

Works perfectly! ? Thank you soooo much for all the help, highly appreciated ?.

You're welcome. Thanks for the follow-up.