Excel to return value give multiple criteria

Exceltung

New Member
Joined
May 29, 2019
Messages
16
[TABLE="width: 947"]
<colgroup><col width="23" style="width: 17pt; mso-width-source: userset; mso-width-alt: 841;"> <col width="64" style="width: 48pt;" span="2"> <col width="111" style="width: 83pt; mso-width-source: userset; mso-width-alt: 4059;"> <col width="143" style="width: 107pt; mso-width-source: userset; mso-width-alt: 5229;"> <col width="143" style="width: 107pt; mso-width-source: userset; mso-width-alt: 5229;"> <col width="113" style="width: 85pt; mso-width-source: userset; mso-width-alt: 4132;"> <col width="65" style="width: 49pt; mso-width-source: userset; mso-width-alt: 2377;" span="4"> <col width="68" style="width: 51pt; mso-width-source: userset; mso-width-alt: 2486;" span="4"> <col width="69" style="width: 52pt; mso-width-source: userset; mso-width-alt: 2523;"> <tbody>[TR]
[TD="width: 23, bgcolor: transparent"]A1[/TD]
[TD="width: 64, bgcolor: transparent"]B[/TD]
[TD="width: 64, bgcolor: transparent"]C[/TD]
[TD="width: 111, bgcolor: transparent"]D[/TD]
[TD="width: 143, bgcolor: transparent"]E[/TD]
[TD="width: 143, bgcolor: transparent"]F[/TD]
[TD="width: 113, bgcolor: transparent"]G[/TD]
[TD="width: 65, bgcolor: transparent"]H[/TD]
[TD="width: 65, bgcolor: transparent"]I[/TD]
[TD="width: 65, bgcolor: transparent"]J[/TD]
[TD="width: 65, bgcolor: transparent"]K[/TD]
[TD="width: 68, bgcolor: transparent"]L[/TD]
[TD="width: 68, bgcolor: transparent"]M[/TD]
[TD="width: 68, bgcolor: transparent"]N[/TD]
[TD="width: 68, bgcolor: transparent"]O[/TD]
[TD="width: 69, bgcolor: transparent"]P[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Table 1[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Week ending[/TD]
[TD="bgcolor: transparent, align: right"]06-Jan-19[/TD]
[TD="bgcolor: transparent, align: right"]13-Jan-19[/TD]
[TD="bgcolor: transparent, align: right"]20-Jan-19[/TD]
[TD="bgcolor: transparent, align: right"]27-Jan-19[/TD]
[TD="bgcolor: transparent, align: right"]03-Feb-19[/TD]
[TD="bgcolor: transparent, align: right"]10-Feb-19[/TD]
[TD="bgcolor: transparent, align: right"]17-Feb-19[/TD]
[TD="bgcolor: transparent, align: right"]24-Feb-19[/TD]
[TD="bgcolor: transparent, align: right"]03-Mar-19[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Work Week[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]2[/TD]
[TD="bgcolor: transparent"]3[/TD]
[TD="bgcolor: transparent"]4[/TD]
[TD="bgcolor: transparent"]5[/TD]
[TD="bgcolor: transparent"]6[/TD]
[TD="bgcolor: transparent"]7[/TD]
[TD="bgcolor: transparent"]8[/TD]
[TD="bgcolor: transparent"]9[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Area 1[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent"]7[/TD]
[TD="bgcolor: transparent"]14[/TD]
[TD="bgcolor: transparent"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]6[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Area 2[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]4[/TD]
[TD="bgcolor: transparent"]2[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]0[/TD]
[TD="bgcolor: transparent"]3[/TD]
[TD="bgcolor: transparent"]2[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]7[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]8[/TD]
[TD="bgcolor: transparent"]Example[/TD]
[TD="bgcolor: transparent"]Task[/TD]
[TD="bgcolor: transparent"]Area[/TD]
[TD="bgcolor: transparent"]Due Date[/TD]
[TD="bgcolor: transparent"]Work Week Due[/TD]
[TD="bgcolor: transparent, colspan: 2"]Proposed Work Week[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]9[/TD]
[TD="bgcolor: transparent"]Example 1[/TD]
[TD="bgcolor: transparent"]Task 1[/TD]
[TD="bgcolor: transparent"]Area 1[/TD]
[TD="bgcolor: transparent, align: right"]07-Jan-19[/TD]
[TD="bgcolor: transparent"]2[/TD]
[TD="bgcolor: transparent"]2[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]10[/TD]
[TD="bgcolor: transparent"]Example 2[/TD]
[TD="bgcolor: transparent"]Task 2[/TD]
[TD="bgcolor: transparent"]Area 1[/TD]
[TD="bgcolor: transparent, align: right"]11-Feb-19[/TD]
[TD="bgcolor: transparent"]7[/TD]
[TD="bgcolor: transparent"]5[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]11[/TD]
[TD="bgcolor: transparent"]Example 3[/TD]
[TD="bgcolor: transparent"]Task 3[/TD]
[TD="bgcolor: transparent"]Area 1[/TD]
[TD="bgcolor: transparent, align: right"]05-Feb-19[/TD]
[TD="bgcolor: transparent"]6[/TD]
[TD="bgcolor: transparent"]5[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]12[/TD]
[TD="bgcolor: transparent"]Example 4[/TD]
[TD="bgcolor: transparent"]Task 4[/TD]
[TD="bgcolor: transparent"]Area 2[/TD]
[TD="bgcolor: transparent, align: right"]27-Jan-19[/TD]
[TD="bgcolor: transparent"]4[/TD]
[TD="bgcolor: transparent"]4[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]13[/TD]
[TD="bgcolor: transparent"]Example 5[/TD]
[TD="bgcolor: transparent"]Task 5[/TD]
[TD="bgcolor: transparent"]Area 2[/TD]
[TD="bgcolor: transparent, align: right"]25-Feb-19[/TD]
[TD="bgcolor: transparent"]9[/TD]
[TD="bgcolor: transparent"]7[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

Hi,

Table 1 shows area status for respective work week, with week ending date indicated.
Excel to return "Proposed Work Week" for each task. Task must be completed before due date. Task can only be carried out when status is "0". Please see examples.

Thanks
 
Try this 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:84.59px;" /><col style="width:84.59px;" /><col style="width:84.59px;" /><col style="width:84.59px;" /><col style="width:111.21px;" /><col style="width:84.59px;" /><col style="width:84.59px;" /><col style="width:84.59px;" /><col style="width:84.59px;" /><col style="width:84.59px;" /></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></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="background-color:#ffff00; ">Table 1</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 style="background-color:#92d050; ">Week End</td><td style="background-color:#92d050; text-align:right; ">06/01/2019</td><td style="background-color:#92d050; text-align:right; ">13/01/2019</td><td style="background-color:#92d050; text-align:right; ">20/01/2019</td><td style="background-color:#92d050; text-align:right; ">27/01/2019</td><td style="background-color:#92d050; text-align:right; ">03/02/2019</td><td style="background-color:#92d050; text-align:right; ">10/02/2019</td><td style="background-color:#92d050; text-align:right; ">17/02/2019</td><td style="background-color:#92d050; text-align:right; ">24/02/2019</td><td style="background-color:#92d050; text-align:right; ">03/03/2019</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="font-weight:bold; ">Work week</td><td style="font-weight:bold; text-align:right; ">1</td><td style="font-weight:bold; text-align:right; ">2</td><td style="font-weight:bold; text-align:right; ">3</td><td style="font-weight:bold; text-align:right; ">4</td><td style="font-weight:bold; text-align:right; ">5</td><td style="font-weight:bold; text-align:right; ">6</td><td style="font-weight:bold; text-align:right; ">7</td><td style="font-weight:bold; text-align:right; ">8</td><td style="font-weight:bold; text-align:right; ">9</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >Area 1</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; ">0</td><td style="text-align:right; ">0</td><td style="text-align:right; ">1</td><td style="text-align:right; ">7</td><td style="text-align:right; ">14</td><td style="text-align:right; ">0</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >Area 2</td><td style="text-align:right; ">0</td><td style="text-align:right; ">4</td><td style="text-align:right; ">2</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; ">0</td><td style="text-align:right; ">3</td><td style="text-align:right; ">2</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</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 style="background-color:#ffff00; ">Table 2</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 style="background-color:#b6dde8; ">Example</td><td style="background-color:#b6dde8; ">Task</td><td style="background-color:#b6dde8; ">Area</td><td style="background-color:#b6dde8; ">Due Date</td><td style="background-color:#b6dde8; ">Work week due</td><td style="background-color:#b6dde8; ">Proposed</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 1</td><td >Task 1</td><td >Area 1</td><td style="text-align:right; ">07/01/2019</td><td style="text-align:right; ">2</td><td style="text-align:right; ">2</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 2</td><td >Task 2</td><td >Area 1</td><td style="text-align:right; ">11/02/2019</td><td style="text-align:right; ">7</td><td style="text-align:right; ">5</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 3</td><td >Task 3</td><td >Area 1</td><td style="text-align:right; ">05/02/2019</td><td style="text-align:right; ">6</td><td style="text-align:right; ">5</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; " >12</td><td >Example 4</td><td >Task 4</td><td >Area 2</td><td style="text-align:right; ">27/01/2019</td><td style="text-align:right; ">4</td><td style="text-align:right; ">4</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; " >13</td><td >Example 5</td><td >Task 5</td><td >Area 2</td><td style="text-align:right; ">25/12/2019</td><td style="text-align:right; ">9</td><td style="text-align:right; ">7</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 >Formula</td></tr><tr><td >F9</td><td >{=MAX(IF($B$3:$J$3<=E9,IF($A$4:$A$5=C9,IF($B$4:$J$5=0,$B$3:$J$3))))}</td></tr></table></td></tr></table>
 
Upvote 0
I'm glad to help you. Thanks for the feedback.
 
Upvote 0

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
Back
Top