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.
Download Practice Workbook
You can download the practice workbook from the download button below.
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.
3 Alternative Ways of Vertical Look up and Returning Multiple Values in One Cell Separated by Comma in Excel
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
- 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.
2. Combine TEXTJOIN with FILTER Function
Alternatively, you can use the following formula to get the same result.
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
- 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.
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.
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. You can also visit our ExcelDemy blog to explore more about excel. Stay with us and keep learning.