Remove #n/a when Index Match

[Promptbeef]

Hi this is my formula {=INDEX('All Players'!B2:B500,MATCH("PG"&'All Players'!B2,'All Players'!A2:A500&'All Players'!B2:B500,0))}

It returns the information but in between each correct cell I get #n/a

I'm moving all "PG" from one sheet name all players to a second sheet named PG. My formula brings back all players but only displays the ones that with PG. How can I change the formula so it only brings back all PG players and no #N/A?

 

[billgyrotech]

Maybe this:

=IFERROR(INDEX('All Players'!B2:B500,MATCH("PG"&'All Players'!B2,'All Players'!A2:A500&'All Players'!B2:B500,0)),"")

 

[Promptbeef]

Hi thanks for the reply this just hides the n/a. What i asking for is to not even bring that information back.

what it looks like
[TABLE="width: 500"]
<tbody>[TR]
[TD]Postition[/TD]
[TD]Name[/TD]
[/TR]
[TR]
[TD]PG[/TD]
[TD]Russell Westbrook[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]*blank*[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]*Blank*[/TD]
[/TR]
[TR]
[TD]PG[/TD]
[TD]John wall[/TD]
[/TR]
</tbody>[/TABLE]

what i want it to look like
[TABLE="width: 500"]
<tbody>[TR]
[TD]position[/TD]
[TD]name[/TD]
[/TR]
[TR]
[TD]PG[/TD]
[TD]Russell westbrook[/TD]
[/TR]
[TR]
[TD]PG[/TD]
[TD]John wall[/TD]
[/TR]
[TR]
[TD]PG[/TD]
[TD]Kyrie[/TD]
[/TR]
</tbody>[/TABLE]

 

[SQUIDD]

Hi

Perhaps advanced filter?

Or, would you be happy to do it in code rather than a formula??

Dave

 

[Promptbeef]

Hey dave, If code can solve this issue than I will be happy doing it in code.

 

[SQUIDD]

Ok

Would it be acceptable to make the code filter your data for the word PG in column A.
Then copy coulumn A:B filtered data.
then paste it to you new sheet.
then remove the filter??

If so, what is the sheet called where you want the data.

Dave

 

[Promptbeef]

Well, I already filter for PG in the main sheet and just copy and paste it but this becomes tedious because I have to do it every day. And if I make changes then I have to re-copy and paste. So I wanted a formula like Vlookup that will auto update and changes. That is why I used index match. I wish i could upload my document so you can take a look its seems like a simple fix but I can't figure it out as I don't have too much experience.

 

[SQUIDD]

Ok

But the code below filters the data for you when you execute it.
But it referrs to sheet 1 as the sheet where you want the data to go.
Also, is i dont know what your data set looks like, it may copy more data than you require if "all players" has several columns
But all of this can be modified easily as and when know.
The could could also be modified to happen automatically upon detecting certian data has changed or be linked to a button.

If you happen to try/use this, please do it on a copy of your workbook otherwise it may paste over data you need to keep.

Sub FILTER_DATA()

Application.ScreenUpdating = False

    With Sheets("all players")
        .Columns("A:B").AutoFilter
        .Range("A:B").AutoFilter Field:=1, Criteria1:="PG"
        .AutoFilter.Range.Copy
    End With
    
    With Sheets("Sheet1")
        .Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
    End With
        
    With Sheets("all players")
        .Columns("A:B").AutoFilter
    End With

Application.ScreenUpdating = True

End Sub

 

[FDibbins]

Just so Im clear, you want it to NOT pull back those rows that don't match the criteria?

=iferror(INDEX('All Players'!$B:$B,small(if(('All Players'!A2:A500="PG")*('All Players'!B2:B500='All Players'!B2),row('All Players'!$A$2:$A$500)),rows($A$1:A1))),"")
entered using CTRL SHIFT ENTER, not just enter

 

[Promptbeef]

@FDibbins correct I want it to not pull back rows that don't meet criteria the formula you gave me produces all blank squares. I have Provided a link from my google drive so you guys can please take a look at my spreadsheet and try to solve it using a formula.

https://drive.google.com/open?id=0B3MgtgmXWaeobkVPWXZzdkJCa0U

@Squidd the code isnt providing the results i want i have posted a link to my excel worksheet please take look if you can.