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.

Dataset for Concatenate If Cell Values Match in Excel


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:
=TEXTJOIN(",",TRUE,IF($B$5:$B$14=E5,$C$5:$C$14,""))

Connect Cell Values with Combination of  TEXTJOIN & IF Functions

  • Press Enter to get the result.

Using TEXTJOIN Function to Concatenate If Match in Excel

How Does the Formula Work?

  • IF($B$5:$B$14=E5,$C$5:$C$14,””): The IF function checks if the value in cell E5 has matches in the cell range B5:B14. If the logical_test is True then the formula returns corresponding values from the cell range C5:C14.
  • TEXTJOIN(“,”,TRUE,IF($B$5:$B$14=E5,$C$5:$C$14,””)): The TEXTJOIN function joins the values returned by the IF function with the given delimiter of a comma (“,”).
  • Drag the Fill Handle to copy the formula down to cell F7.

Dragging Fill Handle to Copy Formula for Concatenate If Match in Excel

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:
=CONCAT(IF($B$5:$B$14=E5,$C$5:$C$14&",",""))

Insert Excel CONCAT & IF Functions to Link Cell Values

  • Press Enter.

Applying CONCAT Function to Concatenate If Match in Excel

How Does the Formula Work?

  • IF($B$5:$B$14=E5,$C$5:$C$14&”,”,””): The IF function checks if the value in cell E5 has matches in the cell range B5:B14. If the logical_test is True then the formula returns corresponding values from the cell range C5:C14 with a delimiter of a comma (“,”).
  • CONCAT(IF($B$5:$B$14=E5,$C$5:$C$14&”,”,””)): The CONCAT function joins the values returned by the IF function.
  • 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:
=IF(B5<>B4,C5,CONCATENATE(D4,",",C5))

Employ Functions & Filter to Concatenate Values If Match

  • Press Enter.

Using IF Function to Concatenate if Match in Excel

How Does the Formula Work?

  • IF(B5<>B4,C5,CONCATENATE(D4,”,”,C5)): The IF function checks if the value in cell B5 is not equal to the value in cell B4. If the logical_test is True then the formula will return the value in cell C5. Otherwise, it will execute the CONCATENATE function.
  • CONCATENATE(D4,”,”,C5): The CONCATENATE function joins the value in cell D4 with the value in cell C5 with a delimiter of a comma (“,”).
  • Drag the Fill Handle down to copy the formula.

The results are as follows.

  • In cell E5, enter the following formula:
