Match and Return Multiple Values Vertically

[gleichmang]

Try to use a constant value on 2 sheets to return multiple corresponding results....

Sheet 1 =Roster
(Column A = Team Name)
(Column B = Players Name)

Sheet 2 = Scoresheet

End goal......
Team Name is located in cell D6 on 'scoresheet, looking for formula to reference column A of Sheet 1 (Roster) and populate all values for corresponding value of column B (Players Name)

Basically.... need a formula to pull in all players names based off of the team they are assigned to

 

[lrobbo314]

What version of Excel are you using?

 

[lrobbo314]

Book1
	A	B	C	D	E	F
1	Team	Name		Team:	Biscuit Bs
2	Biscuit Bs	Tim			2016	365
3	Biscuit Bs	Jim			Player Names	Player Names
4	Biscuit Bs	John			Tim	Tim
5	Biscuit Bs	Tom			Jim	Jim
6	Apple As	Bill			John	John
7	Apple As	Bob			Tom	Tom
8	Apple As	Carl
9	Apple As	Steve
Sheet11

Cell Formulas
Range	Formula
F4:F7	F4	=FILTER($B$2:$B$9,$A$2:$A$9=$E$1)
E4:E9	E4	=IFERROR(INDEX($B$2:$B$9,AGGREGATE(15,6,(1/($A$2:$A$9=$E$1))*ROW($A$2:$A$9)-ROW($A$2)+1,ROW(A1))),"")
Dynamic array formulas.

 

[gleichmang]

What version of Excel are you using?

2021