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.


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

vlookup multiple values in one cell Combination of TEXJOIN and IF function

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

vlookup multiple values in one cell Combination of TEXJOIN MATCH IFERROR IF and ROW functions

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

vlookup multiple values in one cell combination of TEXJOIN and UNIQUE function

  • 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

vlookup multiple values in one cell VBA code with repeating values

  • 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,”, “)

vlookup multiple values in one cell VBA custom function with repeating values

  • 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

vlookup multiple values in one cell VBA custom code without duplicate

  • 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)

vlookup multiple values in one cell VBA custom Function

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

(Visited 31 times, 1 visits today)
Nashid

Nashid

Hello there. I am Nashid, an Engineer. I Love music, traveling, sports and gaming. I have recently grown interest in DATA SCIENCE and wish to build a career over it. Excel is one of the greatest modern digital creation of human. Life simply gets easier. My Articles are meant to lead you to that easier life with efficiency. If you need any kind of help regarding my articles even anything else try contacting me on social platforms of which I've provided. Keep learning. Wish me luck and Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo