Function Problems

How to Combine TEXTJOIN, FILTER, and IFS Formulas in Excel

Camila Milosovich Sep 28, 2026 871 views

Question details

The user needs an Excel formula to join values from one column based on multiple criteria in another, particularly adjusting for matching text length based on the count of specific characters like opening parentheses.

How to Combine TEXTJOIN, FILTER, and IFS Formulas in Excel

Product: Spreadsheet
Device & OS: not provided
Scenario: Filtering and joining text strings from multiple rows into a single cell based on complex dynamic conditions and character counts.
Observed behavior: The user is attempting to build a nested formula using TEXTJOIN, FILTER, IF/IFS, and SEARCH but is unsure how to properly structure the logical tests and matching arrays to yield the correct result.

Before you start

Ensure that your version of the spreadsheet software supports dynamic array functions like FILTER and TEXTJOIN, as these are required for advanced array operations without complex legacy workarounds.

Solution 1Recommended

Combine TEXTJOIN and FILTER for Dynamic Text Extraction

Use TEXTJOIN alongside FILTER to extract and combine strings that meet specific conditions, bypassing the need for complex IFS structures.

The FILTER function is highly efficient for returning arrays that meet specific criteria. Wrapping this inside a TEXTJOIN function allows you to output all matches into a single cell separated by a delimiter.

Select the destination cell

Click on the cell where you want the combined text result to be displayed.

Enter the base FILTER logic

Start by typing the FILTER function to isolate your data. For example: =FILTER($G$3:$G$12, ($G$3:$G$12<>$B$10)*($E$3:$E$12=A3)). This uses multiplication (*) to apply multiple criteria across columns.

Wrap with the TEXTJOIN function

Enclose the FILTER formula within TEXTJOIN. Type: =TEXTJOIN(",", TRUE, FILTER($G$3:$G$12, ($G$3:$G$12<>$B$10)*($E$3:$E$12=A3))). The TRUE argument ensures that any empty cells are ignored in the final output.

Calculate the result

Press Enter to execute the formula. The matched results will populate the cell separated by commas.

Combine TEXTJOIN and FILTER for Dynamic Text Extraction

Formula Array Compatibility: In modern spreadsheet applications, you do not need to press Ctrl+Shift+Enter for these dynamic arrays to spill or calculate correctly.

Solution 2

Calculate Text Length Based on Parentheses Count

Use a combination of LEN and SUBSTITUTE to dynamically find the number of parentheses in each row, acting as the criteria for your logic.

Advanced Spreadsheet Formulas

Master Complex Array Formulas Easily with WPS Spreadsheet

WPS Office provides robust support for modern dynamic array formulas, including TEXTJOIN, FILTER, and IFS. You can seamlessly manage complex data extraction tasks with high performance and familiar syntax.

1. Open your workbook in WPS Spreadsheet: Launch WPS Office and open your existing data file.
2. Select the target cell: Click the cell where the combined formula result should be displayed.
3. Enter the formula: Type your combined =TEXTJOIN(",", TRUE, FILTER(...)) formula and press Enter to instantly see the results.

Fully compatible with Microsoft Excel formula syntaxSupports modern functions like TEXTJOIN, IFS, and FILTERLightweight application with fast calculation speedsFree and intuitive interface for complex data analysis

Frequently Asked Questions

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

The #CALC! error typically occurs in the FILTER function when no data meets the criteria specified. You can add a third argument to FILTER, such as FILTER(array, include, "No Match"), to handle empty results gracefully.

Can I use TEXTJOIN without dynamic arrays if my software doesn't support FILTER?

Yes, in older versions lacking FILTER, you can use an IF statement inside TEXTJOIN as an array formula, such as =TEXTJOIN(",", TRUE, IF($E$3:$E$12=A3, $G$3:$G$12, "")). This requires pressing Ctrl+Shift+Enter to calculate properly.

How do I make the delimiter dynamic in TEXTJOIN?

The first argument of TEXTJOIN is the delimiter. You can reference another cell containing your desired delimiter or use an IF function to change the delimiter dynamically based on specific row conditions.

What is the difference between IF and IFS in spreadsheet formulas?

The IF function evaluates a single logical condition, whereas IFS evaluates multiple conditions sequentially without the need to nest multiple IF statements, making complex logical tests much easier to read and maintain.

Excel & Data Problems