This can be done using array formulas. Consider the following set of team data:
Book1 



 A  B  C  D 

1  First Name  Last Name  Team  Age 

2  Benjamin  Lestronge  Giants  24 

3  Cesar  Manuelito  Giants  24 

4  Letisha  Paeth  Eagles  25 

5  Kelvin  Gett  Cowboys  22 

6  Roy  France  Falcons  21 

7  Dorsey  Chesson  Eagles  26 

8  Jaqueline  Mcdivitt  Cowboys  21 

9  Newton  Silbaugh  Eagles  22 

10  Jennette  Vandervoort  Cowboys  26 

11  Rochel  Digerolamo  Colts  21 

12  Nadine  Sodergren  Eagles  22 

13  Ava  Brandewie  Colts  21 

14  Eleonor  Yoke  Eagles  23 

15  Adela  Voetberg  Falcons  26 

16  Tianna  Shuttlesworth  Cowboys  22 

17  Kerry  Dominquez  Giants  26 

18  Barney  Siler  Eagles  23 

19  Hector  Florek  Colts  26 

20  Pasquale  Croston  Falcons  26 

21  Colin  Hallett  Falcons  23 

22  Nisha  Foard  Falcons  22 

23  Rudy  Lagos  Falcons  24 

24  Max  Hutchinson  Giants  22 

25     

26     

27     

28     

29     

30     



The team sheet for the Falcons could then look like this:
The formula in cell B2 simply counts how many Falcons there are on the main page. This number is not used in the array formulas below it, it's for the user's information only. To build the matrix below, select from cell A5 down to as many members you ever expect any given team to have, plus a few. If you expect teams of twenty, I would select thirty rows so there is plenty of room for future expansion. Once you have the cells selected, enter this formula in the formula bar, and confirm it by hitting CONTROL + SHIFT + ENTER instead of the usual ENTER alone. This tells Excel to resolve the formula as an array:
=(INDEX(Sheet1!A:A,SMALL(IF(Sheet1!$C$2:$C$301=$B$1,ROW(Sheet1!$C$2:$C$301),99),ROW(INDIRECT("1:"&ROWS(Sheet1!$C$2:$C$301)))))&"")
This formula looks through the "Team" column on Sheet1 (Change that reference if your master page has a different sheet name), looking down through row 301 (farther than your current roster to capture future additions), and returns the Column A data (first names in your case) which correspond to "Falcons" entries only. They are returned in the same order they are listed in the Sheet1 worksheet. You may then use the fill handle at the bottom right corner of the array to drag the formulas to the right to populate columns B, C, and D.
Someday you may want to add more columns to the master sheet, maybe column E will be position, column F runs, column G hits, column H errors, etc.... You can drag the formulas to the right as many columns as you want, and the references will update.
You might notice in the snapshot of my spreadsheet that the formula in column C doesn't exactly match the others. Instead of:
=(INDEX(Sheet1!C:C,SMALL(IF(Sheet1!$C$2:$C$301=$B$1,ROW(Sheet1!$C$2:$C$301),99),ROW(INDIRECT("1:"&ROWS(Sheet1!$C$2:$C$301)))))&"")
you find this:
=(INDEX(Sheet1!C:C,SMALL(IF(Team=$B$1,ROW(Team),99),ROW(INDIRECT("1:"&ROWS(Team)))))&"")
I've created a named range on Sheet1 that expands and contracts to the exact number of rows for which there is team data. That named range means I will not have to change the array formulas on the team pages if my master roster grows beyond row 301. On Sheet1, select cell C1. Click on Insert, then Name, then Define... The name will default to the value in the selected cell, so it should already be called "Team". Enter the following formula in the "Refers to" block at the bottom of the dialog box, replacing what Excel put there:
=OFFSET(Sheet1!$C$1,1,0,COUNTA(Sheet1!$C:$C)1,1)
edit: I see that when I built the formulas, Excel inserted the Sheet2! reference to the samesheet cell references. In the explanatory text I have removed that, so you can use the formulas as presented in the text on a sheet of any name.