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