在单个单元格(用逗号分隔)中查找多个值，然后将值返回到单个单元格(也以逗号分隔) [英] Lookup multiple values in a single cell (separated by commas) and then return the values to a single cell (also comma separated)

问题描述

我需要在sheet1的A列上使用单元格A1，A2值进行Vlookup

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

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

，依此类推. vLookup表在具有A，B，C，D列的另一张工作表中.A列具有

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

查找结果将显示在工作表1结果单元格B1和B2的B列中

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

B
CN, US, UK
IN, SG

推荐答案

您可以创建用户函数以通过VLOOKUP函数循环值:

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.

这篇关于在单个单元格(用逗号分隔)中查找多个值，然后将值返回到单个单元格(也以逗号分隔)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！