Function Problems

How to Find and Combine Matching Team Names by Team Number in Excel

Maira Mehtab Sep 30, 2026 868 views

Question details

The user needs to look up two names assigned to the same team number and display them together in a single 'Team Members' cell.

How to Find and Combine Matching Team Names by Team Number in Excel

Product: Excel
Device & OS: not provided
Scenario: Organizing team rosters where each team has two people, and their names need to be merged into one cell based on a shared team number identifier.
Observed behavior: Names are listed in separate rows and must be automatically looked up by team number, then joined together into one cell without manual copying and pasting.

Before you start

Ensure your dataset has a clear, consistent column for team numbers and that your spreadsheet software supports dynamic array functions like TEXTJOIN and FILTER.

Solution 1Recommended

Use TEXTJOIN and FILTER Functions

The most efficient way to combine multiple names based on a matching team number using dynamic arrays.

If you are using a modern version of Excel or WPS Spreadsheet, the combination of TEXTJOIN and FILTER is the fastest way to extract and merge matching records into one cell.

Select the destination cell

Click on the cell where you want the combined team members' names to appear.

Enter the formula

Type the formula `=TEXTJOIN(", ", TRUE, FILTER(B:B, A:A=D2))`. In this example, column B contains the names, column A contains the team numbers, and D2 is the specific team number you want to match.

Apply the result

Press Enter to execute the formula. Both names associated with the team number in D2 will now appear separated by a comma.

Dynamic Updates: This formula will automatically update the combined cell if you add, remove, or change names in the source columns.

Solution 2

Use Helper Columns with INDEX and MATCH

A reliable alternative method for older spreadsheet versions that do not support the FILTER function.

WPS Spreadsheet Formula Solutions

Easily Combine Team Members in WPS Spreadsheet

WPS Spreadsheet fully supports advanced array formulas like TEXTJOIN and FILTER, allowing you to instantly match and combine names by team numbers without complex helper columns.

1. Open Your Data: Launch WPS Office and open the workbook containing your team roster.
2. Select Target Cell: Click on the specific cell in your Team Members column where the joined names should appear.
3. Input the Formula: Type `=TEXTJOIN(", ", TRUE, FILTER(Name_Range, Team_Range=Target_Cell))`.
4. Apply to All Teams: Press Enter, then drag the fill handle down to apply the formula to the rest of the team numbers.

Fully supports modern dynamic array functions like FILTER and TEXTJOIN.100% compatible with Microsoft Excel formulas and file formats.Free, lightweight, and easy-to-use alternative for data management.Familiar user interface requiring no learning curve.

microsoft office alternative - wps office

Frequently Asked Questions

Why is the FILTER function returning a #CALC! error?

This error occurs if the FILTER function finds no matching team numbers in your dataset. You can prevent this error by adding the 'if_empty' argument to your formula, like this: =FILTER(B:B, A:A=D2, "No Members Found").

Can I join more than two names per team using this method?

Yes. The TEXTJOIN and FILTER combination will automatically extract and join all names that match the specified team number, whether there are two, three, or twenty members on the team.

What if I have first and last names in separate columns?

You can concatenate them directly within the FILTER function. For example, if first names are in column A and last names in column B, use: =TEXTJOIN(", ", TRUE, FILTER(A:A&" "&B:B, C:C=D2)).

Excel & Data Problems