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
* =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))))
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 |