Excel Formula to Concatenate Array Values Based on Conditions

Consolidating scattered, conditional text data in Excel-such as matching project milestones to specific statuses-often leads to tedious manual copying. For organizations managing complex portfolios, tracking standard funding sources like federal awards and private grants requires precise, consolidated reporting. Leveraging the dynamic TEXTJOIN function nested with IF grants analysts the ability to instantly aggregate related array values into a single cell based on specific criteria. However, this method carries the stipulation of requiring Excel 2019 or Microsoft 365. Leading research institutions successfully use this syntax to merge recipient lists under specific NSF grants. Below, we outline the exact formula structure and application steps.

In data analysis, we often need to consolidate information from a table into a single, summarized view. A common challenge Excel users face is attempting to concatenate (join) values from an array or list based on one or more specific conditions. For instance, you might want to list all projects assigned to a specific employee, display all products belonging to a particular category, or compile a list of late tasks-all within a single cell, separated by commas.

Historically, achieving this in Excel required complex VBA macros or cumbersome helper columns. However, with the introduction of modern dynamic array functions and the powerful TEXTJOIN function, this task has become remarkably simple and elegant. This comprehensive guide will walk you through the modern, intermediate, and legacy methods to concatenate array values if a condition is met.

The Modern Approach: TEXTJOIN and FILTER (Excel 365 & Excel 2021+)

If you are using Microsoft 365, Excel for the Web, or Excel 2021, you have access to dynamic arrays. The absolute best way to conditionally concatenate values is by combining the TEXTJOIN function with the FILTER function.

Understanding the Component Functions

• TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...): This function joins multiple text strings together using a specified delimiter (such as a comma, space, or hyphen) and gives you the option to ignore empty cells.
• FILTER(array, include, [if_empty]): This function filters an array of data based on a boolean (True/False) condition you define.

Step-by-Step Example

Let's assume we have the following dataset containing department assignments for various employees:

Our goal is to concatenate all the Departments assigned to John Doe into a single cell, separated by a comma and a space.

The Formula

=TEXTJOIN(", ", TRUE, FILTER(B2:B7, A2:A7="John Doe"))

How It Works

1. The FILTER Step: Excel evaluates the criteria A2:A7="John Doe". This returns an array of Boolean values: {TRUE; FALSE; TRUE; FALSE; FALSE; TRUE}.
2. The FILTER function applies this array to the target range B2:B7 and returns only the values that correspond to TRUE. The output is a virtual array: {"Sales"; "IT Support"; "Operations"}.
3. The TEXTJOIN Step: The TEXTJOIN function takes this array, uses ", " as the delimiter, skips any blank entries (since the second argument is set to TRUE), and merges the values.
4. Final Result: Sales, IT Support, Operations

Handling Multiple Conditions

