Concatenate Multiple Cells Based on Criteria in Excel (4 Methods)

Sometimes it becomes necessary in excel to concatenate multiple cells based on criteria. This article shows how to do that in 4 different ways. The methods include formulas and excel functions. The following picture gives an idea about the purpose of this article.

concatenate multiple cells in excel based on criteria


Download Practice Workbook

You can download the practice workbook from the download button below.


4 Ways to Concatenate Multiple Cells Based on Criteria in Excel

Here, I am going to illustrate the 4 methods in excel to concatenate multiple cells based on criteria. So, let’s begin!


1. Combine Multiple Cells Based on Criteria with Formula & Filter

Assume, you have the following dataset containing colors with codes. You want to concatenate the Color cells based on their codes.

Then, follow the steps below.

📌Steps

  • At first, enter the following formula in cell D5:
=IF(B6<>B5,C6,D5&","&C6)
  • After that, use the fill handle tool to apply this formula to the cells below.

formula to concatenate multiple cells in excel based on criteria

  • Next, apply the following formula in cell E5:
=IF(B6<>B7,CONCAT(B6,",""",D6,""""),"")

  • Then, select cell E4. After that, select Sort & Filter >> Filter from the Data tab as shown in the following picture.

  • After that, the Filter column will look as follows.

  • Next, click on the drop-down arrow in cell E4. Uncheck the (Blanks) after that. Then, hit the OK.

  • Finally, the Color cells will be concatenated as follows.

Formula in excel to concatenate multiple cells based on criteria

Read More: How to Concatenate Multiple Cells With Space in Excel (7 Methods)


2. Concatenate Multiple Cells Based on Criteria with CONCAT Function

Alternatively, you can use the CONCAT function in excel to concatenate multiple cells based on criteria. Follow the steps below to be able to do that.

📌Steps

  • First, let’s modify the earlier dataset as follows to make the method more easily understandable.

  • Then, enter the following formula in cell F5:
=CONCAT(IF(C5:C16=E5,B5:B16,""))

CONCAT & IF Functions to concatenate multiple cells in excel based on criteria

  • Now, enter a color name in cell E5. Let’s say, Yellow, for example.
  • Then, the result will look as follows.

CONCAT Function in excel to concatenate multiple cells based on criteria

  • Now, change the criteria Color name as follows and see how the result changes too.

Read More: How to Concatenate in Excel (3 Suitable Ways)


Similar Readings:


3. Join Multiple Cells Based on Criteria with TEXTJOIN Function

You can also use the TEXTJOIN function in excel to concatenate multiple cells based on criteria. Follow the steps below to learn how to do that.

📌Steps

  • First, imagine having a dataset as follows containing students’ marks.

  • Now, suppose you want the students’ IDs who got more marks than your criteria concatenated in cell F5.
  • Then enter the criteria in cell E5. Let’s say, you want to find out who got more than 82 marks among them.

  • After that, apply the following formula in cell F5.
=TEXTJOIN(",",TRUE,IF($C$5:$C$16>E5,$B$5:$B$16,""))
  • You will see the IDs concatenated as follows.

The TEXTJOIN Function in excel to concatenate multiple cells based on criteria

  • You can change the criteria value as well as the criteria in the formula.
  • Suppose you want to find out who got less than or equal to 80 marks. Then, change the criteria value to 80 and the formula to the following one.
=TEXTJOIN(",",TRUE,IF($C$5:$C$16<=E5,$B$5:$B$16,""))

Read More: How to Bold Text in Concatenate Formula in Excel (2 Methods)


4. Concatenate Multiple Cells Based on Criteria with UDF

You can also use a User Defined Function (UDF) in excel to concatenate multiple cells based on criteria. Follow the steps below to see how to do that.

📌Steps

  • First, consider the following dataset similar to the earlier one.

  • Then, enter the following formula in cell E5.
=UNIQUE(C5:C16,FALSE,FALSE)
  • This gives the list of unique marks obtained by the students.

  • After that, press ALT+F11 to open the Microsoft Visual Basic for Applications. You will also find it in the Developer tab. Then select Insert>>Module.

  • Next, copy the following code and paste it on the blank module.
Function CONCATIF(Criteria As Range, Concatcriteria As Variant, ConcatRange As Range, Optional Delimiter As String = ",") As Variant
Dim Results As String
On Error Resume Next
If Criteria.Count <> ConcatRange.Count Then
    CONCATIF = CVErr(xlErrRef)
    Exit Function
End If
For j = 1 To Criteria.Count
    If Criteria.Cells(j).Value = Concatcriteria Then
        Results = Results & Delimiter & ConcatRange.Cells(j).Value
    End If
Next j
If Results <> "" Then
    Results = VBA.Mid(Results, VBA.Len(Delimiter) + 1)
End If
CONCATIF = Results
Exit Function
End Function

  • Then, select Close and Return to Microsoft Excel from the File tab.

  • After that, apply the following formula in cell F5 and drag it to the cells below.
=CONCATIF($C$5:$C$16,E5,$B$5:$B$16,",")
  • Finally, you will see the result as follows.

User Defined Function in excel to concatenate multiple cells based on criteria

Read More: Macro to Concatenate Multiple Columns in Excel (with UDF and UserForm)


Things to Remember

  • The CONCATENATE function is an earlier version to the CONCAT function. But, both functions give the same result.
  • The TEXTJOIN function may only be available in the newer versions of Microsoft Excel.

Conclusion

Now, you know the 4 different methods in excel to concatenate multiple cells based on criteria. If you have any further questions or suggestions, please share the comment section below. You may also visit our Exceldemy blog to learn more about excel and improve your performance.


Related Articles

Md. Shamim Reza

Md. Shamim Reza

Hello there! This is Md. Shamim Reza. Working as an Excel & VBA Content Developer at ExcelDemy. We try to find simple & easy solutions to the problems that Excel users face every day. Our goal is to gather knowledge, find innovative solutions through them and make those solutions available for everybody. Stay with us & keep learning.

6 Comments
  1. Hi Md
    Can this concatenate multiple cells based on criteria be done in power query? I am using a custom function in excel as per the 4th solution however I have 10’s of thousands of rows and it takes so long
    Thanks GT

    • Hello Grant!
      Yes, you can do that. But if you gave a little more description of your dataset, it would’ve been easier for me to help you. Anyway, you can apply the following steps in Power Query for the dataset used in the 4th solution in the article.

      Source = Excel.CurrentWorkbook(){[Name=”Table1″]}[Content]

      Changed Type = Table.TransformColumnTypes(Source,{{“Roll”, Int64.Type}, {“Marks”, Int64.Type}})

      Reordered Columns = Table.ReorderColumns(#”Changed Type”,{“Marks”, “Roll”})

      Sorted Rows = Table.Sort(#”Reordered Columns”,{{“Marks”, Order.Ascending}})

      Transposed Table = Table.Transpose(#”Sorted Rows”)

      Merged Columns = Table.CombineColumns(Table.TransformColumnTypes(#”Transposed Table”, {{“Column1”, type text}, {“Column2”, type text}, {“Column3”, type text}, {“Column4”, type text}}, “en-US”),{“Column1”, “Column2”, “Column3”, “Column4”},Combiner.CombineTextByDelimiter(“,”, QuoteStyle.None),”Merged”)

      Merged Columns1 = Table.CombineColumns(Table.TransformColumnTypes(#”Merged Columns”, {{“Column5”, type text}, {“Column6”, type text}, {“Column7”, type text}}, “en-US”),{“Column5”, “Column6”, “Column7”},Combiner.CombineTextByDelimiter(“,”, QuoteStyle.None),”Merged.1″)

      Merged Columns2 = Table.CombineColumns(Table.TransformColumnTypes(#”Merged Columns1″, {{“Column8”, type text}, {“Column9”, type text}, {“Column10”, type text}}, “en-US”),{“Column8”, “Column9”, “Column10”},Combiner.CombineTextByDelimiter(“,”, QuoteStyle.None),”Merged.2″)

      Merged Columns3 = Table.CombineColumns(Table.TransformColumnTypes(#”Merged Columns2″, {{“Column11”, type text}, {“Column12”, type text}}, “en-US”),{“Column11”, “Column12”},Combiner.CombineTextByDelimiter(“,”, QuoteStyle.None),”Merged.3″)
      Transposed Table1 = Table.Transpose(#”Merged Columns3″)

      Split Column by Delimiter = Table.SplitColumn(#”Transposed Table1″, “Column1”, Splitter.SplitTextByDelimiter(“,”, QuoteStyle.Csv), {“Column1.1”, “Column1.2”, “Column1.3”, “Column1.4″})

      Changed Type1 = Table.TransformColumnTypes(#”Split Column by Delimiter”,{{“Column1.1”, Int64.Type}, {“Column1.2”, Int64.Type}, {“Column1.3”, Int64.Type}, {“Column1.4″, Int64.Type}})

      Removed Columns = Table.RemoveColumns(#”Changed Type1”,{“Column1.2”, “Column1.3”, “Column1.4″})

      Renamed Columns = Table.RenameColumns(#”Removed Columns”,{{“Column1.1”, “Marks”}, {“Column2”, “Roll”}})

  2. Hello –

    Thank you for this article. It is really helpful for what I am trying to do. I do have a question:

    Is it possible to use a range criteria to get multiple values in a single cell?

    For example I have the follwoing table:

    ROUTE MILE #OFLANES
    10 15 2
    10 20 2
    10 25 3
    10 30 3
    10 35 1

    The criteria is to inpute a range of miles and to extract the number of lanes found within that range.

    If I chose miles 20-30, I would like to see “2,3,3” in a single cell. If I chose miles 25-35, I would like to see “3,3,1” in a single cell.

    I am thinking the TEXTJOIN example you gave would be the idea function but I am unsure how to tell the IF function to look inside a range.
    Hope you can help.

    Thanks
    Joe

    • Hi Joe,

      Assume your example data table (with headers) starts from cell A1. The lower criteria range i.e 20 is in cell E2 and the upper criteria range i.e 30 in cell G2. Now enter the following formula in cell H2 to get the desired result.
      =TEXTJOIN(",",TRUE,IF($B$2:$B$6>=$E$2,IF($B$2:$B$6<=$G$2,$C$2:$C$6,""),""))

      You can change the criteria ranges as required. For example, change the lower criteria from 20 to 25 and the upper criteria from 30 to 35. This will give you the #OFLANES values for the range 25-35. You can create dropdown lists in the criteria cells E2 and G2 to easily change the criteria value.

      I’ve also emailed you an excel document with the solution. Please check.

      Don’t hesitate to let us know if you face any further problems. Thanks for reaching out to us.

      Regards,
      Md. Shamim Reza (ExcelDemy Team)

  3. Thank you very much! This is exactly what I was looking for.

Leave a reply

ExcelDemy
Logo