Multiple Criteria/Multiple Results

[mar21n]

Hi there. I'm new to the forum and was wondering if anyone would be able to help me out with my spreadsheet. I am using Excel 2002.

Sheet 1 looks like this.

___A_______B__________C__________D___________E____
1 Name__ColleageA__ColleagueB__ColleagueC__ColleagueD
2 Week1____7______________________8________________
3 Week2________________5__________6___________5____
4 Week3____5______________________9________________
5 Week4____6______________________5___________8____


Sheet 2, i would like to enter a Week# in cell A1 and return all the Colleagues who have a number greater than zero in the cells below along with their number in column B. For example if i entered Week4 in cell A1 the results I would expect to see are.

_____A_______B__
1 Week4
2 ColleagueA__6__
3 ColleagueC__5__
4 ColleagueD__8__
5

I have tried various combinations of MATCH and INDEX however I just can't get the formulas to work in an easy way. Any help or guidance would be appreciated. I hope i have provided enough detail.

Many Thanks

 

[DonkeyOte]

Formula wise - one possibility:

Excel Workbook
	A	B
1	Week4	3
2	ColleagueA	6
3	ColleagueC	5
4	ColleagueD	8
5
6
7
8
9
10
Sheet2

Note the Array entry for A2.

 

[mar21n]

Thankyou so so much. Was not expecting an answer so soon. Been working on this for a couple of weeks now without getting there. Just tried and it works great.

Very much appreciated, thanks again.

 

[mar21n]

I wonder if anyone would be able to help me with the next stage of this. Sheet 1 now looks like this, with SALES WEEKS added.

Sheet 2, I would like to enter a WEEK# in cell A1 and the corresponding SALES WEEK# in A2 and return the COLLEAGUES who have no values in their WEEK#, but have a value greater than zero in their SALES WEEK# in the cells below along with their number in column B. For example if I entered WEEK <?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-comffice:smarttags" /><st1:metricconverter ProductID="3 in">3 in</st1:metricconverter> A1 and SALES WEEK <st1:metricconverter ProductID="3 in">3 in</st1:metricconverter> A2 the results I would expect to see are….

I need to make sure they have no values in WEEK 3 first so I cannot just search for the coleagues who have a a value greater than zero in SALES WEEK 3. Any help would be most appreciated.

 

[pumpon3]

interesting information!!! thank you!!!

 

[Sandeep Warrier]

How about

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="10px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;;">Name</td><td style="font-weight: bold;;">Colleague A</td><td style="font-weight: bold;;">Colleague B</td><td style="font-weight: bold;;">Colleague C</td><td style="font-weight: bold;;">Colleague D</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Week 1</td><td style="text-align: right;;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;">8</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">Week 2</td><td style="text-align: right;;"></td><td style="text-align: right;;">5</td><td style="text-align: right;;">6</td><td style="text-align: right;;">5</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">Week 3</td><td style="text-align: right;;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">Week 4</td><td style="text-align: right;;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;">5</td><td style="text-align: right;;">8</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">Sales Week 1</td><td style="text-align: right;;"></td><td style="text-align: right;;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">Sales Week 2</td><td style="text-align: right;;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">Sales Week 3</td><td style="text-align: right;;"></td><td style="text-align: right;;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;">7</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">Sales Week 4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="font-weight: bold;;">Week 3</td><td style="font-weight: bold;;">Count</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="font-weight: bold;;">Sales Week 3</td><td style="font-weight: bold;text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style=";">Colleague B</td><td style="text-align: right;;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style=";">Colleague D</td><td style="text-align: right;;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style=";"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B14</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">(<font color="Red">$A$2:$A$5=$A$13</font>)*(<font color="Red">$A$6:$A$9=$A$14</font>)*(<font color="Red">LEN(<font color="Green">$B$2:$E$5</font>)=0</font>)*(<font color="Red">$B$6:$E$9>0</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">B15</th><td style="text-align:left">=IF(<font color="Blue">LEN(<font color="Red">A15</font>)>0,INDEX(<font color="Red">$B$6:$E$9,MATCH(<font color="Green">$A$14,$A$6:$A$9,0</font>),MATCH(<font color="Green">$A15,$B$1:$E$1,0</font>)</font>),""</font>)</td></tr></tbody></table></td></tr></table><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">A15</th><td style="text-align:left">{=IF(<font color="Blue">ROWS(<font color="Red">$A$14:A14</font>)<=$B$14,INDEX(<font color="Red">$B$1:$E$1,SMALL(<font color="Green">IF(<font color="Purple">(<font color="Teal">LEN(<font color="#FF00FF">INDEX(<font color="Navy">$B$2:$E$5,MATCH(<font color="Blue">$A$13,$A$2:$A$5,0</font>),0</font>)=0</font>)</font>)*(<font color="Teal">INDEX(<font color="#FF00FF">$B$6:$E$9,MATCH(<font color="Navy">$A$14,$A$6:$A$9,0</font>),0</font>)>0</font>),COLUMN(<font color="Teal">$B$1:$E$1</font>)-COLUMN(<font color="Teal">$B$1</font>)+1</font>),ROWS(<font color="Purple">$A$14:A14</font>)</font>)</font>),""</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />

 

[mar21n]

Thankyou Sandeep, that works great. Going to go away and read up on this LEN function as I am not familair with it. Could be useful in the future. Once again thankyou for your assistance.