List Missing PPE Items
I need to combine all items marked 'Missing' in the safety checklist into a single alert message.
=TEXTJOIN(", ", TRUE, IF(B2:B7="Missing", A2:A7, "")) How it works: This formula leverages TEXTJOIN to concatenate strings from a range, but with a powerful filter. The IF function acts as the filter, creating an array where only the names of PPE items marked 'Missing' are returned, with empty strings for 'Present' items. TEXTJOIN then takes this array, uses ', ' as a separator, and critically, the 'TRUE' argument tells it to ignore all the empty strings generated by the IF function, resulting in a clean list of only the missing items.
Data Setup
| PPE Item | Status |
|---|---|
| Hard Hat | Missing |
| Safety Vest | Present |
| Safety Glasses | Missing |
| Gloves | Present |
| Steel-toe Boots | Missing |
| Ear Plugs | Present |
Step-by-Step Guide
Organize your safety checklist data with PPE items in one column (e.g., Column A) and their status (e.g., 'Present', 'Missing') in an adjacent column (e.g., Column B). Ensure headers are in row 1.
In a new cell where you want the combined list, start typing `=TEXTJOIN(`. The first argument is the delimiter; use `", "` to separate items with a comma and a space.
The second argument is `TRUE` to ignore empty cells, which is crucial for filtering. Type `TRUE,`.
For the third argument (the text to join), we need to filter for 'Missing' items. Use an `IF` statement: `IF(B2:B7="Missing", A2:A7, "")`.
This `IF` statement checks each cell in the status range (B2:B7). If a cell contains "Missing", it returns the corresponding PPE item from A2:A7; otherwise, it returns an empty string (`""`).
Close the `TEXTJOIN` function with a parenthesis: `)` and press Enter. Excel will then display a single string listing all PPE items marked as 'Missing'.