How to VLOOKUP Multiple Values in One Cell in Excel (2 Easy Methods)

While working with large amounts of data in Excel, sometimes you may find yourself in a situation where you need to lookup for values that appears multiple times in your dataset. So, you have to see those data in one cell. In this tutorial, we will show you how to use VLOOKUP for multiple values in one cell in Excel.

Generally speaking, we are not directly using the VLOOKUP function here. We are going to find multiple values in one cell that will be similar to the VLOOKUP function. We hope you will find this tutorial helpful.


Download Practice Workbook


2 Easy Methods to Vlookup for Multiple Values in One Cell

Now, we are going to show you 2 ways to look up multiple values in one cell in Excel. The first one is using the Formulas and the second one is using the VBA codes. We will look up both repeated and non-repeated values in this article.  So, you can choose the best-suited method according to your problem.

To demonstrate this problem, we are going to use the following dataset:

dataset of vlookup multiple values in one cell

Here, we have some salespersons’ Names and their selling Products. Now, our goal is to find the selling products of each salesperson.

1. Using Formulas to Vlookup Multiple Values in One Cell in Excel

The TEXTJOIN function will be our go-to function to implement this method. The TEXTJOIN function allows you to join 2 or more strings together with each value separated by a delimiter. Mainly, we are combining different functions with the TEXTJOIN function to implement our formula.

The TEXTJOIN function is only available for Excel 2019 and Office 365.

The Basic Syntax of TEXTJOIN Function:

=TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)

Here, our delimiter will be a comma (“,”) to separate values in one cell.

1.1 The TEXTJOIN and IF Functions

Now, this formula is pretty easy to use. This formula will look up the values and also insert them into one cell with a delimiter, comma. But, remember this formula will return the value with duplicates.

The Basic Syntax:

=TEXTJOIN(", ",TRUE,IF(lookup_value=lookup_range,,finding_range,""))

📌 Steps

1. First, type the following formula in Cell F5:

=TEXTJOIN(", ",TRUE,IF(E5=B5:B13,C5:C13,""))

textjoin and if functions to vlookup multiple values in one cell

2. Then, press Enter.

result of vlookup multiple values in one cell

3. Finally, drag the Fill Handle icon over the range of cells F6:F7.

drag the fill handle to copy the formula

In the end, we are successful to use VLOOKUP multiple values in one cell.

🔎 Breakdown of the Formula

We are using this breakdown only for the person “John”

IF(E5=B5:B13,C5:C13,"")

This function returns the following array:

{"Mobile";"";"";"TV";"";"Fridge";"";"Mobile";""}

TEXTJOIN(", ",TRUE,IF(E5=B5:B13,C5:C13,""))

Finally, the TEXTJOIN function will return the following result:

{Mobile, TV, Fridge, Mobile}

Read More: Excel VLOOKUP to Return Multiple Values in One Cell Separated by Comma

1.2 The TEXTJOIN and MATCH Functions (Without Duplicates)

Now, if you want multiple values in one cell, you may use this formula. This formula is a combination of the TEXTJOIN and MATCH functions. This formula is a little complex to use but surely it will give your desired values.

📌 STEPS

1. First, type the following formula in Cell F5:

=TEXTJOIN(",", TRUE, IF(IFERROR(MATCH(C5:C13, IF(E5=B5:B13, C5:C13, ""), 0),"")=MATCH(ROW(C5:C13), ROW(C5:C13)), C5:C13, ""))

textjoin and match functions to vlookup multiple values in one cell

2. Then, press Enter.

result of textjoin and match functions

3. Finally, drag the Fill Handle icon over the range of cells F6:F7.

drag fill handle to copy values in one cell

In the end, we are successful to use VLOOKUP multiple values in one cell without any duplicate values.

🔎 Breakdown of the Formula

We are using this breakdown only for the person “John”

ROW(C5:C13)

It returns an array of {5;6;7;8;9;10;11;12;13}

MATCH(ROW(C5:C13), ROW(C5:C13))

It returns: {1;2;3;4;5;6;7;8;9}

IF(E5=B5:B13, C5:C13, "")

It returns: {"Mobile";"";"";"TV";"";"Fridge";"";"Mobile";""}

