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.

How to Vlookup for Multiple Values in One Cell: 2 Easy Methods

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:

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 Basic Syntax of TEXTJOIN Function:

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:

📌 Steps

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

2. Then, press Enter.

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

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

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:

2. Then, press Enter.

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

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

Read More: How to Use VLOOKUP Function on Multiple Rows in Excel

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:

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:

2. Then, press Enter.

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

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

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.

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:

5. Then, press ENTER.

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

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.

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

Here, 2 is the column number of the dataset.

6. Then, press Enter.

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

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

Read More: Excel VLOOKUP Function to Return Min Value from Multiple Hits

Download Practice Workbook

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.

Related Articles

• Find Max of Multiple Values by Using VLOOKUP Function in Excel
• How to Use Excel VLOOKUP to Return Multiple Values Vertically

<< Go Back to VLOOKUP Multiple Values | Excel VLOOKUP Function | Excel Functions | Learn Excel