VBA Function VLOOKUP with Multiple Matches

[Martunis99]

Hi guys,

So I created a function that sort of works like a VLOOKUP but for each criteria value returns multiple matches all concatenated and separated by comma (in the same cell).
Do you think this function is going to work fine?

Or does it need some adjustments?

Thank you.

The code:

Public Function VlookupV2(Criteria As Range, Interval As Range, Column As Integer)

Dim Result As String

For i = 1 To Interval.Rows.Count
    If Criteria.Value = Interval.Columns(1).Rows(i).Value Then
        If InStr(Result, Interval.Columns(Column).Rows(i).Value) = 0 Then
            Result = Result & Interval.Columns(Column).Rows(i).Value & ", "
        End If
    End If
Next i

Result = Left(Result, Len(Result) - 2)
VlookupV2 = Result

End Function

 

[jasonb75]

Or does it need some adjustments?

If it works as required then no need to change it. One point to remember is that it will be case sensitive if the criteria is text. I would just add something like this at the top,

If Column > Interval.Columns.Count Then VlookupV2 = CVErr(xlErrNA): Exit Function

With your code, you could enter

=VLOOKUPV2(A2,B2:B10,2)

and the formula would work even though the column specified falls outside of the interval range, but it would not recalculate if the source data was changed unless you made the function volatile. By adding the line above you can force the function to return a #N/A error instead if it was entered in the same way as the example.

Also, the function would not be needed with office 365, you could do the same with built in functions, for example

=TEXTJOIN(", ",1,UNIQUE(FILTER(C2:C10,B2:B10=A2)))

 

[Martunis99]

Hi Jason,

Great point with the case sensitive remark. I will probably adjust it so that it considers strings and their format. Didn't think about that first cause I mostly work with numbers, and was so thrilled I finally was able to find a workaround for one the biggest limitation of lookup formulas or its numerous variations (I only recently started getting into programming).

As for the second point, again, didn't even think about that. But I should definitely add it.

Unfortunately, I do not have Office 365 so that would not be an option for me, though it seems Office 365 does have a few cool new tricks!

Thank you so much for you all around awesome feedback!