Excel自定义函数实现多值查找

Excel中，很多人都用过vlookup这个函数，它可以实现单值查找，即找到每一个匹配的值。下面来介绍一个自定义的多值查找函数，命名为ClookUp
详细代码如下：Function ClookUp(查找值 As String, 区域 As Range, Optional 列 As Integer = 2, Optional 索引号 As Integer = 1) As String '  确定函数ClookUp，类型为String。包括四个参数，前两个为必选参数，后两个为可选参数  Dim i As Long, cell As Range, Str As String  With 区域.Columns(1)  '引用区域的第一列 '    如果引用区域第一个单元格等于查找的对象，那么将该单元格赋予变量Cell。否则使用Find方法查找，将找到的单元格赋予变量Cell    If .Cells(1) = 查找值 Then Set cell = .Cells(1) Else Set cell = .Find(查找值, LookIn:=xlValues, lookat:=xlWhole)    If Not cell Is Nothing Then  '如果找到      Str = cell.Address  '记录单元格地址      Do  '通过循环语句继续查找        i = i + 1  '累加变量，表示符合条件的个数 '        如果变量等于最后一个参数，那么将查找到的单元格右边的值赋予CLookUp函数        If i = 索引号 Then ClookUp= cell.Offset(0, 列 - 1): Exit Function        Set cell = 区域.Find(查找值, cell, , xlWhole)  '查找下一个 '        如果找到的目标单元格地址不等于第一次找到的单元格的地址就继续查找      Loop While cell.Address  Str    Else      ClookUp= ""  '如果找不到则直接返回空白    End If  End WithEnd Function

参数说明：

在第一参数“区域”所代表的列中查找第二参数“查找值”的值，然后根据第三参数“列”的值确定返回值所在列的值

如果有找到多个值，那么由第四参数决定返回第几个值

忽略第三参数时表示默认值是为2，即返回“区域”右边一列的值

忽略第四参数时表示默认值1，即返回第一个值

使用函数可参考以下公式：=ClookUp(E$2,B$1:C$12,2,ROW(A1))

设计实例效果
如下表所示：对应的成品有多个部件，需要要通过成品编码去查询有哪些部件，Excel的Vlookup函数不能满足需要

1500625181447143.jpg

输入自定义函数ClookUp如C2==ClookUp(A$2,基础资料!A$1:D$671,2,ROW(A1))通过A$2的值去查找上述工作表“基础资料”对应区域对应列的第n个值；如下表所示，默认查找15个值，每一个值对应5列的数据；

1500625206101653.jpg

利用设置好的自定义函数进行查询在A$2位置输入需要查询的成品编码，即可以查询到对应的数据。

1500625219120871.jpg