Function Problems

Excel Formula to Find and Combine Courses Sharing the Same Topic

Ayan MasoodAyan Masood Nov 10, 2026 868 views

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.

Excel Formula to Find Courses Sharing the Same Topic
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.
Before you start

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.

Solution 1Recommended

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.

1
Select the target cell

Click on the cell where you want the combined list of shared courses to appear.

2
Enter the formula

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.

3
Apply to other rows

Press Enter to apply the formula, then click and drag the fill handle down to apply it to the remaining rows in your dataset.

Combine Matching Courses Using TEXTJOIN and FILTER
Data Range Adjustment: Make sure to adjust the absolute ranges $A$2:$A$10 and $B$2:$B$10 in the formula to match the actual size of your dataset.

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. 1. Open your dataset: Launch WPS Spreadsheet and open the file containing your courses and topic codes.
  2. 2. Input the formula: Click on the target cell and type your TEXTJOIN and FILTER formula into the formula bar.
  3. 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.
Full compatibility with modern Microsoft Excel dynamic array functions.Intuitive interface for editing and debugging complex nested formulas.Lightweight application that handles large datasets smoothly.Free to use with comprehensive data analysis tools.
QA img-9

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.