# Struggling to expand on this formula

#### Sahil123

##### Well-known Member
Hi All,

I am struggling to expand this formula

I have a 2 drop down lists which i can pick a team and the week to give me all the data for that criteria.

If both the selection states All Teams and All Weeks then i want all the data which i have manged to do

Now what i want to include in there is
if i select All Teams but i wanted to select a week (not all weeks) then show me all the teams for that week

and vice versa

if i select All Weeks but i wanted to select a Team (not all Teams) then show me all the Weeks data for that Team

This is the formula i have got to so far

=IFERROR(INDEX(M\$9:M\$21,IF(AND(\$E\$6="All Teams",\$F\$6="All Weeks"),ROW(A1),SMALL(IF(--(\$K\$9:\$K\$21=\$E\$6)*(--(\$J\$9:\$J\$21=\$F\$6)),ROW(\$K\$9:\$K\$21)-ROW(\$K\$9)+1),ROWS(\$A\$1:A1)))),"")

### Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

#### Andrew Poulsom

##### MrExcel MVP
Does this work for you:

=IFERROR(INDEX(M\$9:M\$21,SMALL(IF((IF(\$E\$6="All Teams",1,\$K\$9:\$K\$21=\$E\$6)*IF(\$F\$6="All Weeks",1,\$J\$9:\$J\$21=\$F\$6)),ROW(\$K\$9:\$K\$21)-ROW(\$K\$9)+1),ROWS(\$A\$1:A1))),"")

confirmed with Ctrl+Shift+Enter?

#### Sahil123

##### Well-known Member
Thank You that worked

I wanted to go that extra and add another criteria

I wanted to add another drop down list to give me a selection to choose from say week 1 to week 3. So all the data from week 1 to week 3. I have conditional formatting to hide this 3RD DV (TO WEEK) if the 2nd DL said all weeks as
this should display all weeks however if anything other than all week is selected then i show the from DV to choose what weeks i want to view until however struggling to encorporate this extra DV

Replies
9
Views
359
Replies
5
Views
212
Replies
0
Views
158
Replies
26
Views
786
Replies
8
Views
239

1,191,125
Messages
5,984,786
Members
439,911
Latest member
dk73

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