Looping VLOOKUP style function with separators

Question

So I'm working on a function that works like a normal VLOOKUP but will cycle through and return multiple values. My problem now is that I'm not sure how I can make the following code output multiple values WITH separators. (i.e. "Result1, Result2, Result3") If there's an easier way/work around to what I have here I'll take the advice.

Function KVLOOKUP(LookedValue As Variant, Matrix As Variant, Column As Integer) As Variant
 Dim Result() As Integer

 Dim i As Integer
 Dim Counter As Long
 If IsObject(Matrix) Then Matrix = Matrix.Value
 On Error Resume Next
 Do
  i = i + 1
 Counter = UBound(Matrix, i)
 Loop Until Err.Number <> 0

 If Counter < Column Then KVLOOKUP = CVErr(xlErrNum): Exit Function
 Counter = 0
 For i = LBound(Matrix, 1) To UBound(Matrix, 1)
 If Matrix(i, 1) = LookedValue Then
 Counter = Counter + 1
 ReDim Preserve Result(1 To Counter)
 Result(Counter) = Matrix(i, Column)
 End If
 Next i
 On Error GoTo 0
 If Counter = 0 Then
 KVLOOKUP = CVErr(xlErrNA)
 Else

       KVLOOKUP = Insert command to add separator and output values here.

 End If
End Function

one way would be to loop through elements of your array and build up a string that holds that values. Something like stringValue = stringValue & "|" & Array(indexCounter)... — sous2817, Jun 24 '14 at 14:22
@sous2817 This is borrowed code so I'm not totally sure how to implement that into this current situation. — ryano, Jun 26 '14 at 20:10

score 0 · Accepted Answer · answered Jun 27 '14 at 13:12

Here is one way. It basically loops through your Results array and builds up a comma-separated string value that gets dumped out. I didn't test it all that thoroughly, so you should plan on having to build in some error checking, but hopefully it will get you going the right direction:

Function KVLOOKUP(LookedValue As Variant, Matrix As Variant, Column As Integer) As Variant

 Dim Result() As Variant
 Dim i As Integer
 Dim Counter As Long

 If IsObject(Matrix) Then Matrix = Matrix.Value
 On Error Resume Next
 Do
  i = i + 1
 Counter = UBound(Matrix, i)
 Loop Until Err.Number <> 0

 If Counter < Column Then KVLOOKUP = CVErr(xlErrNum): Exit Function
 Counter = 0
 For i = LBound(Matrix, 1) To UBound(Matrix, 1)
 If Matrix(i, 1) = LookedValue Then
 Counter = Counter + 1
 ReDim Preserve Result(1 To Counter)
 Result(Counter) = Matrix(i, Column)
 End If
 Next i
 On Error GoTo 0

 If Counter = 0 Then
    KVLOOKUP = CVErr(xlErrNA)
 Else
    KVLOOKUP = Result(1)
    For i = 2 To UBound(Result)
        KVLOOKUP = KVLOOKUP & "," & Result(i)
    Next i
 End If
End Function

Looping VLOOKUP style function with separators

1 Answers1

Linked