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.

dataset to vlookup

  • Enter the following formula in cell G5.

VLOOKUP function to return multiple values

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.

return comma separated values

  • Enter the following formula in cell F5.

Vlookup with TEXTJOIN & IF Functions to Return Multiple Values in One Cell Separated by Comma

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


Vlookup with TEXTJOIN & FILTER Functions to Return Multiple Values in One Cell Separated by Comma

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


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

insert new module

  • 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
return_value = return_value & "" & table_array.Cells(I, col_index_num) & Char
End If
End If
VLOOKUPM = Left(return_value, Len(return_value) - 1)
End Function

VBA code to Return Multiple Values in One Cell in Excel

  • Apply the following formula in cell F5.

user-defined function

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

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

Get FREE Advanced Excel Exercises with Solutions!
Md. Shamim Reza
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

Leave a reply

Advanced Excel Exercises with Solutions PDF