digest
Short Summary
Main recommendation: Use TEXTJOIN with an array-based condition to look up a value that appears several times in one column and return all matching items from another column into a single cell, using a method shared by commenter DRS. The video shows two array approaches: one using REPEAT, and one using IF, both entered with Control+Shift+Enter.
Key Points
- The problem: a lookup value (e.g., invoice 10001) appears multiple times in the first column, and you want all corresponding items from the next column returned in one cell.
- TEXTJOIN (Excel 2016 and later) joins the matched items with a delimiter; the video credits DRS, a commenter, for the REPEAT-based solution posted on the Excel message board.
- Method 1 uses REPEAT to build an array that keeps matching items and produces empty results for non-matches, which TEXTJOIN then ignores.
- Method 2 uses IF to return matching items and an empty text string ("") for non-matches, producing the same result.
- Both methods depend on an array operation, so they must be entered with Control+Shift+Enter, not just Enter.
Details Worth Noting
- Concrete instructions:
- Enter the delimiter as "; " (double quote, semicolon, space, double quote), which separates the returned items.
- Set the second argument (ignore_empty) to TRUE (or 1) so empty results are skipped.
- REPEAT method: use REPEAT(item_range, (lookup_range=lookup_value)+0). The comparison returns TRUE/FALSE; adding 0 converts them to 1/0, so REPEAT outputs each item once when it matches and zero times otherwise.
- IF method: use IF(lookup_range=lookup_value, item_range, ""). Matching rows return the item; non-matching rows return an empty string.
- Press Control+Shift+Enter to enter the formula, then confirm curly brackets {} appear in the formula bar, indicating an array formula.
- Use F9 while selecting parts of the formula to see the evaluated array values and verify the logic; press Control+Z to undo.
- The video demonstrates the same result with both approaches for invoice 10001, which has three matching rows.
- The video notes that the formula is an array calculation, so Excel tells you it has been recognized by the curly brackets.
- The video credits DRS (an Excel message board commenter) for the REPEAT solution.
Bottom Line
Once you understand the array logic, TEXTJOIN can gather every matching item into one cell, using either REPEAT or IF as the filter, as long as you enter the formula with Control+Shift+Enter.
