How to vlookup and concatenate multiple corresponding values in Excel?
When using VLOOKUP in Excel, the function will typically return only the first matching value it finds for a given lookup criterion. However, there are many common scenarios where you may need to retrieve and combine all matching values associated with a particular key, such as listing all students in a class or all products associated with a certain category. Since the standard VLOOKUP function is limited in this regard, you may wonder how to achieve the ability to both look up and concatenate multiple corresponding results into a single cell. Below, we’ll explore several practical and efficient methods to accomplish this task, suited for different Excel versions and user preferences.
Vlookup and concatenate multiple corresponding values in Excel
Vlookup and concatenate multiple corresponding values with TEXTJOIN and FILTER Functions
If you’re using Excel 365 or Excel 2021, the combination of TEXTJOIN and FILTER functions provides an efficient, formula-based approach to vlookup and concatenate all matching values. This solution is especially suitable for dynamic and updated datasets, as it will automatically refresh the result when the source data changes. It is best applied when your version of Excel supports the FILTER function, which is exclusive to recent Office versions.
In the target cell, enter the following formula, then drag the formula down if you want to apply it to other rows as well. All corresponding matched values are extracted and combined into one cell. See screenshot:
=TEXTJOIN(", ", TRUE, FILTER($B$2:$B$16, $A$2:$A$16=D2, ""))
- FILTER($B$2:$B$16, $A$2:$A$16=D2, ""): This part of the formula checks each value in $A$2:$A$16; if it matches the value in D2, the corresponding value in $B$2:$B$16 will be included in the result array.
- $B$2:$B$16: The range from which the matching values will be retrieved.
- $A$2:$A$16=D2: The condition under which values are selected — only those rows where $A$2:$A$16 equals the content in D2 will be processed.
- TEXTJOIN(", ", TRUE, ...): This function takes the output of the FILTER function (an array of matches), and concatenates them into one text string, separated by the delimiter specified (comma and space), while automatically ignoring empty entries.
- ", ": Sets comma and space as a separator; you can change this symbol as needed, for instance use semicolons or line breaks.
- TRUE: Ensures that empty cells are ignored in the combination process, so you get a neatly formatted output.
Special note: This method requires Excel 365 or 2021, and does not work in older versions (e.g., Excel 2019, 2016, or earlier). Always check your Excel version before applying.
Tip: If your lookup value (e.g., D2) changes or additional matching items are added to the data range, the result updates automatically without any extra steps needed.
Potential limitations: On very large datasets, formula calculation time may increase. Also, users must ensure there are no merged cells in lookup or result ranges, as these can cause formula errors.
Vlookup and concatenate multiple corresponding values with Kutools for Excel
If you find built-in formula methods tricky or your version of Excel does not support advanced functions like TEXTJOIN and FILTER, Kutools for Excel offers a user-friendly graphical solution. The One-to-Many Lookup feature in Kutools enables you to look up and concatenate multiple matching results with just a few steps, making it suitable for both beginners and advanced users. With Kutools, there’s no need to write complicated formulas or codes, and it is especially handy when dealing with large or variable datasets that require repeated lookups and aggregations.
After installing Kutools for Excel, follow the steps below:
Click Kutools > Super Lookup > One-to-many Lookup (returns multiple results) to open the setup dialog. Within this dialog, you can quickly configure your lookup and output settings using the following steps:
- Select your target output cells for the concatenated results, and the cells containing the values you wish to search for;
- Indicate the table range which contains both the lookup key and results columns;
- Specify which column contains the lookup keys (Key Column) and the column whose values will be concatenated (Return Column);
- Click the OK button to confirm your settings and process the data.
Result: Kutools will now display all matching and concatenated values in your selected output cell. See screenshot:
This method is highly recommended for those who prefer working from the Excel interface without complex formulas or code. It also reduces the likelihood of formula errors and improves productivity in handling repetitive lookup and concatenation tasks.
Vlookup and concatenate multiple corresponding values with with User Defined Function
For users who are proficient with VBA (Visual Basic for Applications), or those using older Excel versions that lack dynamic array or FILTER function support, you can create a custom User Defined Function (UDF) to achieve flexible concatenation of multiple results. This method is universally compatible with all Excel versions and can be tailored to particular separator symbols or conditions.
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: Vlookup and concatenate multiple matching values in a cell
Function ConcatenateMatches(LookupValue As String, LookupRange As Range, ReturnRange As Range, Optional Delimiter As String = ", ") As String
'Updateby Extendoffice
Dim Cell As Range
Dim Result As String
Result = ""
For Each Cell In LookupRange
If Cell.Value = LookupValue Then
Result = Result & Cell.Offset(0, ReturnRange.Column - LookupRange.Column).Value & Delimiter
End If
Next Cell
If Result <> "" Then
Result = Left(Result, Len(Result) - Len(Delimiter))
End If
ConcatenateMatches = Result
End Function
3. Save and close the VBA editor. Return to your worksheet, and use this UDF by entering the formula: =ConcatenateMatches(D2, $A$2:$A$16, $B$2:$B$16) in a blank cell where you want your result. Drag the fill handle down to copy the formula to other cells as needed. All corresponding values based on a specific lookup value will be returned and concatenated in one cell, separated by a comma and space. See screenshot:
- D2: The lookup value to be matched within your dataset (LookupValue).
- A2:A16: The range where the function searches for the lookup value (LookupRange).
- B2:B16: The range containing the values to concatenate when the lookup value matches (ReturnRange).
Vlookup and concatenate multiple corresponding values with VBA code
For scenarios requiring repetitive use or for those wishing to avoid custom functions in worksheet cells, you can use a ready-made VBA macro to concatenate results directly. This method works well in shared environments where not all users may have the same version or add-ins.
1. Click Developer Tools > Visual Basic to open the VBA editor.
2. In the VBA window, click Insert > Module, then paste this code into the module:
Sub VLookupAndConcatenate()
Dim ws As Worksheet
Dim dataRange As Range, lookupRange As Range, resultRange As Range
Dim dict As Object
Dim i As Long, lastRow As Long
Dim lookupValue As Variant, result As String
Dim delimiter As String
delimiter = ", "
Set dict = CreateObject("Scripting.Dictionary")
Set ws = ActiveSheet
On Error Resume Next
Set dataRange = Application.InputBox( _
Prompt:="Please select the data range (contains lookup column and result column)", _
Title:="Select Data Range", _
Type:=8)
On Error GoTo 0
If dataRange Is Nothing Then Exit Sub
On Error Resume Next
Set lookupRange = Application.InputBox( _
Prompt:="Please select the lookup range (single column)", _
Title:="Select Lookup Range", _
Type:=8)
On Error GoTo 0
If lookupRange Is Nothing Then Exit Sub
On Error Resume Next
Set resultRange = Application.InputBox( _
Prompt:="Please select the starting cell for results output", _
Title:="Select Output Location", _
Type:=8)
On Error GoTo 0
If resultRange Is Nothing Then Exit Sub
resultRange.Resize(lookupRange.Rows.Count, 1).ClearContents
For i = 1 To dataRange.Rows.Count
lookupValue = dataRange.Cells(i, 1).Value
If Not dict.Exists(lookupValue) Then
dict.Add lookupValue, dataRange.Cells(i, 2).Value
Else
dict(lookupValue) = dict(lookupValue) & delimiter & dataRange.Cells(i, 2).Value
End If
Next i
For i = 1 To lookupRange.Rows.Count
lookupValue = lookupRange.Cells(i, 1).Value
If dict.Exists(lookupValue) Then
resultRange.Cells(i, 1).Value = dict(lookupValue)
Else
resultRange.Cells(i, 1).Value = "Not Found"
End If
Next i
MsgBox "Operation completed! Processed " & lookupRange.Rows.Count & " lookup values.", vbInformation
End Sub
3. Click the 
button to run the macro. The input boxes will prompt you to select your data range, lookup range, result rang. The concatenated result is then displayed directly in the selected output cells.
This macro approach is particularly useful if you frequently perform multiple concatenation searches with different values, as it avoids cluttering the worksheet with UDF calls.
You can easily adjust the delimiter in the code if needed, and extend the macro to output results to a cell or file per your workflow.
Concatenating multiple corresponding values in Excel is possible using various approaches, each with specific benefits depending on your situation. Whether you choose dynamic array formulas, add-ins like Kutools for Excel or VBA-based methods, you’ll improve your ability to analyze and display grouped data efficiently. Depending on the size and complexity of your dataset, consider which approach offers optimal performance and ease of maintenance for yourself or your team. In daily operations, check for data consistency, avoid merged cells, and verify reference ranges for best results. If you encounter errors in formula calculations, double-check that your ranges match the data and that you use the correct formula entry method for your Excel version.
For more advanced Excel techniques and a wide range of practical how-to guides, visit our extensive tutorial library.
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