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

• Using TEXTJOIN and FILTER Functions (Excel365/2021)
• Using Kutools for Excel
• Using User Defined Function
• Using VBA code for Multiple Concatenations

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, ""))

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:

1. Select your target output cells for the concatenated results, and the cells containing the values you wish to search for;
2. Indicate the table range which contains both the lookup key and results columns;
3. Specify which column contains the lookup keys (Key Column) and the column whose values will be concatenated (Return Column);
4. 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:

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.