How to Use the TEXTJOIN Function with Conditional Criteria in Microsoft Excel

In Microsoft Excel, standard concatenation functions like CONCAT or TEXTJOIN merge strings across ranges, but spreadsheets frequently require concatenating only the values that meet specific conditions—such as generating a comma-separated list of completed tasks, concatenating client emails matching a specific region, or aggregating product orders assigned to a single project code.

While Excel does not offer a standalone “TEXTJOINIFS” function, you can achieve powerful conditional concatenation by nesting modern dynamic array functions—specifically combining TEXTJOIN with either FILTER or an IF array expression. This comprehensive guide explains how to build robust conditional string concatenations with single and multiple criteria in Microsoft Excel.

Syntax and Fundamentals of the TEXTJOIN Function

The TEXTJOIN function syntax requires three core arguments:

=TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)
  • delimiter: The separator character inserted between values (such as ", ", " - ", or a line break).
  • ignore_empty: A boolean value (TRUE or FALSE). Setting this to TRUE ensures blank strings or empty results generated by conditional tests do not produce double commas or orphan delimiters.
  • text1, [text2], …: The strings, cell ranges, or dynamically evaluated arrays you wish to merge.

Method 1: TEXTJOIN with the FILTER Function (Recommended)

For Microsoft 365, Office 2021, and Excel for the Web, combining TEXTJOIN with FILTER represents the most efficient, performant, and legible approach. FILTER extracts only the items meeting your criteria into memory before passing them to TEXTJOIN.

Single Condition Example

Suppose you have a project tracking sheet where column A contains Employee Names, column B contains Department (e.g., “Engineering”), and column C contains Project Status. To combine all employee names belonging to the “Engineering” department into a single comma-separated cell:

=TEXTJOIN(", ", TRUE, FILTER(A2:A50, B2:B50="Engineering", "No matches"))

In this formula:

  1. B2:B50="Engineering" evaluates to an array of TRUE and FALSE values.
  2. FILTER(A2:A50, ...) extracts names where the corresponding row equals “Engineering”.
  3. If no matching rows exist, FILTER safely returns "No matches" to prevent an #N/A error.
  4. TEXTJOIN joins the extracted array elements, separated by a comma and a space.

Multiple Conditions (AND Logic)

To concatenate values where multiple criteria must be satisfied simultaneously, multiply criteria conditions using standard boolean logic inside the include argument of FILTER:

=TEXTJOIN(", ", TRUE, FILTER(A2:A50, (B2:B50="Engineering") * (C2:C50="Completed"), "No matches"))

Here, the multiplication operator (*) acts as an AND condition: only rows where Department is “Engineering” and Status is “Completed” yield 1 (TRUE) and pass through the filter.

Multiple Conditions (OR Logic)

To join values that match either condition (for instance, employees in “Engineering” OR “Product”):

=TEXTJOIN(", ", TRUE, FILTER(A2:A50, (B2:B50="Engineering") + (B2:B50="Product"), "No matches"))

The addition operator (+) operates as an OR condition in array calculations.

Method 2: TEXTJOIN with an IF Array Expression

If you are working in environments where FILTER is unavailable or when constructing formulas that rely on classic array mechanics, you can nest an IF condition directly within TEXTJOIN.

Formula Structure

=TEXTJOIN(", ", TRUE, IF(B2:B50="Engineering", A2:A50, ""))

How this formula functions:

  1. The IF function tests the range B2:B50.
  2. For every row matching “Engineering”, it returns the corresponding cell in A2:A50.
  3. For every non-matching row, it outputs an empty string ("").
  4. Because ignore_empty is set to TRUE, TEXTJOIN discards all empty strings and concatenates only the surviving values.

Note for Legacy Excel: In Excel 2019 or Excel 2016 (with dynamic array updates), array formulas that do not spill automatically may require confirming the formula using Ctrl + Shift + Enter rather than a standard Enter keystroke.

Advanced Application: Conditional TEXTJOIN with UNIQUE and SORT

Real-world datasets often contain redundant duplicate records. You can nest UNIQUE and SORT alongside FILTER inside TEXTJOIN to produce clean, alphabetically sorted lists without repetition:

=TEXTJOIN("; ", TRUE, SORT(UNIQUE(FILTER(A2:A100, (C2:C100="Overdue") * (A2:A100<>""), ""))))

This workflow performs four automated operations within a single cell:

  • Filters column A for records marked “Overdue” while excluding blank rows.
  • Strips out duplicate entries via UNIQUE.
  • Sorts remaining records in alphabetical order via SORT.
  • Merges them using semicolons as delimiters.

Formatting Line Breaks as Delimiters

To display each conditionally matched item on its own separate line inside a single worksheet cell:

  1. Use the line-feed character CHAR(10) on Windows (or CHAR(13) on macOS) as the delimiter:
    =TEXTJOIN(CHAR(10), TRUE, FILTER(A2:A50, B2:B50="Marketing"))
  2. Select the formula cell, open the Home tab on the Excel ribbon, and enable Wrap Text. The concatenated strings will now display on separate vertical lines within the cell.

Get the best tech tips delivered straight to your inbox.

Join thousands of readers mastering Apple, Google, Microsoft, and Linux.