Function vLookDown(iValue As Range, iRngs As Range, iIndex As Byte)
'iValue 要查找的值
'iRngs 查找值的区域,在区域的最后一列查找iValue
'iIndex找到之后返回区域的第iIndex列,iIndex取值范围为0~255
Dim iVal As String
'为要查找的值声明变量,声明其类型为字符串
iVal = iValue.Value
'要查找的值
Set iValue = Nothing
'释放内存
If Len(iVal) > 0 Then
'如果不为空值则查找
Dim iRngArr()
'声明数组
iRngArr = iRngs
'区域赋值给数组
iRngsCount = iRngs.Count
'区域的总单元格数
Set iRngs = Nothing
'释放内存
For i = 1 To UBound(iRngArr)
'1到最后一行,UBound(iRngArr)为选中区域的行数
findValue = iRngArr(i, iRngsCount / UBound(iRngArr))
'当前对比的值iRngsCount(总单元格数)/UBound(iRngArr)(总行数) = 总列数
If iVal = findValue Then
vLookDown = iRngArr(i, iIndex)
'如果找到了则返回该值左边区域的第Iindex列
Exit For
'退出for循环
End If
vLookDown = CVErr(xlErrNA)
'如果没找到则返回 #N/A
Next i
Else
vLookDown = CVErr(xlErrNA)
'如果为空值则返回 #N/A
End If
End Function
检验结果: