Return Multiple Matches Using Index Match.

[Promptbeef]

Hi there,

I'm looking for a formula to match a value within an array and return the results found.

I know how to you use vlookup/match/index formula's, but I can only return the first match found. I would like to return the first match in cell A1 and the second match inside A2, third inside A3, etc.

So when the first match is found and placed inside A1 then the formula inside A2 should ignore the first match that is already found and show the next match value.

I want to look up "PG" from the all players page and have only the PG appear without #n/a Errors

this is the formula im using {=INDEX('All Players'!B2:B500,MATCH("PG"&'All Players'!B2,'All Players'!A2:A500&'All Players'!B2:B500,0))} and am getting #n/a's between correct matches

I would like to get this done with a formula and not with the autofilter option inside Excel.
This is a basketball table and is a small sample size the actual tables i'm using have over 200 players.

Example of what i want:

1st Page: PGs

Position	Name
PG	Russell Westbrook
PG	Isaiah Thomas
PG	John Wall

<tbody>
</tbody>

2nd Page: All Players

Position	Name
PG	Russell Westbrook
SG	James Harden
SF	Lebron James
PG	Isaiah Thomas
C	Demarcus Cousins
PG	John Wall

<tbody>
</tbody>

 

[Promptbeef]

Correction so when match is found i want it placed in b2,b3,b4 etc.

 

[Aladin Akyurek]

Sheet2 (2nd page)

Row\Col	A​	B​
1​	Position	Name
2​	PG	Russell Westbrook
3​	SG	James Harden
4​	SF	Lebron James
5​	PG	Isaiah Thomas
6​	C	Demarcus Cousins
7​	PG	John Wall

In what follows, named ranges, that is, Position and Name are used.

Sheet1 (1st page)

Row\Col	A​	B​
1​	PG	3​
2​	Position	Player
3​	PG	Russell Westbrook
4​	PG	Isaiah Thomas
5​	PG	John Wall
6​
7​

In b1 just enter:

=COUNTIFS(Position,A1)

In A3 just enter and copy down:

=IF($B3="","",$A$1)

In B3 control+shift+enter, not just enter, and copy down:

=IF(ROWS($B$3:B3)>$B$1,"",INDEX(Name,SMALL(IF(Position=$A$1,ROW(Name)-ROW(INDEX(Name,1,1))+1),ROWS($B$3:B3))))

 

[Promptbeef]

Hey thanks for the reply but not quite what i wanted. I want to leave the spreadsheet as is without adding an extra row. Also is there just something i can add to the formula i was using to delete or not include #n/a, without leaving a blank space? formul

example of how my formula looks.

Position	Name
pg	Russell Westbrook
	#
pg	Chris Paul
	#

<tbody>
</tbody>