brutusmc99
Board Regular
- Joined
- Oct 6, 2014
- Messages
- 111
Given the following table of NBA players who can play 2 different positions (a "-" indicates they only play 1 position:
<tbody>
</tbody>
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:
<tbody>
</tbody>
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.
Pos 1 | Pos 2 | Player |
PG | - | Westbrook |
PF | C | Aldridge |
C | PF | Cousins |
PF | C | Love |
SF | PF | Anthony |
SG | SF | Harden |
PG | - | Lowry |
C | PF | Bosh |
SF | SG | Leonard |
<tbody>
</tbody>
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:
Position | Player 1 | Player 2 | Player 3 | Player 4 | Player 5 |
PG | Westbrook | Lowry | |||
SG | Harden | Leonard | |||
SF | Anthony | Harden | Leonard | ||
PF | Aldridge | Cousins | Love | Anthony | Bosh |
C | Aldridge | Cousins | Love | Bosh |
<tbody>
</tbody>
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.