# Multiple Criteria/Multiple Results

#### mar21n

##### New Member
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

### Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

#### DonkeyOte

##### MrExcel MVP
Formula wise - one possibility:

Excel Workbook
AB
1Week43
2ColleagueA6
3ColleagueC5
4ColleagueD8
5
6
7
8
9
10
Sheet2

Note the Array entry for A2.

#### mar21n

##### New Member
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

##### New Member
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

##### New Member
interesting information!!! thank you!!!

#### Sandeep Warrier

##### Well-known Member

<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />

#### mar21n

##### New Member
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.

Replies
4
Views
774
Replies
4
Views
471
Replies
1
Views
317
Replies
2
Views
261
Replies
25
Views
1K

1,190,782
Messages
5,982,883
Members
439,803
Latest member
sushilneupane

### 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.

### Which adblocker are you using?

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

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