VLOOKUP函數(shù)功能是按列查找藐守、最終返回該列所需查詢所對(duì)應(yīng)的值挪丢，是Excel中廣泛應(yīng)用的查找、核對(duì)的函數(shù)卢厂。但局限性在于乾蓬，只能返回匹配到第1個(gè)值，如果需要返回指定第N個(gè)值慎恒，就需要編寫自定義函數(shù)進(jìn)行擴(kuò)展
Function VLOOKUP_INDEX(lookup_value As String, table_array As Range, Optional col_index As Integer = 2, Optional index As Integer = 1) As String
'函數(shù)定義VLOOKUP_INDEX(要查找的值任内，查找區(qū)域，匹配值所在列數(shù)融柬，需要返回第幾個(gè)匹配的值)返回與要查找的值匹配的結(jié)果
Dim i As Long, find_cell As Range, cell_address As String
With table_array.Columns(1)
'如果區(qū)域第1個(gè)單元格等于查找的值死嗦，則將該單元格賦值cell；否則使用find查找粒氧，將單元格賦值cell
If .Cells(1) = lookup_value Then 'range.find方法不是從第1個(gè)開始查找
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 '未發(fā)現(xiàn)匹配項(xiàng)時(shí)，find方法返回 Nothing
cell_address = find_cell.Address '記錄單元格地址
Do
i = i + 1
If i = index Then '如果是需要返回的index外盯，則返回對(duì)應(yīng)的匹配值
VLOOKUP_INDEX = find_cell.Offset(0, col_index - 1)
Exit Function
Else
Set find_cell = .Find(lookup_value, find_cell, LookIn:=xlValues, lookat:=xlWhole) '查找下一個(gè)
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幫助信息()
'運(yùn)行一次后該幫助信息生效
Dim 函數(shù)名稱 As String '函數(shù)名稱
Dim 函數(shù)描述 As String '函數(shù)描述
Dim 參數(shù)個(gè)數(shù)(4) As String '函數(shù)參數(shù)描述 數(shù)組 個(gè)數(shù)
函數(shù)名稱 = "VLOOKUP_INDEX"
函數(shù)描述 = "擴(kuò)展VLOOKUP摘盆，可以指定返回第幾個(gè)匹配的值，完全匹配"
參數(shù)個(gè)數(shù)(0) = "要查找的值饱苟，單元格骡澈、文本字符串"
參數(shù)個(gè)數(shù)(1) = "查找區(qū)域，同VLOOKUP掷空，第1列包含要查找的值"
參數(shù)個(gè)數(shù)(2) = "匹配值所在列數(shù)肋殴，同VLOOKUP，數(shù)字"
參數(shù)個(gè)數(shù)(3) = "需要返回第幾個(gè)匹配的值坦弟，數(shù)字"
Call Application.MacroOptions(macro:=函數(shù)名稱, Description:=函數(shù)描述, ArgumentDescriptions:=參數(shù)個(gè)數(shù))
End Sub
舉例：
G2處查找第2個(gè)符合條件的值护锤，公式 =VLOOKUP_INDEX(F1,A1:B9,2,2)
VLOOKUP_INDEX
