如何用VBA实现支持近似匹配的向左VLOOKUP自定义函数？

问题分析 #

原代码存在两个核心问题：

• col_index_num 引用逻辑错误：直接使用Offset(0, -1 * col_index_num)未考虑查找列位置，也未校验索引合法性
• Find 方法仅支持精确匹配：近似匹配需改用Match方法配合范围判断，因为Find无近似匹配参数

修正后的VBA代码 #

Public Function VLOOKUPLEFT(lookup_value As Variant, table_array As Range, col_index_num As Integer, Optional range_lookup As Boolean = False) As Variant
    Dim lookup_col As Range
    Dim match_row As Variant
    Dim result_col As Range
    
    ' 校验索引范围，避免越界
    If col_index_num < 1 Or col_index_num > table_array.Columns.Count Then
        VLOOKUPLEFT = "#INDEX!"
        Exit Function
    End If
    
    ' 定义查找列：取table_array的最后一列（模拟原生VLOOKUP查找列逻辑，反向向左查）
    Set lookup_col = table_array.Columns(table_array.Columns.Count)
    
    ' 定义结果列：向左偏移col_index_num对应的列数
    Set result_col = table_array.Columns(table_array.Columns.Count - col_index_num)
    
    ' 执行匹配：精确/近似模式切换
    On Error Resume Next
    match_row = Application.Match(lookup_value, lookup_col, IIf(range_lookup, 1, 0))
    On Error GoTo 0
    
    ' 处理匹配失败场景
    If IsError(match_row) Then
        VLOOKUPLEFT = "#N/A"
    Else
        VLOOKUPLEFT = result_col.Cells(match_row, 1).Value
    End If
End Function

代码细节说明 #

• 索引合法性校验：先判断col_index_num是否在1到table_array总列数之间，避免非法索引导致的运行错误
• 查找列与结果列映射：默认将table_array最后一列作为查找基准列（对应原生VLOOKUP的第一列），结果列根据索引值向左偏移对应位置
• 近似匹配实现：借助Application.Match方法，通过第三个参数控制模式——1代表近似匹配（要求查找列升序排列），0代表精确匹配
• 错误统一处理：捕获匹配失败的情况，返回#N/A，和Excel原生函数的错误提示逻辑保持一致

使用示例 #

假设A列是姓名，B列是分数，要通过分数反向查找对应姓名：

• 公式：=VLOOKUPLEFT(85, A1:B10, 1, TRUE)
• 说明：table_array为A1:B10，col_index_num=1表示取查找列（B列）左侧第一列（A列）的值，TRUE启用近似匹配

内容的提问来源于stack exchange，提问作者Andrea Torriglia