=IF(B5<>B6,CONCATENATE(B5,",""",D5,""""),"")

Employing CONCATENATE Function to Concatenate If Match in Excel

  • Press Enter to get the result.

How Does the Formula Work?

  • IF(B5<>B6,CONCATENATE(B5,”,”””,D5,””””),””): The IF function checks if the value in cell B5 is not equal to the value in cell B6. If the logical_test is True then the formula will execute the CONCATENATE function. Otherwise, it will return a blank.
  • CONCATENATE(B5,”,”””,D5,””””): Now, the CONCATENATE function will combine the texts.
  • 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.

Adding Filter to Concatenate If Match in Excel

A filter is added to this dataset.

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

Filtering The Non-blank Cells to Concatenate If Match in Excel

  • 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.

Combine CONCATENATE & IF Functions to Join When Values Are Matched

Steps:

  • In cell F5 enter the following formula:
=CONCATENATE(B5," ",IF(ISBLANK(C5),"",C5&" "),D5)

Combining CONCATENATE and IF functions to Concatenate if Values Match in Excel

  • Press Enter.

How Does the Formula Work?

  • ISBLANK(C5): The ISBLANK function will return True if cell C5 is blank. Otherwise, it will return False.
  • IF(ISBLANK(C5),””,C5&” “): The IF function checks for matches and if the logical_test is True returns blank. Otherwise, it returns the value in cell C5 followed by a space.
  • CONCATENATE(B5,” “,IF(ISBLANK(C5),””,C5&” “),D5): The CONCATENATE function will join the resultant text.
  • Drag the Fill Handle down to copy the formula to the other cells.

Dragging Fill Handle to Copy Formula with ISBLANK function to Concatenate If Match in Excel

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:
=IF(COUNTA(C5)=0,B5&" "&D5,B5&" "&C5&" "&D5)

Use COUNTA Function to Concatenate If Cell Values Match in Excel

  • Press Enter to get the result.

Use of COUNTA Function to Concatenate if Match in Excel

How Does the Formula Work?

  • COUNTA(C5): The COUNTA function returns the number of cells containing any values.
  • IF(COUNTA(C5)=0,B5&” “&D5,B5&” “&C5&” “&D5): The IF function checks if the COUNTA function returns 0. If the logical_test is True then the formula will concatenate the values in cells B5 and D5. Otherwise, it will concatenate the values in cells B5, C5, and D5.
  • 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.

Apply VBA to Add Cell Values If Match in Excel

The Visual Basic Editor window will open.

  • Select the Insert tab.
  • Select Module.

Inserting Module to Concatenate IF Match in Excel

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

VBA code to Conactenate if Match in Excel

How Does the Code Work?

  • We create a Sub Procedure named Concatenate_If_Match.
  • We declare the variables.
  • We use a Set Statement to set where we want the output.
  • We use a For Next Loop to go through the input table.
  • We use the ReDim Statement to size the declared array.
  • We use another For Next Loop to go through the columns.
  • We use an IF Statement to check for a match.
  • We end the Sub Procedure.
  • Save the code and go back to the worksheet.
  • Go to the Developer tab.
  • Select Macros.

Running Macros to Conacatenate if Match in Excel

The Macro dialog box will appear.

  • Select Concatenate_If_Match as Macro Name.
  • Click Run.

Macro Dialog Box to Concatenate If Match in Excel

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.

Combine Values If Match Using User Defined Function

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

VBA Code for User Defined Functio to Concatenate If Match in Excel

How Does the Code Work?

  • We create a Function named CONCATENATE_IF.
  • We declare the variables for the function.
  • We use an If Statement to check for a match.
  • We use the CVErr function to return a user defined error.
  • We use a For Next Loop to go through the whole range.
  • We use another If Statement to find a match and then return results accordingly.
  • We end the Sub Procedure.
  • Save the code and go back to the worksheet.
  • In cell F5 enter the following formula:
=CONCATENATE_IF($B$5:$B$14,E5,$C$5:$C$14,",")

  • Press Enter to get the result.

Using CONCATENATE_IF function to Concatenate Cell Values if Match in Excel

Here, we selected cell range B5:B14 as criteria_range, cell E5 as criteria, cell range C5:C14 as concatenate_range, and “,” as Delimiter. The formula will concatenate the values from the concatenate_range if the criteria match the criteria_range.
  • 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

Get FREE Advanced Excel Exercises with Solutions!
Mashhura Jahan
Mashhura Jahan

Mashhura Jahan, BSc in Industrial and Production Engineering from Bangladesh University of Engineering and Technology, invested 1.5 years at ExcelDemy. In roles like Excel & VBA Content Developer, Excel Charts, and Dashboard course trainer, she wrote 90+ articles. She was previously part of the forum support team and a junior software analyst on the Excel Add-in project. With interests spanning Excel, VBA, Power Query, Python, Data Science, and Software Development, Mashhura brings a diverse skill set to her... Read Full Bio

2 Comments
  1. In my case TEXTJOIN in Method 1 returns all values, not only those matched by IF. Does it really work?

    • Hello Undent,

      Sorry to hear your problem. But our Method-1 is working perfectly. Can you check your IF condition based on your case or dataset.
      Here, I’m attaching a image where IF function returns the values based on the conditions.

      If you want you can share your case here.

      Regards
      ExcelDemy

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo