# 3 Ways to VLOOKUP Multiple Values in One Cell in Excel

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.

## 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.

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.

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.

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.

`📌`  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.