when I've had to do this, I've set it up to return an array. This means the UDF has to be entered as an array function over the range where you want the results, but it generally works pretty well. Adapted code:
Function VlookupAll(sSearch As String, rRange As Range, _
Optional lLookupCol As Long = 2, Optional sDel As String = ",") As Variant
Dim i As Long, sTemp As String
dim temparray(35) as string (adjust array size to needs, or use Redim to make array size variable)
If lLookupCol > rRange.Columns.Count Or sSearch = "" Or _
(lLookupCol < 0 And rRange.Columns.Count > 1) Then
VlookupAll = CVErr(xlErrValue)
Exit Function
End If
VlookupAll = ""
For i = 1 To rRange.Rows.Count
If rRange(i, 1).Text = sSearch Then
If lLookupCol >= 0 Then
temparray(i)= rRange(i, lLookupCol).Text
Else
temparray(i)= rRange(i).Offset(0, lLookupCol).Text
End If
sTemp = sDel
End If
Next i
vlookupall=temparray
End Function
Bookmarks