Lookup due date and return earliest work date that contain zero

[Exceltung]

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[TD]P[/TD]
[TD]Q[/TD]
[TD]R[/TD]
[TD]S[/TD]
[TD]T[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Table 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Date[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD]10[/TD]
[TD]11[/TD]
[TD]12[/TD]
[TD]13[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Batch[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]5[/TD]
[TD]4[/TD]
[TD]3[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Table 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Example[/TD]
[TD]Task[/TD]
[TD]Due Date[/TD]
[TD]Proposed Work Date[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Example 1[/TD]
[TD]Task 1[/TD]
[TD]6[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Example 2[/TD]
[TD]Task 2[/TD]
[TD]7[/TD]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Example 3[/TD]
[TD]Task 3[/TD]
[TD]4[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Example 4[/TD]
[TD]Task 4[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Example 5[/TD]
[TD]Task 5[/TD]
[TD]13[/TD]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Hi,

As shown above, Table 1 is the date and the corresponding batch to be produced each day.
Table 2 shows the task and respective due date. Task can only be completed when batch is "0" and not later than the task's due date as shown in the five examples. Need Excel to returned the "Proposed Work Date".

Kindly advice formula or method. Thanks in advance.

 

[DanteAmor]

Array formula

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:73.19px;" /><col style="width:48.48px;" /><col style="width:76.99px;" /><col style="width:141.62px;" /><col style="width:36.12px;" /><col style="width:43.72px;" /><col style="width:76.99px;" /><col style="width:76.99px;" /><col style="width:76.99px;" /><col style="width:76.99px;" /><col style="width:76.99px;" /><col style="width:76.99px;" /><col style="width:76.99px;" /><col style="width:76.99px;" /><col style="width:76.99px;" /><col style="width:76.99px;" /><col style="width:76.99px;" /><col style="width:76.99px;" /><col style="width:76.99px;" /><col style="width:76.99px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td><td >J</td><td >K</td><td >L</td><td >M</td><td >N</td><td >O</td><td >P</td><td >Q</td><td >R</td><td >S</td><td >T</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td > </td><td > </td><td > </td><td > </td><td > </td><td >Date</td><td style="background-color:#c2d69a; text-align:right; ">01/05/2019</td><td style="background-color:#c2d69a; text-align:right; ">02/05/2019</td><td style="background-color:#c2d69a; text-align:right; ">03/05/2019</td><td style="background-color:#c2d69a; text-align:right; ">04/05/2019</td><td style="background-color:#c2d69a; text-align:right; ">05/05/2019</td><td style="background-color:#c2d69a; text-align:right; ">06/05/2019</td><td style="background-color:#c2d69a; text-align:right; ">07/05/2019</td><td style="background-color:#c2d69a; text-align:right; ">08/05/2019</td><td style="background-color:#c2d69a; text-align:right; ">09/05/2019</td><td style="background-color:#c2d69a; text-align:right; ">10/05/2019</td><td style="background-color:#c2d69a; text-align:right; ">11/05/2019</td><td style="background-color:#c2d69a; text-align:right; ">12/05/2019</td><td style="background-color:#c2d69a; text-align:right; ">13/05/2019</td><td style="background-color:#c2d69a; text-align:right; ">14/05/2019</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td > </td><td > </td><td > </td><td > </td><td > </td><td >Batch</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">2</td><td style="text-align:right; ">3</td><td style="text-align:right; ">3</td><td style="text-align:right; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">5</td><td style="text-align:right; ">4</td><td style="text-align:right; ">3</td><td style="text-align:right; ">0</td><td style="text-align:right; ">1</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >Table 2</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="background-color:#b6dde8; ">Example</td><td style="background-color:#b6dde8; ">Task</td><td style="background-color:#b6dde8; ">Due Date</td><td style="background-color:#b6dde8; ">Proposed Work Date</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td >Example 1</td><td >Task 1</td><td style="text-align:right; ">06/05/2019</td><td style="text-align:right; ">03/05/2019</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td >Example 2</td><td >Task 2</td><td style="text-align:right; ">07/05/2019</td><td style="text-align:right; ">07/05/2019</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td >Example 3</td><td >Task 3</td><td style="text-align:right; ">04/05/2019</td><td style="text-align:right; ">03/05/2019</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td >Example 4</td><td >Task 4</td><td style="text-align:right; ">02/05/2019</td><td style="text-align:right; ">02/05/2019</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td >Example 5</td><td >Task 5</td><td style="text-align:right; ">13/05/2019</td><td style="text-align:right; ">12/05/2019</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Array Formula</td></tr><tr><td >D7</td><td >{=MAX(IF($G$2:$T$2<=C7,IF($G$3:$T$3=0,$G$2:$T$2)))}</td></tr></table></td></tr></table>



Array formulas
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

 

[Exceltung]

Thank you, Dante Amor. This is magic to me!

Now I have another scenerio. Now the Due Date is a date range with date start and date end. The returned "Proposed Work Date" should be the date within the Due Date and the lastest date with batch equal to "zero"

Kindly advice the formula. Thanks in advance.

 

[DanteAmor]

Thank you, Dante Amor. This is magic to me!

I'm glad to help you. Thanks for the feedback.

Now I have another scenerio. Now the Due Date is a date range with date start and date end. The returned "Proposed Work Date" should be the date within the Due Date and the lastest date with batch equal to "zero"

You could create a new thread with this question and there you put the examples of how your data is.