Multiple rows returned from a vlookup

Nick,

I have an excel table with 2 columns - client & contact. There are 0..n contacts per client. I want to do a vlookup and get all the contact names (comma separated). How can I do that?

Thx
Laks

Nick's picture

Laks - why not use a pivot

Laks - why not use a pivot table to get the required names, then unfortunately, you need some VBA to concatenate the names to separate by commas and produce the string (which I presume ur using to send mail with)

Easier to do with VBA than

Easier to do with VBA than worksheet functions ..

Assuming that
a) You have a list of unique contact names in range G1:G5
b) Your Clients are listed in range B11:B30
c) The client contacts are listed in C11:C30

Then this code will compile the string for you ..

Private Sub CommandButton1_Click()
Dim iY As Integer, iY1 As Integer, sContact As String, SLineOut As String

For iY = 1 To 5 'Iterate through all contacts
sContact = Range("G" & iY).Value 'Assigns the reference contact name
SLineOut = "" 'Reset the output string for each new reference contact
For iY1 = 11 To 30 'Iterate through all clients
If Range("B" & iY1).Value = sContact Then ' current client contact = reference contact
SLineOut = SLineOut & Range("A" & iY1).Value & ";" 'Add to output string if true
End If
Next iY1
SLineOut = Left(SLineOut, Len(SLineOut) - 1) 'Remove final semicolon
Range("J" & iY).Value = SLineOut
Next iY

End Sub

Numbers and cell references need to be amended to suit

VBA works

Thanks Nick and Andy. I took the VBA route.