MATCH函数返回行号异常，求Office2016兼容的XLOOKUP VBA实现

核心排查点及修复方案： #

• 检查匹配模式参数：插值计算需确定x1所在区间（找到小于等于x1的最大x值作为下限，上限为下一行），此时必须使用MATCH(x1, x_range, 1)，前提是x_range必须按升序排列。若x_range为降序，需改为match_type=-1。参数错误是行号返回异常的最常见原因。
• 统一数据格式：若x1为数值型，但x_range中存在文本型数值，MATCH会匹配失败。选中相关单元格，将格式统一设置为「数值」；或用VALUE()转换，例如MATCH(VALUE(A1), VALUE(C:C), 1)（数组公式需按Ctrl+Shift+Enter确认）。
• 清理异常数据：x_range中的重复值会让MATCH返回第一个匹配项的行号，需确认是否符合插值逻辑；空单元格会被视为0，干扰匹配结果，需删除或填充有效数据。
• 验证引用范围：检查MATCH的第二个参数是否引用了正确的x数据列，避免误包含表头行或多余行导致行号偏移。

代码实现 #

打开Excel后按Alt+F11进入VBA编辑器，插入模块并粘贴以下代码：

Function XLOOKUP(lookup_value As Variant, lookup_array As Range, return_array As Range, _
                Optional if_not_found As Variant = "#N/A", Optional match_mode As Integer = 0, _
                Optional search_mode As Integer = 1) As Variant
    
    Dim arrLookup As Variant, arrReturn As Variant
    Dim i As Long, matchIndex As Long
    Dim found As Boolean
    
    ' 转换为数组提升运行效率
    arrLookup = lookup_array.Value
    arrReturn = return_array.Value
    
    ' 校验查找与返回区域的维度一致性
    If lookup_array.Rows.Count <> return_array.Rows.Count Or _
       lookup_array.Columns.Count <> return_array.Columns.Count Then
        XLOOKUP = "#REF!"
        Exit Function
    End If
    
    matchIndex = -1
    found = False
    
    ' 匹配模式分支处理
    Select Case match_mode
        Case 0 ' 精确匹配（默认）
            If search_mode = 1 Then ' 正向搜索
                For i = LBound(arrLookup, 1) To UBound(arrLookup, 1)
                    If arrLookup(i, 1) = lookup_value Then
                        matchIndex = i
                        found = True
                        Exit For
                    End If
                Next i
            ElseIf search_mode = -1 Then ' 反向搜索
                For i = UBound(arrLookup, 1) To LBound(arrLookup, 1) Step -1
                    If arrLookup(i, 1) = lookup_value Then
                        matchIndex = i
                        found = True
                        Exit For
                    End If
                Next i
            End If
        
        Case 1 ' 近似匹配（返回<=查找值的最大值，要求查找区域升序）
            If search_mode <> 1 Then
                XLOOKUP = "#VALUE!"
                Exit Function
            End If
            ' 简单校验升序
            If arrLookup(UBound(arrLookup, 1), 1) < arrLookup(LBound(arrLookup, 1), 1) Then
                XLOOKUP = "#VALUE!"
                Exit Function
            End If
            For i = LBound(arrLookup, 1) To UBound(arrLookup, 1)
                If arrLookup(i, 1) > lookup_value Then
                    matchIndex = i - 1
                    found = True
                    Exit For
                End If
            Next i
            ' 若所有值都<=查找值，返回最后一项
            If Not found Then
                matchIndex = UBound(arrLookup, 1)
                found = True
            End If
        
        Case -1 ' 近似匹配（返回>=查找值的最小值，要求查找区域升序）
            If search_mode <> 1 Then
                XLOOKUP = "#VALUE!"
                Exit Function
            End If
            If arrLookup(UBound(arrLookup, 1), 1) < arrLookup(LBound(arrLookup, 1), 1) Then
                XLOOKUP = "#VALUE!"
                Exit Function
            End If
            For i = LBound(arrLookup, 1) To UBound(arrLookup, 1)
                If arrLookup(i, 1) >= lookup_value Then
                    matchIndex = i
                    found = True
                    Exit For
                End If
            Next i
        
        Case 2 ' 通配符匹配
            If search_mode = 1 Then
                For i = LBound(arrLookup, 1) To UBound(arrLookup, 1)
                    If arrLookup(i, 1) Like lookup_value Then
                        matchIndex = i
                        found = True
                        Exit For
                    End If
                Next i
            ElseIf search_mode = -1 Then
                For i = UBound(arrLookup, 1) To LBound(arrLookup, 1) Step -1
                    If arrLookup(i, 1) Like lookup_value Then
                        matchIndex = i
                        found = True
                        Exit For
                    End If
                Next i
            End If
        Case Else
            XLOOKUP = "#VALUE!"
            Exit Function
    End Select
    
    ' 返回结果
    If found Then
        XLOOKUP = arrReturn(matchIndex, 1)
    Else
        XLOOKUP = if_not_found
    End If
    
End Function

使用说明 #

1. 保存工作簿为**Excel启用宏的工作簿（.xlsm）**格式，否则宏无法生效。
2. 单元格调用语法与原生XLOOKUP完全一致：

   =XLOOKUP(查找值, 查找区域, 返回区域, [未找到时返回值], [匹配模式], [搜索模式])
   

   • 匹配模式：0=精确匹配（默认），1=近似匹配（小于等于），-1=近似匹配（大于等于），2=通配符匹配
   • 搜索模式：1=正向搜索（默认），-1=反向搜索

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