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

CaraEL

New Member
Joined
Nov 21, 2017
Messages
16
Office Version
  1. 2016
Platform
  1. Windows
Hi,

I need some help :giggle:

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 noReady for DeliveryMatch against transport
Column A * see formula belowColumn BColumn C ** see formula belox
10312021-05-182021-05-21
20012021-09-282021-10-16 want package number 1038 instead
20022022-01-192022-02-01 want package number 1042 instead
10372022-04-112022-05-14
10562022-06-282022-07-26
20062023-06-142023-06-22 want package number 1073 instead
10742023-08-092023-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 upPackage no
Column RColumn S
2021-03-251030
2021-05-211031
2021-07-021033
2021-08-311036
2021-10-162001
2021-11-041038
2022-01-061032
2022-02-012002
2022-02-171042
2022-03-291043
2022-05-141037
2022-05-192004
2022-06-221055
2022-07-261056
2022-08-111057
2023-05-251069
2023-06-242006
2023-08-081073
2023-08-221074
 

CaraEL

New Member
Joined
Nov 21, 2017
Messages
16
Office Version
  1. 2016
Platform
  1. Windows
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

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Forum statistics

Threads
1,147,747
Messages
5,742,966
Members
423,769
Latest member
LongToast

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top