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

Get FREE Advanced Excel Exercises with Solutions!

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.
`=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

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.
`=TEXTJOIN(",",TRUE,IF(B:B=E5,C:C,""))` • You can apply the following formula instead to filter out the duplicate values.
`=TEXTJOIN(",",TRUE,UNIQUE(IF(B:B=E5,C:C,"")))`

### 2. Combine TEXTJOIN with FILTER Function

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

`=TEXTJOIN(",",TRUE,FILTER(C5:C13,B5:B13=E5))` ### 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.
`=VLOOKUPM(E5,B5:C13,2,",")` ## 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.

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

## What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems. Md. Shamim Reza

Hello there! This is Md. Shamim Reza. Working as an Excel & VBA Content Developer at ExcelDemy. We try to find simple & easy solutions to the problems that Excel users face every day. Our goal is to gather knowledge, find innovative solutions through them and make those solutions available for everybody. Stay with us & keep learning.

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  