How to Use TEXTJOIN with FILTER for Conditional Concatenation in Google Sheets

Data aggregation and business reporting in Google Sheets frequently require combining multiple text values from a table into a single, cohesive delimited cell based on specific conditional criteria. When compiling comma-separated lists of project attendees for a given department, aggregating outstanding invoice numbers for a specific client, or generating formatted order summaries, standard spreadsheet functions often fall short. The basic TEXTJOIN function concatenates entire ranges uniformly, but lacks built-in logical filtering parameters to evaluate row criteria.

By nesting the FILTER function directly inside TEXTJOIN, Google Sheets users can construct dynamic conditional text strings that automatically update as source data changes. This technique filters data ranges based on multiple boolean conditions before passing the resulting subset to TEXTJOIN for clean delimiter formatting. This guide demonstrates how to configure and use TEXTJOIN with FILTER to perform conditional text concatenation in Google Sheets.

Understanding the TEXTJOIN and FILTER Functions

Combining these two functions creates a concise, array-aware formula structure without requiring complex custom scripts or repetitive nested logic.

The TEXTJOIN Function Syntax

=TEXTJOIN(delimiter, ignore_empty, text1, [text2, ...])

• delimiter: The text character or string placed between each joined value (e.g. ", ", " | ", or CHAR(10) for a newline).
• ignore_empty: A boolean flag (TRUE or FALSE). Setting this to TRUE ensures blank cells or empty filter results are omitted without leaving trailing or double commas.
• text1, text2: The text items, ranges, or nested array functions to concatenate.

The FILTER Function Syntax

=FILTER(range, condition1, [condition2, ...])

• range: The array or column containing the text values to extract and return.
• condition1, condition2: Boolean criteria expressions evaluating rows in the dataset (e.g. B2:B100 = "Completed").

Basic Implementation: Single Condition Concatenation

Consider an operational sales table where Column A lists project managers, Column B contains project names, and Column C indicates delivery status ("Active" or "Archived").

To compile a comma-separated list of all project names assigned to “Sarah” that are currently “Active”:

=TEXTJOIN(", ", TRUE, FILTER(B2:B100, A2:A100 = "Sarah", C2:C100 = "Active"))

How the Calculation Operates

1. The internal FILTER function scans rows 2 through 100, returning only the project names in Column B where Column A equals "Sarah" and Column C equals "Active".
2. The resulting array of filtered names is passed directly to TEXTJOIN.
3. TEXTJOIN joins each project name using a comma and space (", "), automatically skipping empty strings.

Advanced Scenarios: Multi-Criteria and Boolean Logic

In enterprise datasets, filtering logic frequently requires evaluating OR criteria, numeric thresholds, or dynamic input cell references.

1. Implementing OR Logic Across Categories

In Google Sheets FILTER formulas, separate arguments act as logical AND conditions. To implement logical OR conditions (e.g. finding projects in either “Marketing” or “Sales”), use the addition (+) operator between criteria blocks:

=TEXTJOIN("; ", TRUE, FILTER(A2:A100, (B2:B100 = "Marketing") + (B2:B100 = "Sales")))

2. Filtering by Numeric Ranges and Dates

To extract invoice identifiers for transactions greater than £5,000 recorded after 1 January 2026:

=TEXTJOIN(", ", TRUE, FILTER(A2:A100, (C2:C100 > 5000) * (D2:D100 >= DATE(2026, 1, 1))))

The multiplication operator (*) represents logical AND when combining complex numeric expressions inside array filters.

3. Formatting with In-Cell Line Breaks

To render the concatenated results as an organized vertical list inside a single cell rather than a horizontal comma-delimited string, use CHAR(10) as the delimiter:

=TEXTJOIN(CHAR(10), TRUE, FILTER(B2:B100, A2:A100 = E2))

Ensure that text wrapping is enabled on the destination cell via Format > Wrapping > Wrap so the line breaks render correctly.

Handling Empty Filter Matches Gracefully

If no records meet the specified criteria, the FILTER function returns a #N/A error (“No matches are found in FILTER evaluation”), which propagates to TEXTJOIN. To maintain professional summary tables, wrap the filter inside IFNA or provide the third argument of FILTER:

=TEXTJOIN(", ", TRUE, IFERROR(FILTER(B2:B100, A2:A100 = "NonExistent"), "None"))

Alternatively, use the native third fallback parameter of FILTER directly:

=TEXTJOIN(", ", TRUE, FILTER(B2:B100, A2:A100 = E2, "No items found"))

Troubleshooting Common Formula Glitches

• Mismatched Array Lengths: In FILTER(range, condition), the criteria range must have the exact same starting and ending row indices as the target range (e.g. A2:A100 paired with B2:B100). Specifying mismatched ranges like A2:A100 and B2:B50 produces a #VALUE! dimension error.
• Case Sensitivity: Standard text comparisons in FILTER (e.g. A2:A100 = "Completed") are case-insensitive. If strict case-sensitive matching is required, wrap the condition in EXACT(A2:A100, "COMPLETED").
• Cell Character Limit: Google Sheets enforces a limit of 50,000 characters per cell. When concatenating large enterprise tables, verify that the total output string does not exceed cell capacity.