Lookup multiple values in a single cell (separated by commas) and then return the values to a single cell (also comma separated)

Question

I need to Vlookup on column A of sheet1 with cell A1, A2 values

SD-121, SD-232, SD-23
SD-323,SD-333

and so on.. vLookup table in a different sheet with column A, B, C, D. Column A having

A            B
SD-232      US
SD-23       UK
SD-323      IN
SD-333      SG
SD-121      CN

The lookup result is to be displayed in Column B of sheet1 result cell B1 and B2

B
CN, US, UK
IN, SG

It's not clear from your question what are you trying to achieve. Try to provide an example and expected result, so others will understand you. — tarashypka

JMcD JMcD · Accepted Answer · 2016-08-14T15:12:17

You can create a user function to loop the values through the VLOOKUP function:

Function VLOOKUPARRAY(ByVal lookup_val As Range, ByVal table_array As Range, ByVal col_index_num As Integer, Optional ByVal range_lookup As Integer = 0) As String
    Dim s As String
    Dim a1() As String
    Dim a2() As Variant
    Dim i As Integer

    'Recalculate whenever a calculation happens on the worksheet
    Application.Volatile

    'Get the lookup value from the cell
    s = lookup_val.Value
    'Split into array
    a1 = Split(s, ",")
    'Set output array to input array dimensions
    ReDim a2(0 To UBound(a1))
    'Loop through input array and set output array elements to lookup results using application lookup function.
    For i = 0 To UBound(a1)
        a2(i) = Application.WorksheetFunction.VLookup(Trim(a1(i)), table_array, col_index_num, range_lookup)
    Next i
    'Loop through output array and load values into a string
    s = ""
    For i = 0 To UBound(a2)
        s = s & a2(i) & ", "
    Next i
    'Knock the final ", " off the end of the string
    s = Left(s, Len(s) - Len(", "))
    'Set function output to string value.
    VLOOKUPARRAY = s
End Function

That was quick and dirty, but it gave me the desired output. Adjust as needed.

Lookup multiple values in a single cell (separated by commas) and then return the values to a single cell (also comma separated)

2 Answers