Lookup Returning Multiple Values

izabela92

New Member
Joined
Jul 10, 2017
Messages
5
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!
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
2,711
Office Version
365, 2019, 2016
Platform
Windows
I think that turning your data into a Pivot Table would be the best way to go about it.
 

lrobbo314

Well-known Member
Joined
Jul 14, 2008
Messages
2,711
Office Version
365, 2019, 2016
Platform
Windows
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.

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,095,479
Messages
5,444,723
Members
405,298
Latest member
fxtrtr17

This Week's Hot Topics

Top