If you want to represent the values that match certain criteria in a cell then this article will be helpful for you. The main objective of this article is to explain how to concatenate if cell values match in Excel.

**Table of Contents**hide

## How to Concatenate If Cell Values Match in Excel: 7 Easy Ways

To explain this article, I have taken the following dataset. It contains the State and Sales Person. I will use this dataset to concatenate in Excel. We will concatenate the values in the Sales Person column if the States match in Excel.

### 1. Connect Cell Values with Combination of TEXTJOIN & IF Functions

You can use the** TEXTJOIN function**, and the** IF function** to concatenate if values match in Excel. Let’s see the steps.

**Steps:**

- Firstly, select the cell where you want to concatenate values. Here, I selected cell
**F5**. - Secondly, in cell
**F5**write the following formula.

`=TEXTJOIN(",",TRUE,IF($B$5:$B$14=E5,$C$5:$C$14,""))`

- Thirdly, press
**Enter**to get the result.

**🔎** **How Does the Formula Work?**

**IF($B$5:$B$14=E5,$C$5:$C$14,””):**Here, the**IF function**checks if the value in cell**E5**matches the cell range**B5:B14**. If the**logical_test**is**True**then the formula returns values from the cell range**C5:C14**.**TEXTJOIN(“,”,TRUE,IF($B$5:$B$14=E5,$C$5:$C$14,””)):**Now, the**TEXTJOIN function**joins the values it got from the**IF function**with the given**delimiter**.

- After that, drag the
**Fill Handle**to copy the formula.

- Now, you can see that I have copied the formula to the other cells.

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

### 2. Insert Excel CONCAT & IF Functions to Link Cell Values

You can use the** IF function** for logical comparison. And, the** CONCAT function** joins multiple texts from different strings. You can use these two functions together to concatenate if cell values match in Excel. Let’s see the steps.

**Steps:**

- In the beginning, select the cell where you want to concatenate. Here, I selected cell
**F5**. - Next, in cell
**F5**write the following formula.

`=CONCAT(IF($B$5:$B$14=E5,$C$5:$C$14&",",""))`

- Then, press
**Enter**.

**🔎** **How Does the Formula Work?**

**IF($B$5:$B$14=E5,$C$5:$C$14&”,”,””):**Here, the**IF**function checks if the value in cell**E5**matches the cell range**B5:B14**. If the**logical_test**is**True**then the formula returns values from the cell range**C5:C14**with a delimiter after the value.**CONCAT(IF($B$5:$B$14=E5,$C$5:$C$14&”,”,””)):**Now, the**CONCAT**function will join the values it got from the**IF**function.

- After that, drag the
**Fill Handle**down to copy the formula.

- In the end, you can see that I have copied the formula to the other cells.

### 3. Employ Functions & Filter to Concatenate Values If Match Is Found

In this method, I will use functions to concatenate if the values match and **filter** the results to get my desired output. To apply this method you must store the same values together in the dataset. Let’s see how it is done.

**Steps:**

- First, select the cell where you want to concatenate the values.
- Then, write the following formula in that selected cell.

`=IF(B5<>B4,C5,CONCATENATE(D4,",",C5))`

- Next, press
**Enter**.

**🔎** **How Does the Formula Work?**

**IF(B5<>B4,C5,CONCATENATE(D4,”,”,C5)):**Here, the**IF**function will check 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 go into the**CONCATENATE function.****CONCATENATE(D4,”,”,C5):**Now, the**CONCATENATE function**will join the value in cell**D4**with the value in cell**C5**with a**delimiter**.

- After that, drag the
**Fill Handle**down to copy the formula.

- Finally, you can see that I have copied the formula to the other cells.

- Secondly, select the cell where you want the final result. Here, I selected cell
**E5**. - Now, write the following formula in cell
**E5**.

`=IF(B5<>B6,CONCATENATE(B5,",""",D5,""""),"")`

- Further, press
**Enter**to get the result.

**🔎** **How Does the Formula Work?**

**IF(B5<>B6,CONCATENATE(B5,”,”””,D5,””””),””):**Here, the**IF**function will check 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 go into 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.

- In the following picture, you can see that I have copied the formula to the other cells and got my desired results.

- Thirdly, I will
**filter**the column to get rid of the blank cells. - Select the column header where you want to apply the filter.
- Then, go to the
**Data**tab. - Next, select
**Filter**.

- In the following picture, you can see that filter is added to this dataset.

- Next,
**click**on the filter button. - Then,
**uncheck**the blank option. - After that, select
**OK**.

- Finally, you can see that I have got my desired output.

**Read More: **Combine CONCATENATE & TRANSPOSE Functions in Excel

### 4. Combine CONCATENATE & IF Functions to Join When Values Are Matched

For this example, I have taken the following dataset. Here, you can see that the dataset contains 3 columns, First Name, Middle Name, and Last Name. The **Middle Name** column contains some **blank cells**. Now, I will match the blanks and then concatenate the values accordingly if the match is found in Excel. Let’s see how it is done.

**Steps:**

- Firstly, select the cell where you want to concatenate the values.
- Secondly, write the following formula in that selected cell.

`=CONCATENATE(B5," ",IF(ISBLANK(C5),"",C5&" "),D5)`

