# 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.

## Related Articles

Get FREE Advanced Excel Exercises with Solutions!
Md. Shamim Reza

Md. Shamim Reza, a marine engineer with expertise in Excel and a fervent interest in VBA programming, sees programming as a time-saving tool for data manipulation, file handling, and internet interaction. His diverse skill set encompasses Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning. He holds a B.Sc in Naval Architecture & Marine Engineering from BUET and has transitioned into a content developer role, generating technical content focused on Excel and VBA. Beyond his professional pursuits,... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF