Get a List of Names

[gtd526]

Hello,
I'm trying to get a list of names.
I've tried index,match and vlookup, but how can you 'go to next listing'?
I should have 3 names, 3 names, 2 names. Use either formula or one of your own.
thank you.

NBA.xlsm
	A	B	C	D
2	Team:	POR	ATL	PHX
3	Injuries:	3	3	2
4	Who:	Zach Collins	Bogdan Bogdanović	Cameron Payne
5		Zach Collins	Bogdan Bogdanović	Cameron Payne
Injuries

Cell Formulas
Range	Formula
B2:D2	B2	=LOOKUP(2, 1/((COUNTIF($B$2:B2,Favs!$A$4:$A$29)=0)*(Favs!$A$4:$A$29<>"")),Favs!$A$4:$A$29)
B3:D3	B3	=COUNTIF('Injury List'!$A:$A,B2)
B4:D4	B4	=INDEX('Injury List'!$B:$B,MATCH(Injuries!B$2,'Injury List'!$A:$A,0))
B5:D5	B5	=VLOOKUP(B$2,'Injury List'!$A:$B,2,0)

 

[sandy666]

post representative source data and expected result

 

[gtd526]

post representative source data and expected result

It's another worksheet of data for source data. I didn't want to overwhelm the post data. That's why I only displayed the formulas.
I may have found a solution for the issue.
Thanks for the reply.

NBA.xlsm
	A	B	C	D
2	Team:	POR	ATL	PHX
3	Injuries:	3	3	2
4	Who:	Zach Collins	Bogdan Bogdanović	Cameron Payne
5		CJ Elleby	Kris Dunn	Jalen Smith
6		Jusuf Nurkić	Danilo Gallinari	#NUM!
7		#NUM!	#NUM!	#NUM!
8		#NUM!	#NUM!	#NUM!
Injuries

Cell Formulas
Range	Formula
B2:D2	B2	=LOOKUP(2, 1/((COUNTIF($B$2:B2,Favs!$A$4:$A$29)=0)*(Favs!$A$4:$A$29<>"")),Favs!$A$4:$A$29)
B3:D3	B3	=COUNTIF('Injury List'!$A:$A,B2)
B4	B4	=INDEX('Injury List'!$B$3:$B$200,SMALL(IF($B$2='Injury List'!$A$3:$A$200,ROW('Injury List'!$A$3:$A$200)-ROW('Injury List'!$A$3)+1),1))
C4	C4	=INDEX('Injury List'!$B$3:$B$200,SMALL(IF($C$2='Injury List'!$A$3:$A$200,ROW('Injury List'!$A$3:$A$200)-ROW('Injury List'!$A$3)+1),1))
D4	D4	=INDEX('Injury List'!$B$3:$B$200,SMALL(IF($D$2='Injury List'!$A$3:$A$200,ROW('Injury List'!$A$3:$A$200)-ROW('Injury List'!$A$3)+1),1))
B5	B5	=INDEX('Injury List'!$B$3:$B$200,SMALL(IF($B$2='Injury List'!$A$3:$A$200,ROW('Injury List'!$A$3:$A$200)-ROW('Injury List'!$A$3)+1),2))
C5	C5	=INDEX('Injury List'!$B$3:$B$200,SMALL(IF($C$2='Injury List'!$A$3:$A$200,ROW('Injury List'!$A$3:$A$200)-ROW('Injury List'!$A$3)+1),2))
D5	D5	=INDEX('Injury List'!$B$3:$B$200,SMALL(IF($D$2='Injury List'!$A$3:$A$200,ROW('Injury List'!$A$3:$A$200)-ROW('Injury List'!$A$3)+1),2))
B6	B6	=INDEX('Injury List'!$B$3:$B$200,SMALL(IF($B$2='Injury List'!$A$3:$A$200,ROW('Injury List'!$A$3:$A$200)-ROW('Injury List'!$A$3)+1),3))
C6	C6	=INDEX('Injury List'!$B$3:$B$200,SMALL(IF($C$2='Injury List'!$A$3:$A$200,ROW('Injury List'!$A$3:$A$200)-ROW('Injury List'!$A$3)+1),3))
D6	D6	=INDEX('Injury List'!$B$3:$B$200,SMALL(IF($D$2='Injury List'!$A$3:$A$200,ROW('Injury List'!$A$3:$A$200)-ROW('Injury List'!$A$3)+1),3))
B7	B7	=INDEX('Injury List'!$B$3:$B$200,SMALL(IF($B$2='Injury List'!$A$3:$A$200,ROW('Injury List'!$A$3:$A$200)-ROW('Injury List'!$A$3)+1),4))
C7	C7	=INDEX('Injury List'!$B$3:$B$200,SMALL(IF($C$2='Injury List'!$A$3:$A$200,ROW('Injury List'!$A$3:$A$200)-ROW('Injury List'!$A$3)+1),4))
D7	D7	=INDEX('Injury List'!$B$3:$B$200,SMALL(IF($D$2='Injury List'!$A$3:$A$200,ROW('Injury List'!$A$3:$A$200)-ROW('Injury List'!$A$3)+1),4))
B8	B8	=INDEX('Injury List'!$B$3:$B$200,SMALL(IF($B$2='Injury List'!$A$3:$A$200,ROW('Injury List'!$A$3:$A$200)-ROW('Injury List'!$A$3)+1),5))
C8	C8	=INDEX('Injury List'!$B$3:$B$200,SMALL(IF($C$2='Injury List'!$A$3:$A$200,ROW('Injury List'!$A$3:$A$200)-ROW('Injury List'!$A$3)+1),5))
D8	D8	=INDEX('Injury List'!$B$3:$B$200,SMALL(IF($D$2='Injury List'!$A$3:$A$200,ROW('Injury List'!$A$3:$A$200)-ROW('Injury List'!$A$3)+1),5))
Press CTRL+SHIFT+ENTER to enter array formulas.