Lookup Returning Multiple Values

[izabela92]

Hello,

Is there a function similar to vlookup that will return multiple values instead of just the first time it appears in a list.

I have the same user submitting multiple files and when I get a report of all files submitted from a given day I want to be able to look up the user by their ID and have it list all of the files names, not just the first one in the list.

Thanks!

 

[lrobbo314]

I think that turning your data into a Pivot Table would be the best way to go about it.

 

[lrobbo314]

Or, if you want to use a custom function, I think this would work. It's set up like a vlookup where the first argument is the thing you are looking up, the second argument is the entire range, and the third argument is the column within that range that you want the return values to come from.

Function mLookup(Item As Variant, R As Range, Col As Long) As String
Dim AR()
Dim Res As String
AR = R.Value
For i = 1 To UBound(AR)
    If AR(i, 1) = Item Then
        Res = Res & AR(i, Col) & ", "
    End If
Next i
If Len(Res) > 0 Then
    mLookup = Left(Res, Len(Res) - 2)
Else
    mLookup = "#N/A"
End If
End Function