Look through 2 columns to return name from 3rd column

[brutusmc99]

Given the following table of NBA players who can play 2 different positions (a "-" indicates they only play 1 position:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Pos 1[/TD]
[TD]Pos 2[/TD]
[TD]Player[/TD]
[/TR]
[TR]
[TD]PG[/TD]
[TD]-[/TD]
[TD]Westbrook[/TD]
[/TR]
[TR]
[TD]PF[/TD]
[TD]C[/TD]
[TD]Aldridge[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]PF[/TD]
[TD]Cousins[/TD]
[/TR]
[TR]
[TD]PF[/TD]
[TD]C[/TD]
[TD]Love[/TD]
[/TR]
[TR]
[TD]SF[/TD]
[TD]PF[/TD]
[TD]Anthony[/TD]
[/TR]
[TR]
[TD]SG[/TD]
[TD]SF[/TD]
[TD]Harden[/TD]
[/TR]
[TR]
[TD]PG[/TD]
[TD]-[/TD]
[TD]Lowry[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]PF[/TD]
[TD]Bosh[/TD]
[/TR]
[TR]
[TD]SF[/TD]
[TD]SG[/TD]
[TD]Leonard[/TD]
[/TR]
</tbody>[/TABLE]

I want to be able to look through both Position columns, Pos 1 & Pos 2, and output and sort the player names into a second table as follows:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Position[/TD]
[TD]Player 1[/TD]
[TD]Player 2[/TD]
[TD]Player 3[/TD]
[TD]Player 4[/TD]
[TD]Player 5[/TD]
[/TR]
[TR]
[TD]PG[/TD]
[TD]Westbrook[/TD]
[TD]Lowry[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SG[/TD]
[TD]Harden[/TD]
[TD]Leonard[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SF[/TD]
[TD]Anthony[/TD]
[TD]Harden[/TD]
[TD]Leonard[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]PF[/TD]
[TD]Aldridge[/TD]
[TD]Cousins[/TD]
[TD]Love[/TD]
[TD]Anthony[/TD]
[TD]Bosh[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]Aldridge[/TD]
[TD]Cousins[/TD]
[TD]Love[/TD]
[TD]Bosh[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

So, the first player, Westbrook, is only a point guard, so he gets listed only under PG. But the second player, Aldridge, can play both power forward and center, so he is listed under both PF & C. And on down the list.

I can do it okay if I use only one position, i.e., Pos 2 (column B), using the following array formula:

{=INDEX($B$2:$C$11,SMALL(IF($B$2:$B$11=$S2,ROW($B$2:$B$11)-1),COLUMNS($T2:T2)),2)}

But, how can I use both positions (columns A and B)? Am I on the right track or do I need a new approach? Any help would be appreciated, thanks a lot.

 

[NeonRedSharpie]

You're probably not looking for a vba option, but just in case it will help or for future finders of this thread:

This assumes table 1 is on sheet1, beginning in A1.
This assumes table 2 is on sheet2, and merely includes the column/row headers in row 1 and column A

Sub moveNameTwoColumns()

    Dim nextCol As Long


    Dim list As Worksheet
    Dim table As Worksheet
    
    Set list = Sheets(1)
    Set table = Sheets(2)


    For x = 2 To table.Cells(Rows.Count, "A").End(xlUp).Row
        nextCol = 2
    
        For y = 2 To list.Cells(Rows.Count, "A").End(xlUp).Row
            If list.Cells(y, 1) = table.Cells(x, 1) Or _
                list.Cells(y, 2) = table.Cells(x, 1) Then
                
                    table.Cells(x, nextCol).Value = list.Cells(y, 3)
                    nextCol = nextCol + 1
                
            End If
        Next y
    Next x


End Sub

 

[XOR LX]

Hi.

In T2, assuming you're using Excel 2007 or later, array formula**

=IFERROR(INDIRECT(TEXT(SMALL(IF($A$2:$B$10=$S2,10^5*ROW($A$2:$B$10)+COLUMNS($A$2:$C$10)),COLUMNS($A:A)),"R0C00000"),0),"")

Copy across and down as required.

Regards

 

[brutusmc99]

Hi.

In T2, assuming you're using Excel 2007 or later, array formula**

=IFERROR(INDIRECT(TEXT(SMALL(IF($A$2:$B$10=$S2,10^5*ROW($A$2:$B$10)+COLUMNS($A$2:$C$10)),COLUMNS($A:A)),"R0C00000"),0),"")

Copy across and down as required.

Regards

Works, perfectly. Thanks so much.

 

[XOR LX]

You're welcome.

Regards

 

[brutusmc99]

You're probably not looking for a vba option, but just in case it will help or for future finders of this thread:

This assumes table 1 is on sheet1, beginning in A1.
This assumes table 2 is on sheet2, and merely includes the column/row headers in row 1 and column A

Thanks for the reply. Yeah, a vba approach is way above my pay grade Appreciate the effort, though. Hopefully, someone else will make good use of it.

 

[Domenic]

Or, maybe...

T2, confirmed with CONTROL+SHIFT+ENTER, copied across and down:

=IFERROR(INDEX($C$2:$C$10,SMALL(IF(($A$2:$A$10=$S2)+($B$2:$B$10=$S2),ROW($C$2:$C$10)-ROW($C$2)+1),COLUMNS($T2:T2))),"")

Hope this helps!

 

[brutusmc99]

Or, maybe...

T2, confirmed with CONTROL+SHIFT+ENTER, copied across and down:

=IFERROR(INDEX($C$2:$C$10,SMALL(IF(($A$2:$A$10=$S2)+($B$2:$B$10=$S2),ROW($C$2:$C$10)-ROW($C$2)+1),COLUMNS($T2:T2))),"")

Hope this helps!

Yeah, it works, too. Thanks a lot!

 

[Domenic]

You're very welcome!