Using Excel VLOOKUP to Return Multiple Values in One Cell Separated by Comma

In this example we need to look up the value in cell F5 within the range B5:D12 and return multiple comma-separated values in cell G5.

• Enter the following formula in cell G5.

Excel Vertical Look up and Returning Multiple Values in One Cell Separated by Comma: 3 Alternative Ways

Method 1 – Combine TEXTJOIN & IF Functions

• In this example we need to look up a team and return all team members to a single cell.

• Enter the following formula in cell F5.

• The following formula can be used to filter out the duplicate values.

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

Method 2 – Combine TEXTJOIN with FILTER Function

Use this formula to get the same result.

Read More: Find Max of Multiple Values by Using VLOOKUP Function in Excel

Method 3 – Create a User-Defined Function Using VBA to Return Multiple Values and Separate Them with Comma in One Cell

Steps:

• Press ALT + F11 to open the VB Editor.
• Select Insert >> Module to create a blank module as shown in the picture below.

• Copy the following code and paste it onto the blank code module.

Function VLOOKUPM(lookup_value As String, table_array As Range, col_index_num As Integer, Char As String)
Dim I As Long
Dim return_value As String
For I = 1 To table_array.Columns(1).Cells.Count
If table_array.Cells(I, 1) = lookup_value Then
If return_value = "" Then
return_value = table_array.Cells(I, col_index_num) & Char
Else
return_value = return_value & "" & table_array.Cells(I, col_index_num) & Char
End If
End If
Next
VLOOKUPM = Left(return_value, Len(return_value) - 1)
End Function

• Apply the following formula in cell F5.

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

Things to Remember

• Don’t forget to use double quotes in the formulas.
• You may need to enter the array formulas using CTRL+SHIFT+ENTER.
• The FILTER, TEXTJOIN, and UNIQUE functions are only available in newer Excel versions.

Download Practice Workbook

You can download the practice workbook from the download button below.

Related Articles

• How to VLOOKUP Multiple Values in One Cell in Excel
• VLOOKUP to Return Multiple Values Horizontally 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