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.

=VLOOKUP(F5,B5:E12,2,FALSE)&","&VLOOKUP(F5,B5:E12,3,FALSE)

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.

=TEXTJOIN(",",TRUE,IF(B:B=E5,C:C,""))

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

=TEXTJOIN(",",TRUE,UNIQUE(IF(B:B=E5,C:C,"")))

Method 2 – Combine TEXTJOIN with FILTER Function

Use this formula to get the same result.

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

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.

=VLOOKUPM(E5,B5:C13,2,",")

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.

Return Multiple Values in One Cell Separated by Comma.xlsm