MATCH(C5:C13, IF(E5=B5:B13, C5:C13, "")

This function returns: {8;8;7;9;7;7;7;8;7}

IFERROR(MATCH(C5:C13, IF(E5=B5:B13, C5:C13, ""), 0),"")

   It returns: {1;1;"";4;"";6;"";1;""}

IF(IFERROR(MATCH(C5:C13, IF(E5=B5:B13, C5:C13, ""), 0),"")=MATCH(ROW(C5:C13), ROW(C5:C13)), C5:C13, "")

It returns: {"Mobile";"";"";"TV";"";"Fridge";"";"";""}

TEXTJOIN(",", TRUE, IF(IFERROR(MATCH(C5:C13, IF(E5=B5:B13, C5:C13, ""), 0),"")=MATCH(ROW(C5:C13), ROW(C5:C13)), C5:C13, ""))

The final output will be Mobile, TV, Fridge.

Read More: How to Perform VLOOKUP with Multiple Rows in Excel (5 Methods)

1.3 The TEXTJOIN and UNIQUE Functions (Without Duplicates)

Now, the UNIQUE function is only available in Excel 365. So, if you are using Excel 365 then you can definitely use this formula. The previous formula is a little bit difficult but this formula will ease the way of looking up values in one cell. The UNIQUE function returns a list of unique values in a list or range. Now, the difference between the first and the third formula is using the UNIQUE function before the IF function.

The Basic Syntax of UNIQUE Function:

=UNIQUE (array, [by_col], [exactly_once])

array – Range or array from which to extract unique values.

by_col – [optional] How to compare and extract. By row = FALSE (default); by column = TRUE.

exactly_once – [optional] TRUE = values that occur once, FALSE= all unique values (default)

📌 STEPS

1. First, type the following formula in Cell F5:

=TEXTJOIN(", ",TRUE,UNIQUE(IF(E5=B5:B13,C5:C13,"")))

textjoin and unique functions to lookup values

2. Then, press Enter.

textjoin and unique function result

3. Finally, drag the Fill Handle icon over the range of cells F6:F7.

drag the fill handle to copy the formula

As you can see, we have successfully used VLOOKUP multiple values in one cell.

🔎 Breakdown of the Formula

We are using this breakdown only for the person “John”

➤ IF(E5=B5:B13,C5:C13,"")

    It returns {"Mobile";"";"";"TV";"";"Fridge";"";"Mobile";""}

➤ UNIQUE(IF(E5=B5:B13,C5:C13,""))

It returns {"Mobile";"";"TV";"Fridge"}

TEXTJOIN(", ",TRUE,UNIQUE(IF(E5=B5:B13,C5:C13,"")))

   Final result Mobile,TV,Fridge

Read More: How to Vlookup and Return Multiple Values in Drop Down List

2. Using VBA Codes to Vlookup Multiple Values in One Cell

The TEXTJOIN function is only available for MS Excel 2019 and MS Excel 365. So, if you are well known about Excel’s VBA codes, these two codes will be very practical for you. The first one will be with duplicates and the second one will be without duplicates. So, choose your method according to your problem.

2.1 VBA Codes Multiple Values in One Cell

📌 STEPS

1. First. Press Alt+F11 to open Visual Basic Editor.

2. Then, click on Insert > Module.

insert module for VBA editor

3. Next, type the following code:

Function MultipleValues(work_range As Range, criteria As Variant, merge_range As Range, Optional Separator As String = ",") As Variant
Dim outcome As String
On Error Resume Next
If work_range.Count <> merge_range.Count Then
MultipleValues = CVErr(xlErrRef)
Exit Function
End If
For i = 1 To work_range.Count
If work_range.Cells(i).Value = criteria Then
outcome = outcome & Separator & merge_range.Cells(i).Value
End If
Next i
If outcome <> "" Then
outcome = VBA.Mid(outcome, VBA.Len(Separator) + 1)
End If
MultipleValues = outcome
Exit Function
End Function

4. Now, go to your worksheet. Then, type the following formula in Cell F5:

=MultipleValues(B5:B13,E5,C5:C13,",")

user defined function to vlookup multiple values in one cell

5. Then, press ENTER.

result of the vba codes

6. Finally, drag the Fill Handle icon over the range of cells F6:F7.

drag the fill handle to copy

In the end, we have used VLOOKUP multiple values in one cell.

Read More: VLOOKUP to Return Multiple Values Horizontally in Excel

2.2 VBA Codes to LOOKUP Multiple Values in One Cell (Without Duplicates)

📌 STEPS

1. First. Press Alt+F11 to open Visual Basic Editor.

2. Then, click on Insert > Module.

3. Next, type the following code:

Function ValuesNoDup(target As String, search_range As Range, ColumnNumber As Integer)
Dim i As Long
Dim outcome As String
For i = 1 To search_range.Columns(1).Cells.Count
If search_range.Cells(i, 1) = target Then
For J = 1 To i - 1
If search_range.Cells(J, 1) = target Then
If search_range.Cells(J, ColumnNumber) = search_range.Cells(i, ColumnNumber) Then
GoTo Skip
End If
End If
Next J
outcome = outcome & " " & search_range.Cells(i, ColumnNumber) & ","
Skip:
End If
Next i
ValuesNoDup = Left(outcome, Len(outcome) - 1)
End Function

4. After inserting the code, then click Tools > References in the opened Microsoft Visual Basic for Applications window, and then, in the popped out References – VBAProject dialog box, check the Microsoft Scripting Runtime option in the Available References list box. Click on OK.

select microsoft scripting runtime

5. Now, go to your worksheet. Then, type the following formula in Cell F5:

=ValuesNoDup(E5,B5:B13,2)

Here, 2 is the column number of the dataset.

vba codes to vlookup multiple values in one cell without duplicates

6. Then, press Enter.

result of the vba code

7. Finally, drag the Fill Handle icon over the range of cells F6:F7.

drag the fill handle to copy the formula

As you can see, we have used VLOOKUP multiple values in one cell without duplicates.

Read More: How to VLOOKUP and Return Multiple Values in Excel (8 Methods)


Conclusion

To conclude, I hope this tutorial will help you to vlookup multiple values in one cell in Excel. Download the practice workbook and try these yourself. Surely, it will develop your Excel knowledge. Also, feel free to give feedback in the comment section. Your valuable feedback keeps us motivated to create a lot of articles like these. Don’t forget to check our website ExcelDemy for Excel-related problems and solutions.


Related Articles

Shanto

Shanto

Hello! I am Shanto. An Excel & VBA Content Developer. My goal is to provide our readers with great tutorials on various Excel-related problems. I hope our easy but effective tutorials will enrich your knowledge. I have completed my BSc in Computer Science & Engineering from Daffodil International University. Working with data was always my passion. Love to work with data, analyze those, and find patterns. Also, love to research. Always look for challenges to keep me growing.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo