Return value based on multiple column & row criteria

[Stormseed]

I was trying this since last couple of hours, I could not accomplish a solution. I guess it should be simple but I ain't able to find the solution !

Sheet 1 (Main):

<b>Main</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:153px;" /><col style="width:68px;" /><col style="width:66px;" /><col style="width:73px;" /><col style="width:64px;" /><col style="width:56px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /></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 >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></tr><tr style="height:49px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-weight:bold; font-size:8pt; ">Code</td><td style="font-weight:bold; ">Branches</td><td style="font-weight:bold; ">Product</td><td style="font-weight:bold; ">Populated for Date</td><td > </td><td style="font-weight:bold; ">Date Ended</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:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="font-size:8pt; "> </td><td > </td><td > </td><td style="text-align:right; ">Dec-07</td><td > </td><td style="text-align:right; ">Dec-07</td><td style="text-align:right; ">Dec-07</td><td style="text-align:right; ">Dec-07</td><td style="text-align:right; ">Dec-07</td><td style="text-align:right; ">Dec-07</td><td style="text-align:right; ">Dec-07</td><td style="text-align:right; ">Dec-07</td><td style="text-align:right; ">Dec-07</td><td style="text-align:right; ">Dec-07</td><td style="text-align:right; ">Dec-07</td><td style="text-align:right; ">Dec-07</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="font-size:8pt; "> </td><td > </td><td > </td><td style="text-align:right; ">Jun-08</td><td > </td><td style="text-align:right; ">Dec-07</td><td style="text-align:right; ">Jan-08</td><td style="text-align:right; ">Feb-08</td><td style="text-align:right; ">Mar-08</td><td style="text-align:right; ">Apr-08</td><td style="text-align:right; ">May-08</td><td style="text-align:right; ">Jun-08</td><td style="text-align:right; ">Jul-08</td><td style="text-align:right; ">Aug-08</td><td style="text-align:right; ">Sep-08</td><td style="text-align:right; ">Oct-08</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="font-size:8pt; "> </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:15px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="font-size:8pt; "> </td><td style="font-weight:bold; ">APJ</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:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td style="font-size:8pt; ">INCREMBUS</td><td >B0</td><td >EBG001</td><td style="text-align:right; ">500</td><td > </td><td > </td><td > </td><td > </td><td > </td><td style="text-align:right; ">500</td><td style="text-align:right; ">55555</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td style="font-size:8pt; ">INCDIRBUS</td><td >B0</td><td >EBG001</td><td style="text-align:right; ">119812077</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:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td style="font-size:8pt; ">INCBRKSEC</td><td >B0</td><td >EBG001</td><td style="text-align:right; ">119812077</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:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td style="font-size:8pt; ">INCDP</td><td >B0</td><td >EBG001</td><td style="text-align:right; ">0</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:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="font-size:8pt; ">INCMF</td><td >B0</td><td >EBG001</td><td style="text-align:right; ">972129</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:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >11</td><td style="font-size:8pt; ">INCIPO</td><td >B0</td><td >EBG001</td><td style="text-align:right; ">1265865</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:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="font-size:8pt; ">INCFDFI</td><td >B0</td><td >EBG001</td><td style="text-align:right; ">0</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:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >13</td><td style="font-size:8pt; ">INCPMS</td><td >B0</td><td >EBG001</td><td style="text-align:right; ">444179</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:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >14</td><td style="font-size:8pt; ">INCSTRPDT</td><td >B0</td><td >EBG001</td><td style="text-align:right; ">0</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 />Excel tables to the web - Excel Jeanie Html 4

Sheet 2 (Workout):

