Return Multiple Matches Duplicate Lookup Values

[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 B2 and the second match inside B3, third inside B4, 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>

 

[jorismoerings]

Hi,

take a look at this:

Excel 2016 (Windows) 64 bit
	A	B
1	Position	Name
2	PG	Russell Westbrook
3	PG	Isaiah Thomas
4	PG	John Wall
Sheet2

Cell Formulas
Range	Formula
B2	{=INDEX('all players'!$B$2:$B$7, MATCH(0, IF($A2='all players'!$A$2:$A$7, COUNTIF($B$1:$B1, 'all players'!$B$2:$B$7), ""), 0))}
Press CTRL+SHIFT+ENTER to enter array formulas.