VBA Function VLOOKUP with Multiple Matches

Martunis99

New Member
Joined
Aug 16, 2021
Messages
19
Office Version
  1. 2016
Platform
  1. Windows
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:
Rich (BB 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
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,559
Office Version
  1. 365
Platform
  1. Windows
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,
VBA Code:
If Column > Interval.Columns.Count Then VlookupV2 = CVErr(xlErrNA): Exit Function
With your code, you could enter
Excel Formula:
=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
Excel Formula:
=TEXTJOIN(", ",1,UNIQUE(FILTER(C2:C10,B2:B10=A2)))
 
Solution

Martunis99

New Member
Joined
Aug 16, 2021
Messages
19
Office Version
  1. 2016
Platform
  1. Windows
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!
 

Forum statistics

Threads
1,148,179
Messages
5,745,199
Members
423,932
Latest member
pablo2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top