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