Return values of duplicates

Hi

I'm running a Vloopkup on a table, problem with Vlookups is that they pickup the first matching lookup value but ignores any others that match in the same column:

ah 234
sa 334
po 434
ah 534
ra 634
sur 734
ah 834

I want to list all the values for ah in ONE cell, ie. 234,534,834. Any advice on how to do this? I'm thinking I need to put this into an array but not sure how.

The formula I'm actually using is

=IF(ISNA(VLOOKUP($A569,Payments,8,FALSE)),"",(VLOOKUP($A569,Payments,8,FALSE)))

thanks!

Thanks everyone for the help!

Tony - trying your code :

Dim DataIndex As Variant 'Used to loop through arrays, range objects, and collections
Dim strResult As String 'Used to build the result string

'Test if varData is an Array, Range, or Collection
If IsArray(varData) _
Or TypeOf varData Is Range _
Or TypeOf varData Is Collection Then

'Found to be an, array, range object, or collection
'Loop through each item in varData
For Each DataIndex In varData
'Check if the item isn't empty
If Len(DataIndex) > 0 Then
'Found the item isn't empty, check if user specified bUnique as True
If bUnique = True Then
'bUnique is true, check if the item has been included in the result yet
If InStr(1, "||" & strResult & "||", "||" & DataIndex & "||", vbTextCompare) = 0 Then
'Item has not been included in the result, add item to the result
strResult = strResult & "||" & DataIndex
End If
Else
'bUnique is not true, add item to the result
strResult = strResult & "||" & DataIndex
End If
End If
Next DataIndex

'Correct strResult to remove beginning delimiter and convert "||" to the specified sDelimiter
strResult = Replace(Mid(strResult, 3), "||", sDelimiter)

Else
'Found not to be an array, range object, or collection
'Simply set the result = varData
strResult = varData
End If

'Output result
ConcatAll = strResult

End Function


and I'm getting a Name error in my worksheet, and this error in my VBA - Compile error: Invalid outside procedure

Any ideas what's wrong?

It works! thanks! I see that I was missing the first few lines of code :/

I'm trying to apply this concept to the actual formula I'm using (in my original post so don't think this is a new thread):


=IF(ISNA(VLOOKUP($A569,Payments,8,FALSE)),"",(VLOOKUP($A569,Payments,8,FALSE)))

using your concatall formula I have:

=ConcatAll(IF(ISNA(VLOOKUP($A586,Payments,6,FALSE)),"",(VLOOKUP($A586,Payments,6,FALSE))),",")

The array doesn't work; only returns the first value. Any ideas what I'm doing wrong? Thanks!!!!