Formula to identify from list team members not playing

[cph8]

Hi,

Appreciate some help.

I run a soccer/football team with 20 players. I can only take 16 players to each game and need a spreadsheet that can identify which players have been selected and which have not.

I have a list of the whole squad and a list of the 16 being selected. In a column next to the squad list (20) i have used

=(IF(IF(ISERROR(VLOOKUP(B4,$B$26:$B$42,1,FALSE)),"Not Played",(VLOOKUP(B4,$B$26:$B$42,1,FALSE)))=B4,"Played","Not Played"))
to bring up which players are included in the squad list for that week.

I want to create another list pulling through the players names who have "Not playing", this way i can keep track of the players not going and ensure they get their chance.

I have attached the excel spreadsheet so you can see what i mean.

Any help would be greatly appreciated

Craig

[JohnTopley]

See attached ...

[Craig K.]

John beat me to the solution, but I wanted to point out that you may want to update the formulas you have in C4:C23 to be

Please Login or Register  to view this content.

instead. It's cleaner and more concise.

[CAABYYC]

Option-1 (array formula)

F26 = IFERROR(INDEX($B$4:$B$23,SMALL(IF(ISERROR(MATCH($B$4:$B$23,$B$26:$B$41,)),$A$4:$A$23),ROW(A1))),"") . then hit "Enter+Shift+Control" and copy down

Option -2 (regular formula)

1. E4 =IF(C4="Not played",ROW(),""), then copy down to E23
2. G4 =IFERROR(INDEX($B$1:$B$23,SMALL($E$1:$E$23,ROW(A1))),""), then copy down

[Tony Valko]

This array formula** entered in D26:

=LOOKUP("zzzzz",CHOOSE({1,2},"",INDEX(B:B,SMALL(IF(C$4:C$23="not played",ROW(C$4:C$23)),ROWS(D$26:D26)))))

Copy down to D29.

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

[Jean.P28]

another solution you can select every player 1 time

[cph8]

Guy's

Thank you for your help, see attached what I came up with. Just in-case anyone needs something similar in the future.

Thanks

Craig