Vlookup needed to return multiple unique values into one cell

[chiguy28]

Hi everyone, I'm hoping someone can help me out. I am looking for a macro that will return vlookup multiple values in a single cell in excel. I have used the below one from Lewiy that was listed in another thread, but it is returning the value every time it is found in the source spreadsheet. Is there anyway to modify the below formula to add a comma between values, and to only return unique values once?

Function MYVLOOKUP(lookupval, lookuprange As Range, indexcol As Long)
Dim r As Range
Dim result As String
result = ""
For Each r In lookuprange
If r = lookupval Then
result = result & " " & r.Offset(0, indexcol - 1)
End If
Next r
MYVLOOKUP = result
End Function

 

[RickXL]

Hi,

You mean like this:

Function MYVLOOKUP(lookupval, lookuprange As Range, indexcol As Long)
    
    'Requires: Tools-->References--> Microsoft Scripting Runtime
    Dim dic As Dictionary
    Dim r As Range
    Set dic = New Dictionary

    For Each r In lookuprange
        If r = lookupval Then dic(r.Offset(0, indexcol - 1).Value) = vbNullString
    Next r
    
    MYVLOOKUP = Join(dic.Keys, ",")
    
End Function

Note: The way the macro is written it needs you to specify a reference to the Microsoft Scripting Runtime library. To do that, when in the VB Editor, select Tools-->References-->Microsoft Scripting Runtime.

It uses a Dictionary object. Dictionaries will ensure that only unique keys are added to it. SO every located value is added to the dictionary but the duplicate values are discarded.

dic.Keys works like a array so all the elements can be joined together with the Join command.