List Members in Multiple Clubs
I need a unique list of Student IDs that appear in both the 'Chess Club' and 'Robotics Club' lists.
=UNIQUE(FILTER(A2:A5, COUNTIF(B2:B5, A2:A5))) How it works: This formula works by first identifying which Student IDs from the Chess Club list (A2:A5) are also present in the Robotics Club list (B2:B5). The `COUNTIF(B2:B5, A2:A5)` part checks each ID in A2:A5 against the entire B2:B5 range, returning a count greater than zero if the ID is found in the Robotics Club list. The `FILTER` function then uses these counts to extract only those Student IDs from A2:A5 that appear in both clubs. Finally, the `UNIQUE` function ensures that if any student ID appeared multiple times in the original Chess Club list (or was duplicated in the intersection), it is listed only once in the final result.
Data Setup
| Chess Club IDs | Robotics Club IDs |
|---|---|
| 101 | 102 |
| 102 | 103 |
| 103 | 104 |
| 105 | 102 |
Step-by-Step Guide
Organize your data: Place the Student IDs for the Chess Club in a column (e.g., A2:A5) and the Student IDs for the Robotics Club in an adjacent column (e.g., B2:B5).
In an empty cell where you want the result to appear (e.g., C2), type the following formula: `=UNIQUE(FILTER(A2:A5, COUNTIF(B2:B5, A2:A5)))`
Press Enter. Excel will spill the unique Student IDs that are members of both clubs into the cells below.