Function Problems

How to Use TEXTJOIN, FILTER, and INDIRECT with Multiple Criteria in Excel

John Wilson Oct 14, 2026 870 views

Question details

The user needs to construct a dynamic Excel formula that joins notes from a table on a variable worksheet, applying filters for specific department and staff criteria.

How to Use TEXTJOIN, FILTER, and INDIRECT with Multiple Criteria in Excel

Product: Excel
Device & OS: not provided
Scenario: Combining text data conditionally from dynamically referenced worksheets or tables into a single cell.
Observed behavior: Requires a functional dynamic array formula that applies an AND logic filter to two distinct criteria across a dynamically named sheet, returning a concatenated string.

Before you start

Ensure your spreadsheet software supports Dynamic Array functions like FILTER (available in Microsoft 365, Excel 2021, and WPS Office). Verify that the dynamically referenced sheet names and column headers exactly match your source data.

Solution 1Recommended

Use TEXTJOIN combined with FILTER and INDIRECT

Combine these three functions to create a dynamic concatenation formula with multiple AND conditions based on dynamic worksheet references.

The TEXTJOIN function is used to join the filtered results into a single text string separated by a comma. The FILTER function processes the array to return only rows that meet your specific conditions.

The INDIRECT function enables you to dynamically build the table or sheet reference using a text string stored in another cell. By multiplying two conditions inside the FILTER function, you create an AND logic filter, ensuring both criteria must match to return a result.

Identify the reference cells

Determine the cells storing your criteria. For example, let cell E1 hold the target sheet name, B9 hold the department criteria, and B10 hold the staff criteria.

Construct the dynamic references

Use the INDIRECT function to reference specific columns dynamically. Type `INDIRECT(E1&"[notes]")` for the output range, `INDIRECT(E1&"[cog directorate]")` for the first criteria column, and `INDIRECT(E1&"[Staff]")` for the second.

Apply the FILTER function with AND logic

Inside the formula bar, apply the FILTER function by multiplying the conditions to enforce an AND criteria: `FILTER(INDIRECT(E1&"[notes]"), (INDIRECT(E1&"[cog directorate]")=B9)*(INDIRECT(E1&"[Staff]")=B10), "")`.

Wrap with TEXTJOIN

Enclose the entire FILTER statement within a TEXTJOIN function to concatenate the array. Your final formula should look like this: `=TEXTJOIN(", ", TRUE, FILTER(INDIRECT(E1&"[notes]"), (INDIRECT(E1&"[cog directorate]")=B9)*(INDIRECT(E1&"[Staff]")=B10), ""))`.

Execute the formula

Press the Enter key to execute the formula. The cell will now display the dynamically joined notes filtered by both the department and staff group.

Use TEXTJOIN combined with FILTER and INDIRECT

Understanding the Ampersand (&) Syntax: The ampersand (&) is used to concatenate the dynamic sheet name reference (E1) with the literal column identifier string (e.g., "[notes]"). Ensure you include the quotation marks exactly as shown so INDIRECT interprets it as a valid table or range reference.

Advanced Spreadsheet Functions

Process Dynamic Arrays Seamlessly in WPS Spreadsheet

WPS Office fully supports advanced dynamic array functions like TEXTJOIN, FILTER, and INDIRECT, allowing you to seamlessly execute complex data queries and concatenation tasks without any compatibility issues.

1. Download WPS Office: Download and install WPS Office for free from the official WPS website.
2. Open your workbook: Launch WPS Spreadsheets and open the workbook containing your target data and reference tables.
3. Input the dynamic formula: Click on the cell where you want the result to appear and paste or type the combined TEXTJOIN, FILTER, and INDIRECT formula.
4. View instant results: Press Enter to instantly process the data and view your conditionally filtered and concatenated text.

Fully compatible with Microsoft Excel formulas and functionsNative support for modern dynamic arrays like FILTER and TEXTJOINFree, lightweight, and fast alternative for complex data processingFamiliar user interface makes switching completely seamless

microsoft office alternative - wps office

Frequently Asked Questions

Why is my INDIRECT formula returning a #REF! error?

A #REF! error typically occurs if the sheet or table name referenced in your cell does not exist, is misspelled, or contains spaces without being enclosed in single quotes. INDIRECT will also return a #REF! error if it refers to a separate, external workbook that is currently closed.

How do I use an OR condition in the FILTER function instead of AND?

To apply an OR condition, use the plus sign (+) instead of the multiplication asterisk (*) between your criteria inside the FILTER function. For example, `(INDIRECT(E1&"[Group]")=B9)+(INDIRECT(E1&"[Group]")=B10)` ensures the row is included if either condition is met.

Can I use TEXTJOIN without ignoring empty cells?

Yes, the second argument in the TEXTJOIN function determines whether to ignore empty cells. Setting it to TRUE ignores them, while setting it to FALSE includes empty cells in the final concatenated string, inserting consecutive delimiters.

Excel & Data Problems