打开EXCELL表,然后按F12,在工程资源管理器中点右建增加新模块,在新模块中输入如下内容
Function look(查找值 As String, 区域 As Range, 列 As Long, 索引号) As String
Application.Volatile
On Error Resume Next
Dim onlys As New Collection, arr(), i As Long, j As Long
arr = 区域
For i = 1 To UBound(arr)
If arr(i, 1) = 查找值 Then
If arr(i, 列) <> "" Then onlys.Add arr(i, 列), CStr(arr(i, 列)): j = j + 1
End If
Next i
look = onlys(索引号)
If j < 索引号 Then look = ""
End Function
输完以上内容后,你就可以在EXCELL表格中用=look()函数,参数和vlookup一样。第一个参与查值用绝对地址,往下拉可找出所有要找的值!
效果如截图