Struggling to expand on this formula

[Sahil123]

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)))),"")

 

[Andrew Poulsom]

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]

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