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

This article illustrates how to look up vertically using VLOOKUP and return multiple values in one cell in Excel separated by a comma. Excel has several functions for vertical look up but you may not be able to return multiple values using them directly. However, you can create formulas by combining the functions to get the result as required. Here, we will show you 3 such formulas along with how to create a user-defined function exclusively for this purpose.

How to Apply VLOOKUP Function in Excel 2007 and Later Versions to Return Multiple Values in One Cell Separated by Comma

You can use the VLOOKUP function in Excel to get the desired result.

• Assume you need to look up the value in cell F5 within range B5:D12 and return multiple comma-separated values in cell G5.

• Then, enter the following formula in cell G5 to get the following result.

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

Here we will discuss the 3 possible alternative ways to look up and return multiple values in one cell in Excel separated by a comma.

1. Combine TEXTJOIN & IF Functions

You can also combine the TEXTJOIN & IF functions together to create a formula to get the desired result.

• Assume you have the following dataset instead. Now you need to look up a team and return all team members to a single cell.

• Then, enter the following formula in cell F5 to see the following result.

• You can apply the following formula instead to filter out the duplicate values.

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

2. Combine TEXTJOIN with FILTER Function

Alternatively, you can use the following formula to get the same result.

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

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

Follow the steps below to create a user-defined function (UDF) and get the same result as obtained earlier

📌 Steps:

• First, press ALT + F11 to open the VB Editor. Then select Insert >> Module to create a blank module as shown in the picture below.

• Next, 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

• Finally, apply the following formula in cell F5 to see the following result.

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

Things to Remember

• Don’t forget to put the 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.

Conclusion

Now you know how to Vlookup and return multiple values in one cell in Excel separated by a comma. Do you have any further queries or suggestions? Please let us know in the comment section below.

Related Articles

• How to VLOOKUP Multiple Values in One Cell in Excel
• Excel VLOOKUP Function to Return Min Value from Multiple Hits
• 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