One of the greatest advantages of the TEXTJOIN + FILTER approach is its scalability. If you need to filter by multiple criteria (e.g., matching the employee's name and ensuring their status is "Active"), you can use boolean logic multiplication within the FILTER function.

To join departments for John Doe where the status is Active, use the following formula:

=TEXTJOIN(", ", TRUE, FILTER(B2:B7, (A2:A7="John Doe") * (C2:C7="Active")))

Explanation of Boolean Logic:

• (A2:A7="John Doe") generates: {TRUE; FALSE; TRUE; FALSE; FALSE; TRUE} (or {1; 0; 1; 0; 0; 1})
• (C2:C7="Active") generates: {TRUE; TRUE; FALSE; TRUE; TRUE; TRUE} (or {1; 1; 0; 1; 1; 1})
• Multiplying these two arrays acts as an AND operator: {1*1; 0*1; 1*0; 0*1; 0*1; 1*1} which simplifies to {1; 0; 0; 0; 0; 1} (where 1 is TRUE and 0 is FALSE).
• The FILTER function returns {"Sales"; "Operations"}.
• TEXTJOIN combines them to produce: Sales, Operations.

Advanced Options: Sorting and Removing Duplicates

Often, the filtered data may contain duplicate values, or you may want the concatenated string to appear in alphabetical order. You can easily nest additional dynamic array functions inside your formula.

1. Removing Duplicates (UNIQUE)

If you want to ensure each concatenated value is unique, wrap the FILTER function inside the UNIQUE function:

=TEXTJOIN(", ", TRUE, UNIQUE(FILTER(B2:B7, A2:A7="John Doe")))

2. Sorting Alphabetically (SORT)

To sort the list before concatenating, wrap the array in the SORT function:

=TEXTJOIN(", ", TRUE, SORT(FILTER(B2:B7, A2:A7="John Doe")))

3. Combining Both (SORT & UNIQUE)

To get a sorted, unique list of concatenated items:

=TEXTJOIN(", ", TRUE, SORT(UNIQUE(FILTER(B2:B7, A2:A7="John Doe"))))

The Intermediate Approach: TEXTJOIN and IF (Excel 2016 & Excel 2019)

If you are using Excel 2016 or Excel 2019, you have access to the TEXTJOIN function, but you do not have the FILTER function. In these versions, you must use a traditional array formula using the IF function.

The Formula

=TEXTJOIN(", ", TRUE, IF(A2:A7="John Doe", B2:B7, ""))

Note: Since this is an array formula in older Excel versions, you must press Ctrl + Shift + Enter instead of just Enter. When entered correctly, Excel will automatically wrap the formula in curly braces {...}.

How It Works

1. The IF statement checks each cell in range A2:A7. If a cell equals "John Doe", it returns the corresponding cell value from B2:B7. If not, it returns an empty string ("").
2. This evaluates to the array: {"Sales"; ""; "IT Support"; ""; ""; "Operations"}.
3. The TEXTJOIN function steps in. Because the second argument is set to TRUE (ignore empty cells), it ignores all the empty strings ("") generated by the False condition.
4. The resulting string is: Sales, IT Support, Operations.

The Legacy Approach: Excel 2013 and Older (Using VBA)

Prior to Excel 2016, there was no native TEXTJOIN function. The built-in CONCATENATE and CONCAT functions do not support array evaluations combined with delimiters, making this task notoriously difficult without VBA.

If you are working on an older version of Excel, the most efficient workaround is to write a User Defined Function (UDF) in VBA. Here is a simple macro code to create a custom CONCATIF function:

Function CONCATIF(CriteriaRange As Range, Criteria As Variant, ConcatRange As Range, Optional Delimiter As String = ", ") As String
    Dim Result As String
    Dim i As Long
    
    For i = 1 To CriteriaRange.Cells.Count
        If CriteriaRange.Cells(i).Value = Criteria Then
            If Result = "" Then
                Result = ConcatRange.Cells(i).Value
            Else
                Result = Result & Delimiter & ConcatRange.Cells(i).Value
            End If
        End If
    Next i
    
    CONCATIF = Result
End Function

How to use this VBA function:

1. Press Alt + F11 to open the VBA Editor.
2. Click Insert > Module.
3. Paste the code above into the module window.
4. Close the VBA Editor and return to your worksheet.
5. Now, you can use the custom function in your worksheet like any native formula:

=CONCATIF(A2:A7, "John Doe", B2:B7, ", ")

Troubleshooting Common Errors

• #NAME? Error: This occurs if you try to use TEXTJOIN or FILTER on an older version of Excel that does not support them. Double-check your Office version.
• #CALC! Error: This occurs within the FILTER function when no records match your criteria. To prevent this, provide a fallback value in the third argument of the FILTER function:
  =TEXTJOIN(", ", TRUE, FILTER(B2:B7, A2:A7="No Match", "No Results Found"))
• Extra Delimiters: If you are getting extra commas in your output, ensure that the second argument of TEXTJOIN is set to TRUE so that it actively ignores blank values.

Summary of Best Practices

For the best performance and cleanest worksheets, use the modern TEXTJOIN + FILTER combination if your environment supports it. This combination is dynamic, recalculates quickly, and avoids the security risks and macro-enabled file requirements (.xlsm) associated with custom VBA code. If you are developing spreadsheets to be shared across a diverse user base with varying Excel versions, using the TEXTJOIN + IF method with Ctrl+Shift+Enter serves as a highly compatible middle ground.