Display needed info on different worksheet

I'm just new in using VBA, I created a simple problem that I can't figure out how to solve in worksheet 2, I have 3 worksheets.

1. Players - Complete info of players

2. Members - When I select the team name, I'd like to display the info First Name, Last Name and Team only for the team name I've chosen. This should automatically change whenever I select a team name.

3. Team Codes - Tables I used for my vlookup and drop down list.

I have attached my file for your reference.

Thank you very much!

A5=1
A6=2
A7=3

B5:
=INDEX(Players!$B$1:$B$10, SMALL(IF(Players!$E$1:$E$10=$B$2, ROW($A$1:$A$10), ""), $A5))
(confirmed with CTRL-SHIFT-ENTER to activate the array, then copy down)

C5:
=INDEX(Players!$A$1:$A$10, SMALL(IF(Players!$E$1:$E$10=$B$2, ROW($A$1:$A$10), ""), $A5))
(confirmed with CTRL-SHIFT-ENTER to activate the array, then copy down)

D5:
=INDEX(Players!$D$1:$D$10, SMALL(IF(Players!$E$1:$E$10=$B$2, ROW($A$1:$A$10), ""), $A5))
(confirmed with CTRL-SHIFT-ENTER to activate the array, then copy down)


NOTE:
These array formulas are fine on small datasets. If your data is going to grow into the 100s or even 1000s, I'd have other approaches to suggest.

[edit - removed - see post 7]

@JB

sorry, for being such a novice, your formula is working great, i forgot to type the number on column A, now what if my data is in 100s? thank you very much again!

It's up to you if this works or not. I know the values are probably appearing, but you'll have to watch your sheet's "performance" with a bunch of these formulas active.

Does your sheet slow down perceptibly? If not, you're good.

If so, you'll have to consider adding "helper columns" to provide indexes we can use to sort these people without needing array formulas. Let us know.

==========
If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].


(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated)

I'm a doofus too as I misread JB's formula in the first place !

JB - I've removed my "other" post... too early !

@all
i was out of town for a while haven't replied again. thanks guys for your help!

@JB
i tried to add some names but when i change the team, it's not updating properly unlike the 1st one you did. the result should be updated when we add, delete or change the data. i attached my working file for your reference. thanks again!

just to remind us that our aim here is to display info on per team only. we don't want to see all teams in 1 page.

OK, added some stuff to make it more dynamic:

1) A4 - has a count of how many team members there are total for the chosen color
2) The numbers going down column A will add/remove themselves (down to row 30, copy that formula down further if needed)
3) The chart will now expand and contract with the numbers in column A (down to row 30, copy the formulas/formatting down further if needed)
4) Added some conditional formatting to the chart so even the borders match.
5) Chart now watching 100 rows of Players sheet.