- Thirdly, press
**Enter**.

**🔎** **How Does the Formula Work?**

**ISBLANK(C5):**Here, the**ISBLANK function**will return**True**if cell**C5**is blank. Otherwise, it will return**False**.**IF(ISBLANK(C5),””,C5&” “):**Now, the**IF function**checks for matches. And, if the**logical_test**is**True**then the function returns blank. Otherwise, it returns the value in cell**C5**with a**space**after that.**CONCATENATE(B5,” “,IF(ISBLANK(C5),””,C5&” “),D5):**Finally, the**CONCATENATE function**will join the texts.

- After that, drag the
**Fill Handle**down to copy the formula to the other cells.

- Lastly, you can see that I have copied the formula to the other cells and my desired output.

### 5. Use COUNTA Function to Concatenate

The** COUNTA function** counts cells containing any kind of information. You can use this function to find the blank cells. Here, I will use the **COUNTA function** and the **IF function** to concatenate if cell values match in Excel. I will find a match for the blanks. Let’s see the steps.

**Steps:**

- To begin with, select the cell where you want the result. Here, I selected cell
**F5**. - Next, in cell
**F5**write the following formula.

`=IF(COUNTA(C5)=0,B5&" "&D5,B5&" "&C5&" "&D5)`

- Then, press
**Enter**to get the result.

**🔎** **How Does the Formula Work?**

**COUNTA(C5):**Here, the**COUNTA function**returns the number of cells containing any values.**IF(COUNTA(C5)=0,B5&” “&D5,B5&” “&C5&” “&D5):**Now, the**IF function**will check 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**.

- Afterward, drag the
**Fill Handle**down to copy the formula.

- In the end, you can see that I have copied the formula to all the other cells.

### 6. Apply VBA to Add Cell Values

Here, I will explain how you can apply VBA to concatenate cell values if they match in Excel. I will return the results with the column header. Let’s see how you can do it.

**Steps:**

- Firstly, go to the
**Developer**tab. - Secondly, select
**Visual Basic**.

- Here, the
**Visual Basic Editor**window will open. - Select
**Insert**tab. - Then, select
**Module**.

- Now, a
**module**will open. - Write 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?**

- Here, I created a
**Sub Procedure**named**Concatenate_If_Match**. - Then, I declared the
**variables**. - After that, I used a
**Set Statement**to set where I want the output. - Next, I used a
**For Next Loop**to go through the input table. - Further, I used the
**ReDim Statement**to size the declared array. - Then, I used another
**For Next Loop**to go through the columns. - After that, I used an
**IF Statement**to check for a match. - Finally, I ended the
**Sub Procedure**.

- Now,
**Save**the code and go back to your worksheet. - Go to the
**Developer**tab. - Then, select
**Macros**.

- After that, the
**Macro**dialog box will appear. - Select
**Concatenate_If_Match**as**Macro Name**. - Then, select
**Run**.

- Finally, you will see that you have got your desired output.

### 7. Combine Values with User Defined Function

**User defined function** is a function defined by the user for a specific task. You can make a **user defined function **for yourself by simply writing a VBA code. Here, I will create a **User defined function **to concatenate if cell values match in Excel. Let’s see the steps.

**Steps:**

- First, go to the
**Developer**tab. - Then, select
**Visual Basic**.

- After that, the
**Visual Basic Editor**window will open. - Go to the
**Insert**tab. - Then, select the
**Module**.

- Now, a
**module**will open. - Next, write 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?**

- Here, I created a
**Function**named**CONCATENATE_IF**. - Then, I declared the
**variables**for the function. - After that, I used an
**If Statement**to check for a match. - Next, I used the
**CVErr**function to return a user defined error. - Then, I used a
**For Next Loop**to go through the whole range. - After that, I used another
**If Statement**to find a match and then return results accordingly. - Lastly, I ended the
**Sub Procedure**.

- Next,
**Save**the code and go back to your worksheet. - Select the cell where you want to
**concatenate if the values match**. Here, I selected cell**F5**. - Then, in cell
**F5**write the following formula.

`=CONCATENATE_IF($B$5:$B$14,E5,$C$5:$C$14,",")`

- After that, press
**Enter**and you will get the result.

**CONCATENATE_IF**function, I 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**.

- Further, drag the
**Fill Handle**down to copy the formula.

- Finally, you can see that I have copied the formula and got my desired output.

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

## Practice Section

Here, I have provided a practice sheet for you to practice how to **concatenate if cell values match** in Excel.

**Download Practice Workbook**

You can download the practice workbook from here.

## Conclusion

Finally, you have reached the end of my article. Here, I tried to explain how to concatenate if cell values match in Excel in 7 different ways. I hope this article was helpful to you. Lastly, if you have any questions, feel free to let me know in the comment section below.

## Related Articles

- How to Concatenate Arrays in Excel
- Excel INDEX MATCH to Concatenate Multiple Results
- How to Concatenate with VLOOKUP in Excel
- How to Concatenate Email Addresses in Excel
- How to Concatenate Decimal Places in Excel
- How to Add Parentheses with CONCATENATE Function in Excel
- How to Concatenate Different Fonts in Excel