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

#### CaraEL

##### New Member
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

##### New Member
Much, thank you.

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

CaraEL.xlsm
AB
1Start trpPackage no
217/07/20191011
35/11/20191012
419/12/20191014
524/01/20201015
619/02/20201016
71/04/20201017
816/10/20201018
927/11/20201019
1030/12/20201020
1129/01/20211021
1219/02/20211022
1310/03/20211023
1425/03/20211030
1521/05/20211031
162/07/20211033
1722/07/20211034
1813/08/20211035
1931/08/20211036
2016/10/20212001
214/11/20211038
2219/11/20211039
237/12/20219082
2425/12/20211040
256/01/20221032
261/02/20222002
2717/02/20221042
2810/03/20222003
2929/03/20221043
3014/05/20221037
3119/05/20222004
3222/06/20221055
3326/07/20221056
3411/08/20221057
3527/08/20221058
366/09/20221041
3713/09/20221059
385/10/20221060
3921/10/20221061
4018/11/20221062
417/12/20221063
4222/12/20221064
4311/01/20231044
441/02/20231065
4510/02/20231066
4628/02/20232005
4718/03/20231067
485/04/20231068
4925/05/20231069
5031/05/20239084
5122/06/20232006
5224/06/20231070
5325/07/20231072
5419/12/20191073
5522/08/20231074
561/09/20232007
575/09/20231075
5814/09/20232008
5911/10/20231077
6025/10/20231078
618/11/20231079
626/12/20231081
6320/12/20231082
645/01/20241083
653/02/20241085
6621/02/20241086
6727/02/20241071
686/03/20241087
6922/03/20241088
7016/05/20241076
7125/05/20241090
728/06/20241091
736/07/20241093
7416/07/20241080
753/08/20241095
7617/08/20241096
7731/08/20241097
7814/09/20241098
7917/09/20241084
8015/10/20241099
8129/10/20241100
8212/11/20241101
8326/11/20241102
8429/11/20241089
8510/12/20241103
8624/12/20241104
877/01/20251105
8823/01/20251106
896/02/20251092
908/02/20251107
9122/02/20251108
9213/03/20251109
9325/03/20251094
9428/03/20251110
9517/05/20251111
9631/05/20251112
9714/06/20251113
9828/06/20251114
Program

CaraEL.xlsm
ABC
1Package noReady for DeliveryMatching against transport
2103118/05/202121/05/2021
3103828/09/20214/11/2021
4104219/01/202217/02/2022
5103711/04/202214/05/2022
6105628/06/202226/07/2022
710412/09/20226/09/2022
810629/11/202218/11/2022
9106531/01/20231/02/2023
1010684/04/20235/04/2023
11107014/06/202324/06/2023
1210749/08/202322/08/2023
1310775/10/202311/10/2023
14108212/12/202320/12/2023
15107126/02/202427/02/2024
16107623/04/202416/05/2024
17109024/04/202425/05/2024
18109125/04/20248/06/2024
19109326/04/20246/07/2024
Delivery
Cell Formulas
RangeFormula
A2:A19A2=VLOOKUP(C2,Program!A\$2:B\$98,2,0)
C2:C19C2=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 .

### Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

#### Peter_SSs

##### MrExcel MVP, Moderator
Works perfectly! Thank you soooo much for all the help, highly appreciated .
You're welcome. Thanks for the follow-up.

Replies
8
Views
223
Replies
4
Views
201
Replies
8
Views
137
Replies
3
Views
108
Replies
0
Views
98

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,151,768
Messages
5,766,370
Members
425,350
Latest member
procha

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back