How to vlookup and return multiple values without duplicates in Excel? 

Sometimes, you may want to vlookup and return multiple matched values into a single cell at once. But, if there are some repeated values populated into the returned cells, how could you ignore the duplicates and only keep the unique values when returning all matching values as following screenshot shown in Excel?

Vlookup and return multiple matching values without duplicates by using User Defined Function

Vlookup and return multiple matching values without duplicates by using User Defined Function

The following VBA code can help you to return multiple matching values without duplicates, please do as this:

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 return multiple unique matched values:

Function MultipleLookupNoRept(Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer)
    Dim xDic As New Dictionary
    Dim xRows As Long
    Dim xStr As String
    Dim i As Long
    On Error Resume Next
    xRows = LookupRange.Rows.Count
    For i = 1 To xRows
        If LookupRange.Columns(1).Cells(i).Value = Lookupvalue Then
            xDic.Add LookupRange.Columns(ColumnNumber).Cells(i).Value, ""
        End If
    Next
    xStr = ""
    MultipleLookupNoRept = xStr
    If xDic.Count > 0 Then
        For i = 0 To xDic.Count - 1
            xStr = xStr & xDic.Keys(i) & ","
        Next
        MultipleLookupNoRept = Left(xStr, Len(xStr) - 1)
    End If
End Function

3. After inserting the code, then click Tools > References in the opened Microsoft Visual Basic for Applications window, and then, in the popped out References – VBAProject dialog box, check Microsoft Scripting Runtime option in the Available References list box, see screenshot:

4. Then click OK to close the dialog box, save and close the code window, return to the worksheet, and enter this formula: =MultipleLookupNoRept(E2,A2:C17,3) into a blank cell where you want to output the result, press Enter key to get the correct result as you need. See screenshot:

Note: In the above formula, E2 is the criteria that you want to vlookup, A2:C17 is the data range that you want to use, the number 3 is the column number which contains the returned values.