How to Concatenate If Cell Values Match in Excel (7 Easy Ways)

To demonstrate our methods we’ll use the following dataset, containing columns for State and Sales Person. We’ll concatenate the values in the Sales Person column if the States match.

Method 1 – Combining TEXTJOIN & IF Functions

We can use the TEXTJOIN function, and the IF function to concatenate if values match in Excel.

Steps:

• In cell F5 (the cell where we want to concatenate values), enter the following formula:

• Press Enter to get the result.

How Does the Formula Work?

• Drag the Fill Handle to copy the formula down to cell F7.

The results are as follows.

Read More: How to Concatenate Cells with If Condition in Excel

Method 2 – Using CONCAT & IF Functions

The CONCAT function joins multiple texts from different strings.

Steps:

• In cell F5 enter the following formula:

• Press Enter.

How Does the Formula Work?

• Drag the Fill Handle down to copy the formula.

The results are as follows.

Method 3 – Using Functions & Filter

We can use functions to concatenate if the values match, then Filter the results to return the desired output. To apply this method, the values must be stored together in the same dataset.

Steps:

• In cell D5 enter the following formula:

• Press Enter.

How Does the Formula Work?

• Drag the Fill Handle down to copy the formula.

The results are as follows.

• In cell E5, enter the following formula:

• Press Enter to get the result.

How Does the Formula Work?

• Drag the Fill Handle down to copy the formula.

The results are as follows. Some cells are blank.

Now we’ll filter the column to get rid of the blank cells.

• Select the column header where you want to apply the filter (cell E4).
• Go to the Data tab.
• Select Filter.

A filter is added to this dataset.

• Click on the filter button on cell E4.
• Uncheck the blank option.
• Click OK.

• The blank cells are filtered out.

Read More: Combine CONCATENATE & TRANSPOSE Functions in Excel

Method 4 – Combining CONCATENATE & IF Functions

For this example, we’ll use a different dataset containing 3 columns; First Name, Middle Name, and Last Name. The Middle Name column contains some blank cells. Let’s match the blanks and then concatenate the values accordingly if a match is found or not.

Steps:

• In cell F5 enter the following formula:

• Press Enter.

How Does the Formula Work?

• Drag the Fill Handle down to copy the formula to the other cells.

Full Names include Middle Names where those are present in column C, otherwise they don’t.

Method 5 – Using COUNTA Function

The COUNTA function counts cells containing any kind of information, and can be used to find the blank cells, then used in conjunction with the IF function to concatenate the matches accordingly.

Steps:

• In cell F5 enter the following formula:

• Press Enter to get the result.

How Does the Formula Work?

• Drag the Fill Handle down to copy the formula.

• The results are as follows.

Method 6 – Using VBA Code

The VBA macro below will concatenate cell values if they match, and return the results with the column header.

Steps:

• Go to the Developer tab.
• Select Visual Basic.

The Visual Basic Editor window will open.

• Select the Insert tab.
• Select Module.

A module will open.

• Enter the following code in the module:

Sub Concatenate_If_Match()
    Dim t_col As New Collection
    Dim inp_table As Variant
    Dim output() As Variant
    Dim m As Long
    Dim col_no As Long
    Dim rng As Range
    inp_table = Range("B4", Cells(Rows.Count, "B").End(xlUp)).Resize(, 2)
    Set rng = Range("E4")
    On Error Resume Next
    For m = 2 To UBound(inp_table)
        t_col.Add inp_table(m, 1), TypeName(inp_table(m, 1)) & CStr(inp_table(m, 1))
    Next m
    On Error GoTo 0
    ReDim output(1 To t_col.Count + 1, 1 To 2)
    output(1, 1) = "State"
    output(1, 2) = "Sales Person"
    For m = 1 To t_col.Count
        output(m + 1, 1) = t_col(m)
        For col_no = 2 To UBound(inp_table)
            If inp_table(col_no, 1) = output(m + 1, 1) Then
                output(m + 1, 2) = output(m + 1, 2) & ", " & inp_table(col_no, 2)
            End If
        Next col_no
        output(m + 1, 2) = Mid(output(m + 1, 2), 2)
    Next m
    Set rng = rng.Resize(UBound(output, 1), UBound(output, 2))
    rng.NumberFormat = "@"
    rng = output
    rng.EntireColumn.AutoFit
End Sub

How Does the Code Work?

• Save the code and go back to the worksheet.
• Go to the Developer tab.
• Select Macros.

The Macro dialog box will appear.

• Select Concatenate_If_Match as Macro Name.
• Click Run.

The desired output is returned.

Method 7 – Using a User Defined Function

A user defined function is a function defined by the user for a specific task, and created by writing a VBA code. Let’s create a User defined function to concatenate if cell values match.

Steps:

• Go to the Developer tab.
• Select Visual Basic.

The Visual Basic Editor window will open.

• Go to the Insert tab.
• Select the Module.

A module will open.

• Enter the following code in the module:

Function CONCATENATE_IF(criteria_range As Range, criteria As Variant, _
concatenate_range As Range, Optional Delimiter As String = ",") As Variant
Dim Results As String
On Error Resume Next
If criteria_range.Count <> concatenate_range.Count Then
    CONCATENATE_IF = CVErr(xlErrRef)
    Exit Function
End If
For J = 1 To criteria_range.Count
    If criteria_range.Cells(J).Value = criteria Then
        Results = Results & Delimiter & concatenate_range.Cells(J).Value
    End If
Next J
If Results <> "" Then
    Results = VBA.Mid(Results, VBA.Len(Delimiter) + 1)
End If
CONCATENATE_IF = Results
Exit Function
End Function

How Does the Code Work?

• Save the code and go back to the worksheet.
• In cell F5 enter the following formula:

• Press Enter to get the result.

• Drag the Fill Handle down to copy the formula.

• The desired output is returned.

Things to Remember

• If you use VBA in your Excel workbook then you must save the Excel file as Excel Macro-Enabled Workbook. Otherwise, the VBA code will not work.

Download Practice Workbook

Related Articles

• How to Concatenate Arrays in Excel
• How to Concatenate with VLOOKUP in Excel
• How to Concatenate Email Addresses in Excel
• How to Concatenate Decimal Places in Excel
• How to Concatenate Different Fonts in Excel