VLOOKUP函数功能是按列查找、最终返回该列所需查询所对应的值,是Excel中广泛应用的查找、核对的函数。但局限性在于,只能返回匹配到第1个值,如果需要返回指定第N个值,就需要编写自定义函数进行扩展
Function VLOOKUP_INDEX(lookup_value As String, table_array As Range, Optional col_index As Integer = 2, Optional index As Integer = 1) As String
'函数定义VLOOKUP_INDEX(要查找的值,查找区域,匹配值所在列数,需要返回第几个匹配的值)返回与要查找的值匹配的结果
Dim i As Long, find_cell As Range, cell_address As String
With table_array.Columns(1)
'如果区域第1个单元格等于查找的值,则将该单元格赋值cell;否则使用find查找,将单元格赋值cell
If .Cells(1) = lookup_value Then 'range.find方法不是从第1个开始查找
Set find_cell = .Cells(1)
Else
'按值查找xlValues,完全匹配xlWhole
Set find_cell = .Find(lookup_value, LookIn:=xlValues, lookat:=xlWhole)
End If
If Not find_cell Is Nothing Then '未发现匹配项时,find方法返回 Nothing
cell_address = find_cell.Address '记录单元格地址
Do
i = i + 1
If i = index Then '如果是需要返回的index,则返回对应的匹配值
VLOOKUP_INDEX = find_cell.Offset(0, col_index - 1)
Exit Function
Else
Set find_cell = .Find(lookup_value, find_cell, LookIn:=xlValues, lookat:=xlWhole) '查找下一个
End If
Loop While Not find_cell Is Nothing And find_cell.Address <> cell_address
Else
VLOOKUP_INDEX = "" '如果找不到则返回空值
End If
End With
End Function
Sub VLOOKUP_INDEX帮助信息()
'运行一次后该帮助信息生效
Dim 函数名称 As String '函数名称
Dim 函数描述 As String '函数描述
Dim 参数个数(4) As String '函数参数描述 数组 个数
函数名称 = "VLOOKUP_INDEX"
函数描述 = "扩展VLOOKUP,可以指定返回第几个匹配的值,完全匹配"
参数个数(0) = "要查找的值,单元格、文本字符串"
参数个数(1) = "查找区域,同VLOOKUP,第1列包含要查找的值"
参数个数(2) = "匹配值所在列数,同VLOOKUP,数字"
参数个数(3) = "需要返回第几个匹配的值,数字"
Call Application.MacroOptions(macro:=函数名称, Description:=函数描述, ArgumentDescriptions:=参数个数)
End Sub
举例:
G2处查找第2个符合条件的值,公式 =VLOOKUP_INDEX(F1,A1:B9,2,2)