Create Parent Email List
I need to join all 'Parent Email' addresses for Class 5B into a single string for a newsletter.
=TEXTJOIN(", ", TRUE, IF(B2:B9="5B", C2:C9, "")) How it works: The `TEXTJOIN` function is ideal for combining text strings from a range into a single string. In this scenario, we use ", " as the delimiter to separate each email address. The `TRUE` argument for `ignore_empty` is critical because it ensures that only actual email addresses are included, skipping any empty strings generated by our filter. The core of the solution is the `IF` function, which acts as a dynamic filter. It iterates through each cell in the 'Class' column (B2:B9) and checks if the class name is "5B". If it matches, the corresponding 'Parent Email' from column C is passed to `TEXTJOIN`. If it doesn't match, an empty string is passed, which `TEXTJOIN` then ignores thanks to the `TRUE` argument. This effectively creates a clean, comma-separated list of only the parent emails for Class 5B.
Data Setup
| Student Name | Class | Parent Email |
|---|---|---|
| Alice Smith | 5A | alice.p@email.com |
| Bob Johnson | 5B | bob.p@email.com |
| Charlie Brown | 5B | charlie.p@email.com |
| Diana Prince | 5A | diana.p@email.com |
| Eve Adams | 5B | eve.p@email.com |
| Frank White | 5B | frank.p@email.com |
| Grace Kelly | 5A | grace.p@email.com |
| Harry Potter | 5B | harry.p@email.com |
| Ivy Green | 5B |
Step-by-Step Guide
Organize your student data with columns for 'Student Name', 'Class', and 'Parent Email'.
Identify the range containing the 'Class' information (e.g., B2:B9) and the 'Parent Email' addresses (e.g., C2:C9).
In an empty cell where you want the combined email list, type the `TEXTJOIN` function.
Set the first argument, `delimiter`, to ", " (a comma followed by a space) to separate the email addresses.
Set the second argument, `ignore_empty`, to TRUE. This is crucial as it tells TEXTJOIN to skip any blank cells or results from the IF function that don't match your criteria.
For the third argument, `text1`, use an `IF` statement: `IF(B2:B9="5B", C2:C9, "")`. This checks each cell in the 'Class' column (B2:B9) to see if it equals "5B". If true, it returns the corresponding 'Parent Email' from column C; otherwise, it returns an empty string.
Close the `TEXTJOIN` function. If you are using an older version of Excel, you might need to enter this as an array formula by pressing Ctrl+Shift+Enter.