Lookup and Return Multiple Values Concatenated into One Cell in Excel

Method 1 – Using the TEXTJOIN Function to Lookup and Extract Multiple Values Joined into Single Cell

In the following dataset, we’ll extract the model names of a specified brand and then concatenate them into a single cell. We want to concatenate all model names of Xiaomi smartphones into one cell.

• The required formula in the output cell C15 will be:

Method 2 – VBA User-Defined Function to Lookup and Return Multiple Values Concatenated into One Cell

Step 1

• Press Alt + F11 to open the VBA window.
• Select the Module option from the Insert tab.
• In the new module window, copy and paste the following code:

Option Explicit

Function CONCATIF(LookupRange As Range, LookupVal As Variant, _

ConcatRange As Range, Optional Separator As String = ",") As Variant

Dim i As Integer

Dim Result As String

On Error Resume Next

If LookupRange.Count <> ConcatRange.Count Then

    CONCATIF = CVErr(xlErrRef)

    Exit Function

End If

For i = 1 To LookupRange.Count

    If LookupRange.Cells(i).Value = LookupVal Then

        Result = Result & Separator & ConcatRange.Cells(i).Value

    End If

Next i

If Result <> "" Then

    Result = VBA.Mid(Result, VBA.Len(Separator) + 1)

End If

CONCATIF = Result

Exit Function

End Function

Sub Lookup_and_Concat()

End Sub

• Press F5 to run the code.
• Return to the Excel spreadsheet by pressing Alt + F11 again.

We’ve just created a user-defined function and that is:

=CONCATIF(LookupRange, LookupVal, ConcatRange, [Separator])

Step 2

• Select the output cell C15 and insert:

• Press Enter.

You’ll find the concatenated values in a single cell as shown in the screenshot below.

Method 3 – Lookup and Return Multiple Unique Values Concatenated into One Cell

The smartphone model names have duplicates in Column C. We’ll extract and concatenate all smartphone models of a specified brand (in C17) into a single cell but display each model name only once if the duplicates are found.

• In the output cell C18, the required formula under the specified criteria will be:

How Does the Formula Work?

• IFERROR(MATCH(C5:C15, IF(C17=B5:B15, C5:C15, “”), 0), “”): This part of the formula checks if the defined condition in cell C17 matches with the values in the range of cells B5:B15 and returns the corresponding row numbers of the values. If duplication is found for a value, then the row number returns for the first value. Thus this part of the formula returns the following array:

{“”;2;””;4;””;””;7;2;””;4;””}

• IF(IFERROR(MATCH(C5:C15, IF(C17=B5:B15, C5:C15, “”), 0), “”)=MATCH(ROW(B5:B15), ROW(B5:B15)), C5:C15, “”): This part is the third argument (text1) of the TEXTJOIN function which defines the statements to be concatenated. The values from Column C will be extracted based on the row numbers found in the previous step. So, the return array here will be:

{“”;”Mi 10″;””;”Redmi 9 Pro”;””;””;”POCO F2″;””;””;””;””}

• The entire formula will concatenate the values obtained in the preceding step with the defined delimiter- Comma (,).

Method 4 – VBA to Lookup and Return Multiple Unique Values Concatenated into One Cell

Step 1

• Press Alt + F11 to open the VBA window.
• Activate a new module from the Insert tab and paste the following code in the new module window:

Option Explicit

Function CONCATUNIQUE(LookupValue As String, LookupTable As Range, Col_Num As Integer)

Dim i As Long

Dim j As Integer

Dim Result As String

For i = 1 To LookupTable.Columns(1).Cells.Count

  If LookupTable.Cells(i, 1) = LookupValue Then

    For j = 1 To i - 1

    If LookupTable.Cells(j, 1) = LookupValue Then

      If LookupTable.Cells(j, Col_Num) = LookupTable.Cells(i, Col_Num) Then

        GoTo Skip

      End If

    End If

    Next j

    Result = Result & " " & LookupTable.Cells(i, Col_Num) & ","

Skip:

  End If

Next i

CONCATUNIQUE = Left(Result, Len(Result) - 1)

End Function

Sub Concatenate_Unique()

End Sub

• Press F5.

The user-defined function we’ve just created is:

=CONCATUNIQUE(LookupValue, LookupTable, Col_Num)

Step 2

• Return to your Excel spreadsheet by pressing Alt + F11.
• In the output cell C18, put:

• Press Enter.

You’ll get the return values in a single cell as shown in the picture below.

Download the Practice Workbook

<< Go Back to Lookup | Formula List | Learn Excel