In this lesson, I’ll demonstrate 3 effective approaches to VLOOKUP Multiple Values in One Cell in Excel. Moreover, these techniques will enable you to solve the problem that led you here. However, you will master several crucial Excel features and functions during this article, which will come in extremely handy for any assignment using Excel.

## 📁 Download Excel File

The sample worksheet that was used during the discussion is available to download for free right here.

## Learn to Vlookup Multiple Values in One Cell in Excel with These 3 Methods

Here in this article, we will learn how to **VLOOKUP** Multiple Values in One Cell in Excel using different approaches. To be exact, I’ve provided a total of 3 methods down below.

**Method 1**

### 1. Combination of TEXTJOIN Function

This approach uses the combination of different functions along with the **TEXTJOIN function** as common and a core part to VLOOKUP multiple values in one cell. The **TEXTJOIN **function can be described below.

**TEXTJOIN(Character_between_value, Ignore_empty_or_not, Text1, [Text2], …)**

**Character_between_value: **This part denotes which character will be there between the joined texts or values. It is described with a quotation mark like **“,”**

**Ignore_empty_or_not: TRUE **denotes **ignoring** empty cells and** FALSE **denotes **including** empty cells

**Text1: **Values or texts you want to join

**Text2: **Values or texts you want to join

**Combination of TEXTJOIN**

#### 1.1 With Duplicate Results

Here we will VLOOKUP multiple values in one cell using a combination of **TEXTJOIN** and **IF** functions. For instance, we have a dataset of salesman names, their products, and units sold at different times. We are going to find which products any specific salesman sold.

⬇️⬇️ **STEPS **⬇️⬇️

- Firstly, copy down the following formula.

**=TEXTJOIN(“,”,TRUE,IF($B$6:$B$14=F6,$C$6:$C$14,””))**

🔨** Formula Breakdown**

👉 **IF($B$6:$B$14=F6,$C$6:$C$14,””)**

The **IF** function here describes if the data in cell **F6** is equal to any of the data of the array **$B$6:$B$14 **then return from **$C$6:$C$14 **else return nothing which is denoted by** “”**.

👉 **TEXTJOIN(“,”,TRUE,IF($B$6:$B$14=F6,$C$6:$C$14,””))**

The **TEXTJOIN** function here provides all the results putting a comma within each one.

👉 **Output- **.**Mobile, Laptop, Laptop, AC**.

- Press
**Enter**to apply the formula. As we can see here we have got duplicate results because the formula assigns these texts as different values.

**Copy down the formula**for other results.

**Combination of TEXTJOIN**

#### 1.2 Without Duplicate Results

Here we will VLOOKUP multiple values in one cell using a combination of **TEXTJOIN**, **IFERROR**, **MATCH**,** ROW**, and **IF** functions. For instance, we have a dataset of salesman names, their products, and units sold at different times. We are going to find which products any specific salesman sold without the duplicates.

⬇️⬇️ **STEPS **⬇️⬇️

- Firstly, copy down the following formula.

**=TEXTJOIN(“,”, TRUE, IF(IFERROR(MATCH($C$6:$C$14, IF(F6=$B$6:$B$14, $C$6:$C$14, “”), 0),””)=MATCH(ROW($C$6:$C$14), ROW($C$6:$C$14)), $C$6:$C$14, “”))**

🔨** Formula Breakdown**

👉 **ROW($C$6:$C$14)**

The** ROW **function provides the row number of the corresponding data.

👉 ** IF(F6=$B$6:$B$14, $C$6:$C$14, “”)**

The** IF** function here describes if the data in cell **F6** is equal to any of the data of the array **$B$6:$B$14 **then return from **$C$6:$C$14 **else return nothing which is denoted by** “”**.

👉 **MATCH($C$6:$C$14, IF(F6=$B$6:$B$14, $C$6:$C$14, “”), 0)**

The **MATCH** function here provides the position of the items **$C$6:$C$14 **in the range **IF(F6=$B$6:$B$14, $C$6:$C$14, “”)**.

👉 **IFERROR(MATCH($C$6:$C$14, IF(F6=$B$6:$B$14, $C$6:$C$14, “”), 0),””)**

The **IFERROR **function finds if the value is an error or not.

👉 **MATCH(ROW($C$6:$C$14), ROW($C$6:$C$14))**

The **MATCH** function here provides the position of the item **ROW($C$6:$C$14)** in the range **ROW($C$6:$C$14)**

👉 **TEXTJOIN(“,”, TRUE, IF(IFERROR(MATCH($C$6:$C$14, IF(F6=$B$6:$B$14, $C$6:$C$14, “”), 0),””)=MATCH(ROW($C$6:$C$14), ROW($C$6:$C$14)), $C$6:$C$14, “”)**)

The total formula finds all the value we want to look for, merging the duplicates.

👉 **Output- **.**Mobile, Laptop, AC**.

- Press
**Enter**to get the result.

**Copy down the formula**along the column for other results.

**Method 2**

### 2. Utilizing UNIQUE Function

In this section, we will be utilizing **TEXTJOIN** and **UNIQUE** functions to create a combination formula to VLOOKUP multiple values in one cell.

⬇️⬇️ **STEPS **⬇️⬇️

- Firstly, copy the following formula

**=TEXTJOIN(“, “,TRUE,UNIQUE(IF(F6=$B$6:$B$14,$C$6:$C$14,””)))**

🔨** Formula Breakdown**

👉 **IF(F6=$B$6:$B$14,$C$6:$C$14,””)**

