List Team Members
I need to combine the names of all students on 'Team A' into a single cell.
=TEXTJOIN(", ", TRUE, IF(A2:A6="Team A", B2:B6, "")) How it works: The `TEXTJOIN` function is used to concatenate text strings from a range or array, using a specified delimiter. Here's how it works: - `", "` acts as the separator between each student's name. - `TRUE` tells `TEXTJOIN` to ignore any empty strings generated by the `IF` function, preventing extra delimiters from appearing in the final list. - `IF(A2:A6="Team A", B2:B6, "")` creates an array of names. It evaluates each cell in the range A2:A6. If a cell contains "Team A", the corresponding student name from B2:B6 is included in the array; otherwise, an empty string is included. `TEXTJOIN` then processes this array, effectively only joining the names of "Team A" members.
Data Setup
| Team | Student Name |
|---|---|
| Team A | Alice Smith |
| Team B | Bob Johnson |
| Team A | Charlie Brown |
| Team A | David Lee |
| Team B | Eve Davis |
Step-by-Step Guide
Identify the range containing the team names (e.g., A2:A6) and the student names (e.g., B2:B6).
In the cell where you want the combined list, type `=TEXTJOIN(`.
For the `delimiter`, enter `", "` (a comma followed by a space, enclosed in double quotes).
For `ignore_empty`, enter `TRUE` to ensure that blank results from the `IF` function are skipped, preventing extra delimiters.
For `text1`, use an `IF` statement to conditionally select names: `IF(A2:A6="Team A", B2:B6, "")`. This checks each cell in the team column; if it's "Team A", it returns the corresponding student name from the name column; otherwise, it returns an empty string.
Close the `TEXTJOIN` function with a closing parenthesis: `)`. The complete formula should look like: `=TEXTJOIN(", ", TRUE, IF(A2:A6="Team A", B2:B6, ""))`.
Press `Enter`. If you are using an older version of Excel, you might need to press `Ctrl+Shift+Enter` to enter it as an array formula.