Excel Formulas to Concatenate Columns Based on Criteria

Manually consolidating rows of conditional data in Excel is a tedious, error-prone struggle for data analysts. While tracking standard funding sources-such as federal grants, corporate sponsorships, or capital allocations-is routine, aggregating their distributed project codes into a single cell often stalls reporting.

Utilizing a dynamic concatenation formula solves this, offering immediate visibility into consolidated portfolios. Notably, this method carries the stipulation of using modern Excel engines that support the TEXTJOIN and FILTER functions. For example, you can instantly group all municipal initiatives funded by "Grant A" into a single, comma-separated list. Below, we detail the step-by-step formula construction to streamline your reporting.

In Excel, combining text from multiple cells is a standard task that most users solve using basic tools like the ampersand (& ) operator, CONCATENATE, or the newer CONCAT function. However, a common challenge arises when you need to merge values from a column only if they meet specific criteria. Essentially, what you need is a "CONCATIF" function.

While Microsoft Excel does not feature a native, standalone CONCATIF function, you can easily build this functionality. Depending on your version of Excel, you can use powerful combinations of modern dynamic array formulas, older array methods, or even custom VBA functions. This comprehensive guide walks you through the best methods to concatenate columns based on criteria.

The Sample Dataset

To understand these formulas in action, we will reference the sample dataset below. Our goal is to extract and concatenate a list of team members assigned to specific projects.

Method 1: The Modern Standard (Excel 365 & Excel 2021)

If you are using modern Excel (Microsoft 365 or Excel 2021), the absolute best way to concatenate a column based on criteria is by combining the TEXTJOIN and FILTER functions.

The Formula Syntax

=TEXTJOIN(delimiter, ignore_empty, FILTER(array, include, [if_empty]))

How It Works

• FILTER inspects your specified array and returns only the rows that match your criteria.
• TEXTJOIN takes those filtered results, joins them together using a specified; as a comma or semicolon), and ignores any empty values.

Step-by-Step Example

To list all team members assigned to "Project Alpha", use the following formula:

=TEXTJOIN(", ", TRUE, FILTER(B2:B7, A2:A7="Project Alpha"))

Result: Alice, Charlie, Evan

Handling Multiple Criteria

The beauty of the FILTER function is that it easily accommodates multiple criteria using boolean logic (multiplication * for AND, addition + for OR).

For instance, to concatenate team members assigned to "Project Alpha" who are also Active:

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

Result: Alice, Evan (Charlie is omitted because his status is Inactive).

Method 2: The Legacy Compatibility Formula (Excel 2019)

If you are using Excel 2019, you have access to TEXTJOIN, but you do not have the FILTER function. To get around this limitation, you can nest an IF statement inside the TEXTJOIN function.

The Formula Syntax

=TEXTJOIN(delimiter, ignore_empty, IF(criteria_range = criteria, value_range, ""))

Step-by-Step Example

To achieve the same Project Alpha join in Excel 2019, enter the following formula:

=TEXTJOIN(", ", TRUE, IF(A2:A7="Project Alpha", B2:B7, ""))

Crucial Step for Excel 2019 and Older: Because this is an array formula, you must commit the formula by pressing Ctrl + Shift + Enter instead of just Enter. When done correctly, Excel will wrap your formula in curly braces like this: {=TEXTJOIN(...)}.

How It Works

The IF statement evaluates every row. If a row matches "Project Alpha", it returns the corresponding name from Column B. If it does not match, it returns an empty string (""). The array looks like this internally: {"Alice", "", "Charlie", "", "Evan", ""}. By setting the second argument of TEXTJOIN to TRUE, Excel ignores all those blank strings, leaving you with: Alice, Charlie, Evan.

Method 3: Removing Duplicates (Excel 365 & 2021)

In real-world data, matching values might repeat, resulting in redundant outputs. If you want to merge names but avoid duplicates, you can nest the UNIQUE function inside your formula.

Step-by-Step Example

Assuming "Alice" was accidentally listed twice under Project Alpha, you can clean your output like this:

=TEXTJOIN(", ", TRUE, UNIQUE(FILTER(B2:B7, A2:A7="Project Alpha")))

This filters the list first, extracts only unique names, and then merges them smoothly.

Method 4: The VBA Custom Function (For Excel 2016 and Older)

If you are using Excel 2016 or older, you do not have access to TEXTJOIN. The native CONCATENATE function cannot process arrays of data, which leaves you with two choices: tedious manual concatenating, or writing a custom User Defined Function (UDF) using VBA.

Creating a CONCATIF function in VBA is clean, simple, and runs on all older versions of Excel.

How to Add the VBA Code

1. Open your Excel workbook and press Alt + F11 to open the VBA Editor.
2. Click Insert > Module from the top menu.
3. Copy and paste the following VBA code into the empty module window:

Function CONCATIF(CriteriaRange As Range, Criteria As Variant, ConcatenateRange As Range, Optional;
String = ", ");
String
    Dim i;
Long
    Dim Result;
String
    
    ' Loop through all cells in the criteria range
    For i = 1 To CriteriaRange.Cells.Count
        ' Check if the cell matches the criteria
        If CriteriaRange.Cells(i).Value = Criteria Then
            ' Avoid prepending delimiter on the first item
            If Result = "" Then
                Result = ConcatenateRange.Cells(i).Value
            Else
                Result = Result & Delimiter & ConcatenateRange.Cells(i).Value
            End If
        End If
    Next i
    
    CONCATIF = Result
End Function

4. Close the VBA Editor and return to your spreadsheet. Save your workbook as an Excel Macro-Enabled Workbook (.xlsm).

Using Your Custom Function

You can now use this newly created function just like any other Excel formula. In an empty cell, write:

=CONCATIF(A2:A7, "Project Alpha", B2:B7, ", ")

Result: Alice, Charlie, Evan

Method 5: Combining and Formatting with Power Query

If you are working with large datasets containing thousands of rows, complex Excel formulas can slow down your workbook. Power Query is an excellent, scalable alternative that does not require writing formulas.

Step-by-Step Power Query Process

1. Select your dataset (A1:C7) and go to the Data tab, then click From Table/Range.
2. In the Power Query Editor, go to the Transform tab and click Group By.
3. In the pop-up window:
   • Set "Group by" to Project ID.
   • Set "New column name" to Team.
   • Set "Operation" to All Rows.
4. Click OK. This will create a column containing nested tables.
5. Go to the Add Column tab and select Custom Column.
6. Name the column and enter the following M-code formula:

   Text.Combine([Team][Team Member], ", ")

7. Click OK, delete the intermediate "Team" table column, and then click Close & Load to return the clean, aggregated list back to your Excel workbook.

Summary of Methods

By selecting the method that aligns with your Excel environment and data size, you can easily automate textual lookups and generate clean, condensed reports with ease.