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?

 

[FDibbins]

Did you enter my formula using CTRL SHIFT ENTER, not just enter?
If done correctly, it will put {} around the formula

 

[SQUIDD]

Hi promptbeef

The code below pasted into the worksheet code(right click on sheet, "all players" and click view code, paste the below into there)

change any data in column A in "all players" and press enter will trigger the filter

Everytime data is changed in column A in "all players" the code filters your data.
Im not sure what you would like to trigger the filter, so i used column a A change

DO IT ON A COPY OF YOUR WORKSHEET PLEASE.

Im not sure of how to do what you require with formula, but im sure it is possible.

Private Sub Worksheet_Change(ByVal Target As Range)

    If Not Intersect(Target, Range("A1:A200")) Is Nothing Then
    
        Application.ScreenUpdating = False
        
            With Sheets("PG")
                .Range("A:B").ClearContents
            End With
        
            With Sheets("all players")
                .Columns("A:B").AutoFilter
                .Range("A:B").AutoFilter Field:=1, Criteria1:="PG"
                .AutoFilter.Range.Copy
            End With
                
            With Sheets("PG")
                .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 If
End Sub

 

[SQUIDD]

Hi Ford

I downloaded the file, but your formula seems to only work if "PG" is found in a2.

although my code works for me, I think formula would be the best way to go.



Dave

 

[FDibbins]

QUIDD thanks for the input. I am not able (allowed) to go to file-hosting sites.

I based my suggestion on the initial provided formula, it looked like they wanted to pull out the rows with "PG"?

 

[Promptbeef]

@FDibbins yes i entered it with CONTRL SHIFT ENTER. Did you download my spreadsheet did it work for you? Everything is blank when i try. Can you try it yourself and tweak it so it can work please.

@squidd yes i also think formula would work best just need to find the right one

 

[FDibbins]

As mentioned in post #14, I cannot access your file.

Can you provide a small sample here? (copy/paste or upload a pic)?

 

[SQUIDD]

Ford

Yeah, something weird here.

The pull down dosent work, but if i paste your formula into the second cell, a3, then change the references from 2 -3 for the rows, it works.

i will keep looking, but thought i would ppost my findings.

i cannot see why yet.

dave

 

[Promptbeef]

Hey @FDibbins this are some small examples. My spreadsheets have more then 5 players.
Sheet 1: All Players
[TABLE="width: 500"]
<tbody>[TR]
[TD]Postition[/TD]
[TD]Name[/TD]
[/TR]
[TR]
[TD]PG[/TD]
[TD]Russell Westbrook[/TD]
[/TR]
[TR]
[TD]SG[/TD]
[TD]James Harden[/TD]
[/TR]
[TR]
[TD]PG[/TD]
[TD]Kyrie Irving[/TD]
[/TR]
[TR]
[TD]SF[/TD]
[TD]Kevin Durant[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]Demarcus Cousins[/TD]
[/TR]
[TR]
[TD]PG[/TD]
[TD]Damian Lillard[/TD]
[/TR]
</tbody>[/TABLE]

Sheet 2: PG (What i want)

[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]Kyrie Irving[/TD]
[/TR]
[TR]
[TD]PG[/TD]
[TD]Damian Lillard[/TD]
[/TR]
</tbody>[/TABLE]


Sheet 2: PG (What i'm getting)
[TABLE="width: 500"]
<tbody>[TR]
[TD]PG[/TD]
[TD]Russell Westbrook[/TD]
[/TR]
[TR]
[TD]PG[/TD]
[TD]#n/a[/TD]
[/TR]
[TR]
[TD]PG[/TD]
[TD]Kyrie Irving[/TD]
[/TR]
[TR]
[TD]PG[/TD]
[TD]#n/a[/TD]
[/TR]
[TR]
[TD]PG[/TD]
[TD]#n/a[/TD]
[/TR]
[TR]
[TD]PG[/TD]
[TD]Damian Lillard[/TD]
[/TR]
</tbody>[/TABLE]

 

[SQUIDD]

yep

found it i think

change fords code to this

=IFERROR(INDEX('All Players'!$B:$B,SMALL(IF(('All Players'!A2:A500="PG")*('All Players'!B2:B500='All Players'!B2),ROW('All Players'!$A2:$A$500)),ROWS($A1:A1))),"")

CTRL SHIFT ENTER

nice job ford

dave

 

[FDibbins]

SQUIDD thanks for the assist. What did you change (my formula is on the previous page)