Lookup to return multiple results in one cell

[jaybee3]

Name # of cats
Jack X123
Jack X224
Lisa X123
Ben Z324
Derek X123
Derek Z324

Above is my example data set.

Ideally what I would like to do is be able to have concatonated
results on one line based off a lookup.

E.g.

I look for Jack and the formula returns: "X123, X224" in one cell.

Is there anything out there that can do that?

 

[merjet]

Function MyLookup(LookFor, FindCol As Range, GetCol As Range) As
String
For Each c In FindCol
If LookFor = c Then
If MyLookup = "" Then
MyLookup = Cells(c.Row, GetCol.Column)
Else
MyLookup = MyLookup & ", " & Cells(c.Row, GetCol.Column)
End If
End If
Next c
End Function

You may want to add some error checking.

Hth,
Merjet

 

[amontes]

Function MyLookup(LookFor, FindCol As Range, GetCol As Range) As
String
For Each c In FindCol
If LookFor = c Then
If MyLookup = "" Then
MyLookup = Cells(c.Row, GetCol.Column)
Else
MyLookup = MyLookup & ", " & Cells(c.Row, GetCol.Column)
End If
End If
Next c
End Function

You may want to add some error checking.

Hth,
Merjet

Hello, I know near to nothing about VBA, would you tell me how to use
your macro?
Thanks in advance,
Armando