A little slow for my 2000 rows of data but works perfectly! Thanks!

"Jacob Skaria" wrote:

Try this UDF (User Defined function). From workbook launch VBE using Alt+F11.
From menu Insert a Module and paste the below function.Close and get back to
workbook and try the below formula.

With your sample data try the below formula
=VLOOKUP_CONCAT(A1:B7,"B",2,", ")

Syntax:
=VLOOKUP_CONCAT(rngRange,strLookupValue,intColumn, strDelimiter)

rngRange is the Range
strLookupValue is the lookup string or cell reference
inColumn is the column to be concatenated
strDelimiter Optional . Default is space

Examples:
'1. To vlookup 'jacob' and concatenate all entries of 2nd column
=VLOOKUP_CONCAT(A1:B10,"jacob",2)

'2. with lookup value in cell C1
=VLOOKUP_CONCAT(A1:B10,C1,2)

'3. with delimiter as comma
=VLOOKUP_CONCAT(A1:B10,C1,2,",")

Function VLOOKUP_CONCAT(rngRange As Range, _
strLookupValue As String, intColumn As Integer, _
Optional strDelimiter As String = " ")
Dim lngRow As Long
For lngRow = 1 To rngRange.Rows.Count
If StrComp(CStr(rngRange(lngRow, 1)), _
strLookupValue, vbTextCompare) = 0 Then _
VLOOKUP_CONCAT = VLOOKUP_CONCAT & strDelimiter & _
rngRange(lngRow, intColumn)
Next
VLOOKUP_CONCAT = Mid(VLOOKUP_CONCAT, Len(strDelimiter) + 1)
End Function

If this post helps click Yes
---------------
Jacob Skaria


"Kevin W" wrote:

I believe vlookup is set to bring in the first match in the range. But is it
possible to bring in all matches and display them in a list separated by
commas? So:

A Yellow
B Red
C Green
B Blue
D Purple
B Orange
E Black

Input into D1 =+vlookup("B",A1:B7,2,FALSE) will retrieve "Red."

Is there a formula to use that will bring in (Red, Blue, Orange) - all into
D1?? Thanks