The **IF** function here describes if the data in cell **F6** is equal to any of the data of the array **$B$6:$B$14 **then return from **$C$6:$C$14 **else return nothing which is denoted by** “”**.

👉 **UNIQUE(IF(F6=$B$6:$B$14,$C$6:$C$14,””)**

The **UNIQUE **function here merges the duplicate values into one.

👉 **TEXTJOIN(“, “,TRUE,UNIQUE(IF(F6=$B$6:$B$14,$C$6:$C$14,””)))**

The **TEXTJOIN** function here provides all the results putting a comma within each one.

👉 **Output- **.**Mobile, Laptop, Laptop, AC**.

- Press
**Enter**to get the answer. As you can see here we got the results without the duplicates.

**Copy down the formula**along the column for all the results.

**Method 3**

### 3. VBA User-Defined Function

**VBA **is a special feature of Microsoft. It can be used as an internal programming language for Excel by which we can command Excel to do different types of operations with codes. Here we will demonstrate the solution to the above-stated problem with **VBA code**.

**VBA**

#### 3.1 VBA User-Defined Function with Duplicate

In this section, we will be using **VBA code** to VLOOKUP multiple values in one cell with duplicate results which means, the same texts will appear as different values.

⬇️⬇️ **STEPS **⬇️⬇️

- Firstly, you have to
**enable the Developer tab**of the ribbon. After that go to the**Visual Basic**option from the**Developer Tab**of the ribbon.

- A new window will pop-up and select
**Module**from the**Insert**.

- Now type in the following code and save it.

```
Function ValueswithRep(input_range As Range, criteria_range As Variant, output_range As Range, Optional Separator As String = ",") As Variant
Dim OutString As String
On Error Resume Next
If input_range.Count <> output_range.Count Then
ValueswithRep = CVErr(xlErrRef)
Exit Function
End If
For i = 1 To input_range.Count
If input_range.Cells(i).Value = criteria_range Then
OutString = OutString & Separator & output_range.Cells(i).Value
End If
Next i
If OutString <> "" Then
OutString = VBA.Mid(OutString, VBA.Len(Separator) + 1)
End If
ValueswithRep = OutString
Exit Function
End Function
```

- Now as we have defined a new function let’s use this. Copy the following formula.

**=ValueswithRep($B$6:$B$14,F6,$C$6:$C$14,”, “)**

- Press
**Enter**to get the results.

- As you can see we got duplicate results also.
**Copy down the formula**along the column for the other results.

**VBA**

#### 3.2 VBA User-Defined Function Without Duplicate

In this section, we will be using **VBA code** to VLOOKUP multiple values in one cell without duplicate results which means, the same texts will not appear.

⬇️⬇️ **STEPS **⬇️⬇️

- Type in the following code for this condition in the
**Visual Basic**window after**inserting**a new**Module**and save it.

```
Function ValueswithoutRep(target_range As String, input_range As Range, CNumber As Integer)
Dim ix1 As Long
Dim OutputString As String
For ix1 = 1 To input_range.Columns(1).Cells.Count
If input_range.Cells(ix1, 1) = target_range Then
For Jx1 = 1 To ix1 - 1
If input_range.Cells(Jx1, 1) = target_range Then
If input_range.Cells(Jx1, CNumber) = input_range.Cells(ix1, CNumber) Then
GoTo Skip
End If
End If
Next Jx1
OutputString = OutputString & " " & input_range.Cells(ix1, CNumber) & ","
Skip:
End If
Next ix1
ValueswithoutRep = Left(OutputString, Len(OutputString) - 1)
End Function
```

- Now as we have defined another function, let’s use this to solve our problem. Copy the following formula.

**=ValueswithoutRep(F6,$B$6:$D$14,2)**

- After that Press
**Enter**to get the results.

- As you can see the duplicate texts are merged there is only one
**Laptop**here in the results.**Copy down the formula**along the column for the total result.

## 📄 Important Notes

`🖊️`

Be careful while using the code as there are a lot of terms and if you lose concentration the code may be incorrect.

`🖊️`

Use shortcuts to reduce time, use **F4** for absolute value.

`🖊️`

While writing formulas carefully see the suggestions and don’t forget the commas and parentheses.

`🖊️`

Be careful while defining criteria to be accurate as the dataset.

`🖊️`

If you download a macro-enabled Excel file don’t forget to unblock the file from the properties menu.

## 📝 Takeaways from This Article

`📌`

Use of **IFERROR**, **ROW, MATCH, IF, TEXTJOIN,** and **UNIQUE **functions.

`📌`

How to VLOOKUP multiple values in one cell both with or without duplicate results.

`📌`

Creating formulas using multiple functions.

`📌`

Using VBA codes to customize functions.

`📌`

How to define a new function.

**Conclusion**

Firstly, I hope you were able to use the techniques I demonstrated in this VLOOKUP Multiple Values in One Cell In Excel lesson. As you can see, there are a lot of options on how to do this. However, decide deliberately on the approach that best addresses your circumstance. Most importantly I advise repeating the steps if you become confused in any of the steps if you get stuck. Practice on your own after taking a look at the Excel file in the practice workbook as I’ve provided above because practice makes a man perfect. Above all, I earnestly hope you can use it. Please share your experience with us in the comment box about how your experience was throughout the article and what we need to improve or add to make things better for you folks. However, if you have any problem regarding Excel spit it in the comment box. The Excelden crew is always available to answer your questions. In conclusion, keep educating yourself by reading. For more Excel-related articles please visit our website **Excelden.com****.**