I think that it can be done only with a UDF:

Function findcolor(brng As String) As String
Dim c As Range
With Range("C:C")
Set c = .Find(brng)
If Not c Is Nothing Then
firstAddress = c.Address
Do
result = result & Range("D" & c.Row) & ","
Set c = .Find(brng, c)
Loop While Not c Is Nothing And c.Address < firstAddress
End If
End With
If Len(result) = 0 Then
findcolor = result
Else
findcolor = Left(result, Len(result) - 1)
End If
End Function

Usage (in A2, it's advisable to insert a header row):
=findcolor(B2)

Regards,
Stefi

€žMelody€� ezt Ã*rta:

Here's the scenario:

A B C D
1 Blue Blue John
2 Yellow Black Mary
3 Black Yellow Carol
4 Yellow Terri
5 Black Joe
6 Blue Tracy


What I need is a formula in Column A that will return all the names in
Column D where Column C matches Column B. So the results would look like
this:

A B C D
1 John, Tracy Blue Blue John
2 Carol, Terri Yellow Black Mary
3 Mary, Joe Black Yellow Carol
4 Yellow Terri
5 Black Joe
6 Blue Tracy

I'm using Excel 2003. This is a small sample, the spreadsheet I'm working
with is much larger. I can't attach the file because it contains
confidential information.

Thanks.