Function Problems

How to Return Multiple Names for Matching Criteria in Excel

Camila Milosovich Oct 1, 2026 869 views

Question details

The user needs to retrieve and display all employee names that match specific project and position criteria in a single cell, rather than just the first match.

How to Return Multiple Names for Matching Criteria in a Single Excel Cell

Product: Excel
Device & OS: not provided
Scenario: Managing an employee schedule where multiple employees share the same project and position, requiring all assigned names to be listed together.
Observed behavior: Standard lookup functions like VLOOKUP return only the first matching employee, failing to display the complete list of matching names in one cell.

Before you start

Verify your Excel version, as the most efficient dynamic array solution requires the FILTER function, which is only available in Microsoft 365 and Excel 2021 or later.

Solution 1Recommended

Use TEXTJOIN and FILTER Functions (Microsoft 365 & Excel 2021+)

This is the most efficient and recommended way to extract and combine multiple matches into a single cell using dynamic arrays.

The FILTER function extracts all records that meet your conditions, and TEXTJOIN combines these results into a single string separated by a delimiter of your choice (such as a comma).

Select the target cell

Click on the specific cell in your schedule where you want the combined list of employee names to appear.

Input the combined formula

Enter the formula: =TEXTJOIN(", ", TRUE, FILTER(Name_Range, (Project_Range="Target Project") * (Position_Range="Target Position"))). Adjust the ranges to match your specific dataset.

Execute the formula

Press Enter to execute. The cell will now display all matching employee names separated by commas.

Use TEXTJOIN and FILTER Functions (Microsoft 365 & Excel 2021+)

Handling Empty Results: If no matches are found, the FILTER function may return a #CALC! error. You can avoid this by using the 'if_empty' argument in FILTER: FILTER(range, criteria, "No Match").

Solution 2

Use TEXTJOIN and IF Array Formula (Excel 2019)

For older versions that support TEXTJOIN but lack the dynamic FILTER function, an array formula can achieve the same result.

Advanced Functions in WPS Office

Easily Filter and Join Multiple Matches in WPS Spreadsheet

WPS Spreadsheet provides robust support for modern array functions, including TEXTJOIN and FILTER, allowing you to seamlessly look up and combine multiple matching records without complex helper columns.

1. Open your schedule workbook: Launch WPS Spreadsheet and open the file containing your employee schedule data.
2. Select the output cell: Click on the cell where you wish to display the combined list of matching employees.
3. Enter the dynamic formula: Type =TEXTJOIN(", ", TRUE, FILTER(Name_Range, Criteria_Range="Condition")) into the formula bar.
4. Get instant results: Press Enter. WPS Spreadsheet will instantly compute the dynamic array and display all combined matches.

Fully compatible with Microsoft Excel formulas and .xlsx formatsSupports dynamic arrays and advanced lookup functions nativelyLightweight, fast, and completely free to use for daily tasks

microsoft office alternative - wps office

Frequently Asked Questions

Why does my FILTER function return a #NAME? error?

This error occurs if your version of Excel does not support the FILTER function. You must be using Microsoft 365, Excel 2021, or a modern alternative like WPS Office. If you are on an older version, use the TEXTJOIN and IF array formula method instead.

How do I avoid extra commas if there are blank matches?

Set the second argument of the TEXTJOIN function to TRUE (e.g., =TEXTJOIN(", ", TRUE, ...)). This tells the formula to ignore empty strings and prevents trailing or consecutive commas.

Can I return matches based on multiple criteria?

Yes, you can multiply conditions within the FILTER function. For example, using FILTER(A2:A10, (B2:B10="Project X") * (C2:C10="Manager")) requires both the project and role conditions to be true before returning a name.

What if I have an older version of Excel that doesn't even have TEXTJOIN?

If your Excel version is older than 2019 (like Excel 2016 or 2013), neither FILTER nor TEXTJOIN is available. You will need to use helper columns to concatenate matches row by row, or write a custom VBA macro to join the text.

Excel & Data Problems