Function Problems

How to Join Matching Excel Values Using FILTER and CONCAT

Elise Williams Sep 27, 2026 869 views

Question details

The user needs to find all matching entries in one column based on a lookup value and concatenate their corresponding values from another column into a single cell.

How to Join Matching Excel Values Using FILTER and CONCAT

Product: Microsoft Excel
Device & OS: not provided
Scenario: Searching for multiple matching records in a dataset and dynamically combining their associated text or values together in one cell.
Observed behavior: Using standard TEXTJOIN or CONCAT alone did not produce the expected result when trying to extract and join multiple dynamic matches based on a condition.

Before you start

Ensure your spreadsheet software supports Dynamic Array functions like FILTER, which are available in Microsoft 365, Excel 2021, and recent versions of WPS Office.

Solution 1Recommended

Combine CONCAT and FILTER Functions

This is the most efficient method to dynamically extract and join multiple matching values into a single cell without spaces or delimiters.

While functions like XLOOKUP are excellent for finding a single match, they cannot easily return multiple matches into a single cell. The FILTER function solves this by returning an array of all matches, which can then be wrapped in CONCAT to stitch the text together.

Select the result cell

Click on the cell where you want the combined result to appear, for example, cell D1.

Enter the nested formula

Type the formula =CONCAT(FILTER(B1:B4, A1:A4=C1, "")) into the formula bar.

Apply the formula

Press Enter to execute the formula. The FILTER function extracts all values in column B where column A equals your criteria in C1, and CONCAT immediately joins those results together.

Formula Breakdown: The empty quotes ("") at the end of the FILTER function act as an error handler. If no matches are found, it returns an empty string instead of a calculation error.

Solution 2

Use TEXTJOIN and FILTER for Delimited Results

If you need to separate the joined matching values with a comma, space, or custom delimiter, use TEXTJOIN instead of CONCAT.

Advanced Spreadsheets in WPS Office

Concatenate and Filter Data Easily with WPS Spreadsheet

WPS Spreadsheet fully supports advanced dynamic array formulas including FILTER, XLOOKUP, CONCAT, and TEXTJOIN. You can effortlessly manage complex data consolidation tasks just as you would in Microsoft Excel.

1. Open your dataset in WPS Spreadsheet: Launch WPS Office and open your .xlsx workbook containing the data you want to join.
2. Identify your criteria and ranges: Locate the column containing the data you want to return and the column you need to check against your criteria.
3. Apply the nested formula: Enter =TEXTJOIN(", ", TRUE, FILTER(return_range, lookup_range=criteria, "")) in your destination cell.
4. Get instant results: Press Enter to instantly combine all matching data points with your chosen delimiter.

Fully compatible with Microsoft Excel formulas and .xlsx formats.Natively supports modern dynamic array functions for advanced filtering and joining.Lightweight software with a fast, intuitive tabbed interface.Free to download and highly efficient for everyday data processing tasks.

microsoft office alternative - wps office

Frequently Asked Questions

Why does my FILTER function return a #CALC! error?

The #CALC! error typically occurs if the FILTER function finds no matches and the [if_empty] argument is omitted. Adding an empty string "" at the end of your FILTER formula prevents this error and returns a blank cell instead.

Can I use XLOOKUP to return multiple matching values?

No, XLOOKUP is designed to return only the first matching value it encounters (or the last, if searching in reverse). To extract and join multiple matches, you must use the FILTER function combined with CONCAT or TEXTJOIN.

What is the difference between CONCAT and TEXTJOIN?

CONCAT directly strings all text together without any spaces or separators between the values. TEXTJOIN is more versatile, allowing you to specify a delimiter (like a comma) and providing an option to ignore empty cells within your data array.

Excel & Data Problems