How to return multiple lookup values separated by commas in one cell?
When working with data in Excel, you may encounter a situation where you need to return multiple matching values for a lookup and display them in a single cell, separated by commas, dash, etc. While Excel's built-in lookup functions like "VLOOKUP" and "INDEX-MATCH" are great for finding a single match, combining multiple results into one cell requires a more advanced approach. This article will introduce some useful ways for solving this task in Excel.
Return multiple lookup values in a cell separated by commas
Return multiple lookup values in a cell separated by commas with TEXTJOIN and FILTER functions
If you have access to Excel 365 or Excel 2021 and later versions, using the dynamic array formula is the simplest method.
Please apply the below formula into a blank cell to output the result, and then press "Enter" key to get the result.
=TEXTJOIN(", ", TRUE, FILTER($B$2:$B$15, $A$2:$A$15=D2))Then, drag the fill handle down to fill the formula to other cells, see screenshot:
- FILTER(B2:B15, A2:A15=D2): Extracts all the values in column B where column A equals the cell D2.
- TEXTJOIN(", ", TRUE, ...): Joins the resulting values into a single string, separated by commas.
Return multiple lookup values in a cell separated by commas with Kutools for Excel
"Kutools for Excel" is an add-in designed to simplify complex tasks in Excel. Its advanced "One-to-Many Lookup" feature allows you to effortlessly extract all matching values into a single cell with a specified delimiter—no need for complicated formulas or VBA coding.
After installing Kutools for Excel, please do as this:
Click "Kutools" > "Super Lookup" > "One-to-many Lookup(returns multiple results)" to open the dialog box. In the dialog box, please specify the operations as below:
- Select the "Output range and Lookup values" in the textboxes separately;
- Select the table range that you want to use;
- Specify the key column and return column from the "Key Column" and "Return Column" drop down separately;
- Finally, click the "OK" button.
Result:
All the corresponding cells with the same value have been combined into one cell which are separated with the comma, see screenshot:
Return multiple lookup values in a cell separated by commas with User Defined Function
If you don't have Excel 365 or Excel 2021, this section will guide you step-by-step on how to create and use a UDF to return multiple lookup values in a single cell, separated by commas.
1. Hold down the "ALT" + "F11" keys to open the "Microsoft Visual Basic for Applications" window.
2. Click "Insert" > "Module", and paste the following code in the Module Window.
VBA code: Return multiple lookup values into one comma separated cell
Function JoinLookupValues(lookupValue As String, lookupRange As Range, returnRange As Range, delimiter As String) As String
'Updateby Extendoffice
Dim result As String
Dim i As Long
result = ""
For i = 1 To lookupRange.Rows.Count
If lookupRange.Cells(i, 1).Value = lookupValue Then
result = result & returnRange.Cells(i, 1).Value & delimiter
End If
Next i
If Len(result) > 0 Then
JoinLookupValues = Left(result, Len(result) - Len(delimiter))
Else
JoinLookupValues = ""
End If
End Function
3. Then save this code and close the Module window, go back to your worksheet, and enter the below formula into a blank cell that you want to return the result. Then, drag the fill handle down to fill this formula to other cells, see screenshot:
=JoinLookupValues(D2, $A$2:$A$15, $B$2:$B$15, ", ")
In conclusion, returning multiple lookup values in a single cell separated by commas can be achieved efficiently using various methods, depending on your Excel version and needs. By selecting the method that best fits your requirements, you can streamline your data analysis and improve productivity. If you're interested in exploring more Excel tips and tricks, our website offers thousands of tutorials.
Related Articles:
- Vlookup and return multiple values without duplicates
- When working with data in Excel, you may sometimes need to return multiple matching values for a specific lookup criterion. However, the default VLOOKUP function only retrieves a single value. In situations where multiple matches exist, and you want to display them in a single cell without duplicates, you can use alternative methods to achieve this.
- Return multiple matching values based on one or multiple criteria
- Normally, looking up a specific value and returning the matching item is easy for most of us by using the VLOOKUP function. But, have you ever tried to return multiple matching values based on one or more criteria as following screenshot shown? In this article, I will introduce some formulas for solving this complex task in Excel.
- Vlookup and return the last matching value
- If you have a list of items which are repeated many times, and now, you just want to know the last matching value with your specified data. How to do?
- Vlookup and return matching data between two values
- In Excel, we can apply the normal Vlookup function to get the corresponding value based on a given data. But, sometimes, we want to vlookup and return the matching value between two values as the following screenshot shown, how could you deal with this task in Excel?
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in