How to Return Multiple Related Items in One Excel Cell
Question details
The user wants to extract and combine a variable list of related records into a single cell, rather than displaying them across multiple rows.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Organizing grouped data for presentation, such as rolling up multiple actors associated with a single movie title into one comma-separated "Cast" column.
- Observed behavior
- The user needs a way to look up a primary value (like a movie title) and output all matching secondary values (like actors) combined together in one specific cell.
Ensure your source data is organized in a structured table format with clear primary identifiers to accurately match and extract the related items.
Use TEXTJOIN and FILTER Functions
The most efficient and dynamic way to combine matching records into a single cell, available in modern versions of Excel and WPS Spreadsheet.
By combining the TEXTJOIN function with the FILTER function, you can search for a specific criteria (e.g., a movie name) and return an array of matching results. TEXTJOIN then stitches this array together using a delimiter of your choice, such as a comma and a space.
Click on the cell where you want the combined list of items to appear.
Type the formula: =TEXTJOIN(", ", TRUE, FILTER(Return_Range, Criteria_Range=Criteria)). Replace 'Return_Range' with the column containing the items to combine, 'Criteria_Range' with the column containing the lookup values, and 'Criteria' with the specific value you are searching for.
Press Enter to execute the formula. If you are applying this to a table, drag the fill handle down to copy the formula to the remaining rows.
Group and Combine Data using Power Query
Best for large datasets or older versions of Excel where you need to aggregate related records without writing complex dynamic array formulas.
Create a Custom VBA Function (UDF)
Ideal for users running legacy versions of Excel that do not support TEXTJOIN or dynamic arrays natively.
Combine Multiple Related Items Easily in WPS Spreadsheet
WPS Spreadsheet fully supports advanced array functions like TEXTJOIN and FILTER, allowing you to merge related records into a single cell effortlessly. It handles complex data processing with an intuitive interface.
- 1. Open your dataset: Launch WPS Spreadsheet and open the workbook containing your raw data.
- 2. Select the destination cell: Click the specific cell where the combined list of items should be displayed.
- 3. Enter the combination formula: Type =TEXTJOIN(", ", TRUE, FILTER(...)) substituting your specific lookup and return ranges.
- 4. Apply across your table: Press Enter, then drag the fill handle down to apply the combination logic to all other rows.
Frequently Asked Questions
Why does my TEXTJOIN and FILTER formula return a #CALC! error?
A #CALC! error typically occurs when the FILTER function finds no matches for your criteria. To fix this, add the optional 'if_empty' argument to your FILTER function, like this: FILTER(Return_Range, Criteria_Range=Criteria, "").
Can I combine items with a line break instead of a comma?
Yes. In the TEXTJOIN function, use CHAR(10) as the delimiter argument. For example: =TEXTJOIN(CHAR(10), TRUE, FILTER(...)). Ensure you enable 'Wrap Text' on the destination cell so the line breaks display properly.
How do I remove duplicate values when returning related items?
You can nest the UNIQUE function inside your formula. By structuring it as =TEXTJOIN(", ", TRUE, UNIQUE(FILTER(Return_Range, Criteria_Range=Criteria))), the formula will only return distinct related items and ignore duplicates.
Does WPS Spreadsheet support the TEXTJOIN function?
Yes, the latest versions of WPS Spreadsheet fully support both the TEXTJOIN and FILTER functions, allowing you to seamlessly process dynamic arrays and concatenate strings just as you would in Microsoft 365.
