单元格内一对多查询

一、图1

二、图2

三、函数解决

1、公式截图

2、公式

=VLOOKUP(--TRIM(MID(SUBSTITUTE(A2,'.',REPT(' ',99)),1,99)),参数表!A:B,2,0)&','&VLOOKUP(--TRIM(MID(SUBSTITUTE(A2,'.',REPT(' ',99)),100,99)),参数表!A:B,2,0)&','&VLOOKUP(--TRIM(MID(SUBSTITUTE(A2,'.',REPT(' ',99)),199,99)),参数表!A:B,2,0)

3、公式解释

• --TRIM(MID(SUBSTITUTE(A2,'.',REPT(' ',99)),1,99))用拉大距离法把单元格每个数字接点号分隔出来作为vlookup函数的第1参数到参数表中查找，查找出来的3个结果然后用逗号把它们连接起来就可以了

• 可以这位朋友又提出问题，如果数字中间分隔符是任意的字符如何实现，且可能输入的数字不只三个，又如何实现。那这里用函数实现就公式更长了，下面我用vba自定义函数实现

四、vba自定义函数实现

1、公式截图

2、公式

=提(A9,参数表!$A$1:$B$20)

3、动画演示

4、代码

Option Explicit

Function 提(rg As Range, SumRg As Range)

    Dim arr1, arr2, arr3(), x, k, y, Reg, M, Mat

    Application.Volatile '让函数刷新

    Set Reg = CreateObject('Vbscript.RegExp') '引用外部对象正则表达式

    With Reg

        .Global = True

        .Pattern = '\d ' '匹配任意长的数字

    End With

    Set Mat = Reg.Execute(rg) '把所有的符合规则的装进mat

    arr1 = SumRg '把第2参数装入数组，目的为了提速度

    For Each M In Mat循环符合规则的数字

        For y = 1 To UBound(arr1) '循环数组arr1

            If arr1(y, 1) = Val(M) Then '判断是否和M相等于，由于M还是文本型的数字，通过val处理转为数值型数字

                k = k 1 '计数

                ReDim Preserve arr3(1 To k) '重新定义arr2的空间，且保留原有值

                arr3(k) = arr1(y, 2) '把数组arr1的第2参装进arr3

            End If

        Next y

    Next M

    提 = Join(arr3, ',') '把结果赋值给函数名

End Function