Assistance with Dynamic Lists

Question

I'm trying to create a dynamic list based on a set criteria. I have a list of Agents, and a list of Team Codes and I want to be able to "select" a team code and "extract" the list of agents for said team based on the criteria.

It's really not much more complicated (as far as the data is concerned) than this:

Team          Agent
Team1         Him
Team1         Her
Team2         Me
Team2         You

and I select Team1 and get Him and Her in a list, or Team2 and get Me and You in a list.

Answer 1

I assume you want to get these via a formula. You can do this with a version of the INDEX-SMALL pattern.

Picture for ranges

The formula in F2 is an array formula (enter with CTRL+SHIFT+ENTER). It needs to be copied down far enough to get all your matches. I overdid it in the picture above. The input is taken from cell E3

=INDEX($C$3:$C$6,SMALL(IF($B$3:$B$6=$E$3,ROW($B$3:$B$6)-ROW($B$3)+1), ROW(1:1)))

You can see this related post which has a fuller explanation of how that formula works.

If you do not want the errors, wrap the formula in an IFERROR(..., "").

Answer 2

Thanks for the assistance guys.

What I actually wound up using was an array formula that worked like a charm.

{=IF(ROWS('KPI DASHBOARD STATS'!K$3:K3)<=$M$2,INDEX(INDIRECT(O$29),SMALL(IF(MKPI_TEAM_CODE=$L$2,ROW(MKPI_TEAM_CODE)-ROW('KPI DASHBOARD STATS'!$K$3)+1),ROWS('KPI DASHBOARD STATS'!K$3:K3))),"")}

ROWS('KPI DASHBOARD STATS'!K$3:K3)<=$M$2
This counts the number of rows (where they are) in respect to the "count" of my agents
INDEX(INDIRECT(O$29),SMALL(IF(MKPI_TEAM_CODE=$L$2,ROW(MKPI_TEAM_CODE)-ROW('KPI DASHBOARD STATS'!$K$3)+1),ROWS('KPI DASHBOARD STATS'!K$3:K3))),"")
This part indexes and matches based on the criteria.

Answer 3

I realized that the question asks for a formula to generate a list, however since there is no mention of the final use for such a list, so I wonder if a PivotTable would fit for your purpose. (see below samples)