Email List of Absent Workers
I need to join the email addresses of all workers marked 'Absent' to send a bulk notification.
=TEXTJOIN(", ", TRUE, IF(C2:C7="Absent", B2:B7, "")) How it works: The TEXTJOIN function is perfect for concatenating text strings from a range with a specified delimiter. By nesting an IF function inside, we create an array of email addresses, but only for those workers whose 'Attendance Status' is 'Absent'. The `TRUE` argument for `ignore_empty` ensures that only the actual email addresses are joined, effectively filtering out the empty strings from present workers. This results in a single cell containing a comma-separated list of all absent workers' email addresses, ready for a bulk email.
Data Setup
| Worker Name | Email Address | Attendance Status |
|---|---|---|
| Alice Smith | alice.smith@company.com | Present |
| Bob Johnson | bob.j@company.com | Absent |
| Charlie Brown | charlie.b@company.com | Present |
| Diana Prince | diana.p@company.com | Absent |
| Eve Adams | eve.a@company.com | Present |
| Frank White | frank.w@company.com | Absent |
Step-by-Step Guide
Organize your data with 'Email Address' in one column (e.g., Column B) and 'Attendance Status' in another (e.g., Column C).
Identify the range containing the 'Attendance Status' (e.g., C2:C7) and the range containing the 'Email Address' (e.g., B2:B7).
Use an IF statement to check the attendance status: `IF(C2:C7="Absent", B2:B7, "")`. This creates an array where only emails of absent workers are returned, and others are empty strings.
Wrap the IF statement with TEXTJOIN: `TEXTJOIN(", ", TRUE, ...)`.
The first argument `", "` is the delimiter, separating each email with a comma and a space.
The second argument `TRUE` tells TEXTJOIN to ignore any empty cells generated by the IF statement (i.e., emails of present workers).
Enter the formula into a cell. In modern Excel (Microsoft 365), this will spill automatically. In older versions, you might need to enter it as an array formula by pressing Ctrl+Shift+Enter.