<b>Workout</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:91px;" /><col style="width:223px;" /><col style="width:105px;" /><col style="width:105px;" /><col style="width:105px;" /><col style="width:105px;" /><col style="width:106px;" /><col style="width:106px;" /><col style="width:107px;" /><col style="width:101px;" /><col style="width:47px;" /><col style="width:106px;" /></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></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="color:#ffffff; font-family:Times New Roman; text-align:center; ">v</td><td style="font-weight:bold; font-family:Times New Roman; "> </td><td style="color:#ff0000; font-family:Times New Roman; text-align:center; ">EBG001</td><td style="color:#ffffff; font-family:Times New Roman; "> </td><td style="color:#ffffff; font-family:Times New Roman; "> </td><td style="color:#ffffff; font-family:Times New Roman; "> </td><td style="color:#ffffff; font-family:Times New Roman; "> </td><td style="color:#ffffff; font-family:Times New Roman; "> </td><td style="color:#ffffff; font-family:Times New Roman; "> </td><td style="font-family:Times New Roman; "> </td><td style="font-family:Times New Roman; "> </td><td style="font-family:Times New Roman; "> </td></tr><tr style="height:20px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="color:#ffffff; font-family:Times New Roman; "> </td><td style="font-weight:bold; font-family:Times New Roman; "> </td><td style="font-weight:bold; font-family:Times New Roman; "> </td><td style="font-weight:bold; font-family:Times New Roman; "> </td><td style="font-family:Times New Roman; "> </td><td style="background-color:#ffff00; font-family:Times New Roman; text-align:right; ">Dec-07</td><td style="background-color:#ffff00; font-family:Times New Roman; text-align:right; ">May-08</td><td style="font-family:Times New Roman; "> </td><td style="font-family:Times New Roman; "> </td><td style="font-family:Times New Roman; "> </td><td style="font-family:Times New Roman; "> </td><td style="font-family:Times New Roman; "> </td></tr><tr style="height:20px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="color:#ffffff; font-family:Times New Roman; "> </td><td style="font-weight:bold; font-family:Times New Roman; font-size:9pt; "> </td><td colspan="10" style="font-weight:bold; font-family:Times New Roman; font-size:9pt; "> </td></tr><tr style="height:25px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="color:#ff0000; font-family:Times New Roman; ">B0</td><td style="background-color:#ffff00; font-weight:bold; font-family:Times New Roman; font-size:14pt; text-align:center; ">APJ</td><td style="background-color:#c0c0c0; font-weight:bold; font-family:Times New Roman; font-size:8pt; "> </td><td style="background-color:#c0c0c0; font-weight:bold; font-family:Times New Roman; font-size:8pt; "> </td><td style="background-color:#c0c0c0; font-weight:bold; font-family:Times New Roman; font-size:8pt; "> </td><td style="background-color:#c0c0c0; font-weight:bold; font-family:Times New Roman; font-size:8pt; "> </td><td style="background-color:#c0c0c0; font-weight:bold; font-family:Times New Roman; font-size:8pt; "> </td><td style="background-color:#c0c0c0; font-weight:bold; font-family:Times New Roman; font-size:8pt; "> </td><td style="background-color:#c0c0c0; font-weight:bold; font-family:Times New Roman; font-size:8pt; "> </td><td style="background-color:#c0c0c0; font-weight:bold; font-family:Times New Roman; font-size:8pt; "> </td><td style="background-color:#c0c0c0; font-family:Times New Roman; "> </td><td style="background-color:#c0c0c0; font-weight:bold; font-family:Times New Roman; font-size:8pt; "> </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >10</td><td style="color:#ffffff; font-family:Times New Roman; "> </td><td style="font-weight:bold; font-family:Times New Roman; "> </td><td style="font-weight:bold; font-family:Times New Roman; "> </td><td style="font-weight:bold; font-family:Times New Roman; "> </td><td style="font-weight:bold; font-family:Times New Roman; "> </td><td style="font-weight:bold; font-family:Times New Roman; "> </td><td style="font-weight:bold; font-family:Times New Roman; "> </td><td style="font-weight:bold; font-family:Times New Roman; "> </td><td style="font-weight:bold; font-family:Times New Roman; "> </td><td style="font-weight:bold; font-family:Times New Roman; "> </td><td style="font-weight:bold; font-family:Times New Roman; "> </td><td style="font-weight:bold; font-family:Times New Roman; "> </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >12</td><td style="color:#ffffff; font-family:Times New Roman; "> </td><td style="background-color:#c0c0c0; font-weight:bold; font-family:Times New Roman; "> </td><td style="font-family:Times New Roman; "> </td><td style="font-family:Times New Roman; "> </td><td style="font-family:Times New Roman; "> </td><td style="font-family:Times New Roman; "> </td><td style="font-family:Times New Roman; "> </td><td style="font-family:Times New Roman; "> </td><td style="font-family:Times New Roman; "> </td><td style="font-family:Times New Roman; "> </td><td style="font-family:Times New Roman; "> </td><td style="font-family:Times New Roman; "> </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >16</td><td style="color:#ff0000; font-family:Times New Roman; ">INCREMBUS</td><td style="font-family:Times New Roman; "> </td><td style="background-color:#ffff00; font-family:Times New Roman; text-align:right; ">                              -   </td><td style="font-family:Times New Roman; "> </td><td style="font-family:Times New Roman; "> </td><td style="font-family:Times New Roman; "> </td><td style="font-family:Times New Roman; "> </td><td style="font-family:Times New Roman; "> </td><td style="font-family:Times New Roman; "> </td><td style="font-family:Times New Roman; "> </td><td style="font-family:Times New Roman; "> </td><td style="font-family:Times New Roman; "> </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >20</td><td style="color:#ffffff; font-family:Times New Roman; "> </td><td style="font-family:Times New Roman; "> </td><td style="font-family:Times New Roman; "> </td><td style="font-family:Times New Roman; "> </td><td style="font-family:Times New Roman; "> </td><td style="font-family:Times New Roman; "> </td><td style="font-family:Times New Roman; "> </td><td style="font-family:Times New Roman; "> </td><td style="font-family:Times New Roman; "> </td><td style="font-family:Times New Roman; "> </td><td style="font-family:Times New Roman; "> </td><td style="font-family:Times New Roman; "> </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >22</td><td style="color:#ffffff; font-family:Times New Roman; "> </td><td style="font-family:Times New Roman; "> </td><td style="font-family:Times New Roman; "> </td><td style="font-family:Times New Roman; "> </td><td style="font-family:Times New Roman; "> </td><td style="font-family:Times New Roman; "> </td><td style="font-family:Times New Roman; "> </td><td style="font-family:Times New Roman; "> </td><td style="font-family:Times New Roman; "> </td><td style="font-family:Times New Roman; "> </td><td style="font-family:Times New Roman; "> </td><td style="font-family:Times New Roman; "> </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >24</td><td style="color:#ffffff; font-family:Times New Roman; "> </td><td style="font-family:Times New Roman; "> </td><td style="font-family:Times New Roman; "> </td><td style="font-family:Times New Roman; "> </td><td style="font-family:Times New Roman; "> </td><td style="font-family:Times New Roman; "> </td><td style="font-family:Times New Roman; "> </td><td style="font-family:Times New Roman; "> </td><td style="font-family:Times New Roman; "> </td><td style="font-family:Times New Roman; "> </td><td style="font-family:Times New Roman; "> </td><td style="font-family:Times New Roman; "> </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >26</td><td style="color:#ffffff; font-family:Times New Roman; "> </td><td style="font-family:Times New Roman; "> </td><td style="font-family:Times New Roman; "> </td><td style="font-family:Times New Roman; "> </td><td style="font-family:Times New Roman; "> </td><td style="font-family:Times New Roman; "> </td><td style="font-family:Times New Roman; "> </td><td style="font-family:Times New Roman; "> </td><td style="font-family:Times New Roman; "> </td><td style="font-family:Times New Roman; "> </td><td style="font-family:Times New Roman; "> </td><td style="font-family:Times New Roman; "> </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >31</td><td style="color:#ffffff; font-family:Times New Roman; "> </td><td style="font-family:Times New Roman; "> </td><td style="font-family:Times New Roman; "> </td><td style="font-family:Times New Roman; "> </td><td style="font-family:Times New Roman; "> </td><td style="font-family:Times New Roman; "> </td><td style="font-family:Times New Roman; "> </td><td style="font-family:Times New Roman; "> </td><td style="font-family:Times New Roman; "> </td><td style="font-family:Times New Roman; "> </td><td style="font-family:Times New Roman; "> </td><td style="font-family:Times New Roman; "> </td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >35</td><td style="color:#ffffff; font-family:Times New Roman; "> </td><td style="background-color:#c0c0c0; font-weight:bold; font-family:Times New Roman; "> </td><td style="background-color:#c0c0c0; font-weight:bold; font-family:Times New Roman; "> </td><td style="background-color:#c0c0c0; font-weight:bold; font-family:Times New Roman; "> </td><td style="background-color:#c0c0c0; font-weight:bold; font-family:Times New Roman; "> </td><td style="background-color:#c0c0c0; font-weight:bold; font-family:Times New Roman; "> </td><td style="background-color:#c0c0c0; font-weight:bold; font-family:Times New Roman; "> </td><td style="background-color:#c0c0c0; font-weight:bold; font-family:Times New Roman; "> </td><td style="background-color:#c0c0c0; font-weight:bold; font-family:Times New Roman; "> </td><td style="background-color:#c0c0c0; font-weight:bold; font-family:Times New Roman; "> </td><td style="background-color:#c0c0c0; font-weight:bold; font-family:Times New Roman; "> </td><td style="background-color:#c0c0c0; font-weight:bold; font-family:Times New Roman; "> </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>Spreadsheet Formulas</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 >C16</td><td >{=IF(AND<span style=' color:008000; '>(Main!G2:R2=Workout!F2,Main!G3:R3=Workout!G2)</span>,SUM<span style=' color:008000; '>(IF<span style=' color:#0000ff; '>(Main!A5:A14200=Workout!A16,IF<span style=' color:#ff0000; '>(Main!C5:C14200=Workout!C1,IF<span style=' color:#804000; '>(Main!B5:B14200=Workout!A4,Main!G5:R14200)</span>)</span>)</span>)</span>,0)}</td></tr></table></td></tr><tr><td ><span style=' font-family:Arial,Arial; font-size:9pt; font-weight:bold;'>Formula Array:</span><span style=' font-family:Arial,Arial; font-size:9pt;'><br />Produce enclosing </span><span style=' font-family:Arial,Arial; font-size:9pt; font-weight:bold;'>{ }</span><span style=' font-family:Arial,Arial; font-size:9pt;'> by entering<br />formula with CTRL+SHIFT+ENTER!</span></td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4

I just want to be able to display the value 55555 in Sheet 2 (Workout) which is in cell L6 on Sheet 1 (Main) based on a match with the dates in cells F2 & G2 *and* branch, Code and Product. If I do a SUM(IF()) or SUMPRODUCT() it would return the sum of values in K6 & L6 which I do not want. I think there is INDEX() involved here but cannot get my head through it

Help me out guys, please !!

 

[Stormseed]

anyone, please ??

 

[Stormseed]

I guess someone would have got a couple of tips ?

 

[Stormseed]

*bump*

 

[Stormseed]

*bump*

 

[Stormseed]

*bump*

 

[Stormseed]

Its a pity NOBODY helped me here.

http://www.ozgrid.com/forum/showthread.php?p=456390#post456390