How to Use CONCATENATE Function in Excel (4 Easy Methods)

• CONCATENATE function cannot recognize an array, i.e. if you want to join values in cells A1, A2, A3, and A4, you cannot write the argument as (A1:A4), rather it must be (A1,A2,A3,A4). You have to separate the inputs with commas in the function.
• But you can separate arrays as arguments.

Formula Breakdown:

If we break the Array Formula, we will get 18 single formulas like this:

• CONCATENATE(B5,”, “,C5,” “,D5)
• CONCATENATE(B6,”, “,C6,” “,D6)
• CONCATENATE(B7,”, “,C7,” “,D7)

…
…
…

• CONCATENATE(B22,”, “,C22,” “,D22)

CONCATENATE(B5,”, “,C5,” “,D5) joins the number in cell B5, a comma with a space, the text in cell C5, another space, and the text in cell D5 together.

Formula Breakdown:

• CONCATENATE(C5:C22,D5:D22,E5:E22) joins all values of the arrays C5:C22, D5:D22 and E5:E22 into single text values.
  For the first student, it joins 78, 80, and 100 and returns “7880100”.
  For the student with 100 in all 3 subjects, it returns “100100100”.
• MATCH(“100100100”,CONCATENATE(C5:C22,D5:D22,E5:E22),0) returns the row number in the table where marks in all three subjects are 100.
• INDEX(B5:E22,MATCH(“100100100”,CONCATENATE(C5:C22,D5:D22,E5:E22),0),1) returns the value in the cell with row number equal to the output of the MATCH function and column number equal to 1 from the table array B5:E22.
  The student who got 100 in all three subjects is Usman Malik.

Note:

• It is an Array Formula, so you must press Ctrl + Shift + Enter to insert this formula.
• This method only works for cases where the criteria are equal to some values. If the criteria are greater than or less than some values, this method will not work. For example, you can not identify the student who got greater than 95 in all subjects in this way.
• If more than one value satisfies all the criteria, you will get only the first value in this process. To get all the values that satisfy all the criteria, use the FILTER function instead.