List Available Colors
As a Catalogue Manager, I frequently need to present all available color options for a specific product in a single, comma-separated cell. Manually concatenating these can be time-consuming and error-prone, especially with many products and color variations. I need an efficient way to join all 'Color' options for a specific product ID into a single cell (e.g., 'Red, Blue, Green').
=TEXTJOIN(", ", TRUE, IF(A2:A7="P001", C2:C7, "")) How it works: This formula leverages `TEXTJOIN` with a conditional `IF` statement. The `IF(A2:A7="P001", C2:C7, "")` part creates an array that includes the color from column C if the corresponding Product ID in column A matches 'P001', otherwise it returns an empty string. `TEXTJOIN` then takes this array. The `", "` specifies that a comma followed by a space should be used as the delimiter between joined items. The `TRUE` argument is crucial as it tells `TEXTJOIN` to ignore any empty strings generated by the `IF` function, ensuring only actual colors are joined and preventing extra delimiters.
Data Setup
| ProductID | ProductName | Color |
|---|---|---|
| P001 | T-Shirt | Red |
| P001 | T-Shirt | Blue |
| P001 | T-Shirt | Green |
| P002 | Jeans | Black |
| P002 | Jeans | Grey |
| P003 | Shoes | White |
Step-by-Step Guide
Organize your product data with columns for 'ProductID' and 'Color' (e.g., 'ProductID' in column A and 'Color' in column C, starting from row 2).
In an empty cell where you want the combined colors to appear (e.g., E2), enter the formula: `=TEXTJOIN(", ", TRUE, IF(A2:A7="P001", C2:C7, ""))`.
Replace `A2:A7` with your actual range containing Product IDs, `C2:C7` with your actual range containing Colors, and `"P001"` with the specific Product ID you wish to filter for.
Press Enter. Excel will automatically spill the result into the cell.