**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:

`=TEXTJOIN(", ", TRUE, IF(C14=B5:B12, C5:C12, ""))`

**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:

`=CONCATIF(B5:B12,C14,C5:C12,", ")`

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

`=TEXTJOIN(", ", TRUE, IF(IFERROR(MATCH(C5:C15, IF(C17=B5:B15, C5:C15, ""), 0), "")=MATCH(ROW(B5:B15), ROW(B5:B15)), C5:C15, ""))`

** 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:

`=CONCATUNIQUE(C17,B5:C15,2)`

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

Thank you for this great collection working.

If it is with out duplication, could we have the occurrence number in front of ever string text, delimiter might : e.g. Brand xiamoi

Model Mi 10:2, Redmi 9 Pro:2, POCO F2:1

Hi Esayas,

You can count that if you mix the formulas with the COUNTIF function. You have to be a bit creative while using it in VBAs. For example, take the first method. You can use

=TEXTJOIN(“, “,TRUE,IF(C14=B5:B12,C5:C12&”:”&COUNTIF(C5:C12,IF(B5:B12=C14,C5:C12)),””))formula instead of the given one to find the count of each one in the dataset along with the models.Does anyone have a solution to a similar (albeit slightly more complex) scenario where there are potentially multiple entries (several countries as an example) captured within a single cell (but separated by a comma or other delimiter). Each of the entries (country in this example) requires a lookup on a separate table and each of the values returned (for each respective country in the lookup reference) needs to be concatenated into a single cell (preferably in the same order that they appeared within the referenced cell)?

Hope that makes some sense without showing the sample tables.

Dear

AdamThanks for your feedback. I have reviewed your requirements. It seems like you want to perform a lookup of

multiple entries(country names) ina single cell separated by a delimiter, such as commas. There will be alookup tableto keep the corresponding short names for each country. You are looking for a complex formula that takes multiple country names separated by commas, returnscorresponding values(country short names), and displays them ina single cell.Don’t worry! I have demonstrated your situation and developed a complex formula to fulfil your goal. I used the

TEXTJOIN,INDEX,MATCH,TRIM, andTEXTSPLITfunctions to build the formula.SOLUTION Overview:Suppose you want to enter the comma-separated country names in cell

E1. In cellE2, you want to display country short names. To do so,I hope you have found the formula you were looking for. I have attached the solution workbook as well; good luck.

DOWNLOAD SOLUTION WORKBOOKRegards

Lutfor Rahman ShimantoExcelDemy