vlookup multiple values in one cell

Question

I am trying to vlookup multiple values in one cell based on the a selection from another cell.

I have the below table where I select a single "Gym" the "GymIDs" is automatically populated. I have then used the below VBA to allow me to select multiple "Gyms", I also want it to show me multiple "GymIDs".

Current vlookup =VLOOKUP(M7,Ignore!F1:G300,2,FALSE)

for some reason I could only upload one image so put them all together

excel table

VBA code for multiple selections

Private Sub Worksheet_Change(ByVal Target As Range)

'Code by Sumit Bansal from https://trumpexcel.com
' To Select Multiple Items from a Drop Down List in Excel

Dim Oldvalue As String
Dim Newvalue As String

On Error GoTo Exitsub
If Not Intersect(Target, Range("M7:M30")) Is Nothing Then
    If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
    GoTo Exitsub
    ElseIf Target.Value = "All" Then GoTo Exitsub
    ElseIf Target.Value = "Select" Then GoTo Exitsub
    ElseIf Target.Value = "" Then GoTo Exitsub
    Else
        Application.EnableEvents = False
        Newvalue = Target.Value
        Application.Undo
        Oldvalue = Target.Value
        If Oldvalue = "" Then
            Target.Value = Newvalue
        ElseIf Oldvalue = "All" Then
            Target.Value = Newvalue
        ElseIf Oldvalue = "Select" Then
            Target.Value = Newvalue
        Else
            Target.Value = Oldvalue & ", " & Newvalue
        End If
    End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub

Zerk Zerk · Accepted Answer · 2017-10-02T12:37:50

I'd suggest writing a function in the lookup column. As you've not provided the vlookup i'll leave that part to you. The main concept at play here is to detect whether the Gyms column is a single value or a comma separated list and treat it based upon that:

Public Function GymLookup(ByVal stringToProcess As String) As Variant
Application.Volatile

Dim var As Variant
Dim arrData As Variant
Dim strConcat As String

If InStr(1, stringToProcess, ",") > 0 Then
    arrData = Split(stringToProcess, ",")
    For Each var In arrData
        'multiple handler
        If strConcat = "" Then
            strConcat = "enter your vlookup to resolve a result here"
        Else
            strConcat = strConcat & ", " & "enter your vlookup to resolve a result here"
        End If
    Next var
    GymLookup = strConcat
Else
    'Single cell lookup
    GymLookup = "enter your vlookup to resolve a result here"
End If

End Function

vlookup multiple values in one cell

1 Answers