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.


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.

dataset to vlookup

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

VLOOKUP function to return multiple values


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

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.

return comma separated values

  • Then, enter the following formula in cell F5 to see the following result.
=TEXTJOIN(",",TRUE,IF(B:B=E5,C:C,""))

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

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

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

Read More: Excel VLOOKUP to Return Multiple Values Vertically


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.

insert new module

  • 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

VBA code to Return Multiple Values in One Cell in Excel

  • Finally, apply the following formula in cell F5 to see the following result.
=VLOOKUPM(E5,B5:C13,2,",")

user-defined function

Read More: How to Perform VLOOKUP with Multiple Rows in Excel (5 Methods)


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. You can also visit our ExcelDemy blog to explore more about excel. Stay with us and keep learning.


Related Articles

Md. Shamim Reza

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

Leave a reply

ExcelDemy
Logo