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

We have a dataset with salesperson Names and their selling Products. Our goal is to find the selling products of each salesperson.

Method 1 – Using Formulas to Vlookup Multiple Values in One Cell in Excel

The TEXTJOIN function will be used for this method. The TEXTJOIN function allows you to join 2 or more strings together with each value separated by a delimiter.

The Basic Syntax of TEXTJOIN Function:

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

1.1 The TEXTJOIN and IF Functions

The Basic Syntax:

Steps

1. Enter the following formula in Cell F5:

2. Press Enter.

3. Drag the Fill Handle icon to fill in the remaining cells.

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

1.2 The TEXTJOIN and MATCH Functions (Without Duplicates)

STEPS

1. Enter the following formula in cell F5:

2. Press Enter.

3. Drag the Fill Handle icon to fill in the remaining cells.

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

1.3 The TEXTJOIN and UNIQUE Functions (Without Duplicates)

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. Enter the following formula in cell F5:

2.Press Enter.

3. Drag the Fill Handle icon to fill in the remaining cells.

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

Method 2 – Using VBA Codes to Vlookup Multiple Values in One Cell

2.1 VBA Codes Multiple Values in One Cell

STEPS

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

2. Click on Insert > Module.

3. Enter 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. Go to your worksheet and enter the following formula in Cell F5:

5. Press ENTER.

6. Drag the Fill Handle icon to fill in the remaining cells.

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. Press Alt+F11 to open Visual Basic Editor.

2. Click on Insert > Module.

3. Enter 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. The Microsoft Visual Basic for Applications window will open. Click Tools > References. From the References – VBAProject dialog box, check the Microsoft Scripting Runtime option in the Available References list box. Click OK.

5. Go to the worksheet and enter the following formula in Cell F5:

2 is the column number of the dataset.

6. Press Enter.

7. Drag the Fill Handle icon to fill in the remaining cells.

Download Practice Workbook

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