Separating an individual list from one main list (Basketball Example)

What I would like to do in the attached document is list all players from the "Players" tab in the corresponding team's roster tab. The teams need to be able to find the players regardless of their order, if possible. It would also need to be able to update the individual tabs when a new player is added to the "Players" tab.

Here is my example file:
basketball rosters example.xlsx

I feel like it is the same basic concept as Gooford's example here:
http://www.excelforum.com/excel-gene...11#post3144711

Thanks!

Thank you for your quick reply.

I paste that into C3 and Ctrl+Shift+Enter and get "AJ Price," which is good. However, I can only seem to either get one row to work or have every cell come back with "AJ Price." Using the formula in the 2nd and 3rd team tabs also produced results that should have come on the 1st team's tab. I could really just get away with only listing player names, so if there is another way that only returns one column of data that would be just as helpful. Or, there is every chance I'm doing something wrong with your original directions.

Thanks again.

This is extremely helpful and you have solved my problem. Thank you very much.