Excel Formula to Find and Combine Courses Sharing the Same Topic
Question details
The user wants to find all courses that share the same topic code and display the matching course names concatenated into a single cell using a formula.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Grouping matching course names based on shared topic codes into a single cell for reporting or data organization.
- Observed behavior
- The goal is to retrieve multiple matching results and concatenate them into one cell rather than spreading them across multiple rows or columns.
Ensure you are using a version of Excel or WPS Spreadsheet that supports Dynamic Array formulas, as both the TEXTJOIN and FILTER functions are required for this solution.
Combine Matching Courses Using TEXTJOIN and FILTER
This is the most efficient and recommended method to find and list all other items that share the same category or topic code.
The FILTER function extracts all matching course names based on the topic code while excluding the current course itself. The TEXTJOIN function then combines these filtered results into a single comma-separated text string.
Click on the cell where you want the combined list of shared courses to appear.
Type the formula: =TEXTJOIN(", ",,FILTER($A$2:$A$10,($B$2:$B$10=B2)*($A$2:$A$10<>A2),"No shared course")). Assume Column A contains course names and Column B contains topic codes.
Press Enter to apply the formula, then click and drag the fill handle down to apply it to the remaining rows in your dataset.

Include the Current Course Name in the Combined List
Use this method if you want the output to display the current course name alongside the other courses that share the topic.
Use REDUCE and LAMBDA for a Dynamic Array Result
If you want to generate the results for the entire column automatically without copying the formula down manually, use a dynamic array formula.
Easily Manage Advanced Formulas with WPS Spreadsheet
WPS Spreadsheet fully supports advanced dynamic array functions like TEXTJOIN, FILTER, LET, and LAMBDA, allowing you to seamlessly execute complex data lookups and concatenations just like in Microsoft Excel.
- 1. Open your dataset: Launch WPS Spreadsheet and open the file containing your courses and topic codes.
- 2. Input the formula: Click on the target cell and type your TEXTJOIN and FILTER formula into the formula bar.
- 3. Apply and analyze: Press Enter to see the combined text result, and use the fill handle to drag the formula down for the rest of your data.

Frequently Asked Questions
Why am I getting a #NAME? error when using this formula?
A #NAME? error usually indicates that your current version of the spreadsheet software does not support the FILTER or TEXTJOIN functions. These functions require newer versions of Office or WPS Office that support dynamic arrays.
Can I use VLOOKUP to return multiple values in one cell?
No, the standard VLOOKUP function only returns the first matching value it finds. To return and combine multiple matches into a single cell, you must use a combination of TEXTJOIN and FILTER.
How do I change the separator from a comma to a line break?
You can change the first argument in the TEXTJOIN function to CHAR(10), which represents a line break. For example: =TEXTJOIN(CHAR(10),,FILTER(...)). Make sure to enable 'Wrap Text' on the cell to display the line breaks properly.
What does 'No shared course' mean in the formula?
This is the 'if_empty' argument for the FILTER function. If the formula does not find any other courses sharing the same topic code, it returns 'No shared course' instead of a #CALC! error.




