如何在VBA中实现数组式LOOKUP获取最后匹配结果并赋值给变量x

原Excel数组公式功能 #

用于获取符合条件的最后匹配结果的数组公式：

{=IFERROR(IF(Sheet1!T1="approved", LOOKUP(2,1/(Sheet2!G9:G16=Sheet1!B1),Sheet2!H9:H16),""),"")}

逻辑说明：当Sheet1的T1单元格值为approved时，在Sheet2的G9:G16区域中匹配与Sheet1的B1相同的最后一行，返回对应H9:H16区域的值；不满足条件或匹配出错时返回空值。

你尝试的VBA代码问题 #

你写的代码存在几个关键问题：

• 使用了VBA关键字Name作为变量名，会导致语法错误
• 未还原原公式的IF判断和IFERROR错误处理逻辑
• 直接用WorksheetFunction.Lookup无法处理1/(Range("B1:B3")=Name)这类数组运算
• 变量x未赋值，却在MsgBox中调用

修正后的VBA代码 #

Sub GetLastMatch()
    Dim x As Variant
    Dim isApproved As Boolean
    Dim matchRange As Range
    Dim resultRange As Range
    Dim matchCriteria As String
    
    ' 读取原公式中的条件和区域
    matchCriteria = Sheet1.Range("B1").Value
    isApproved = (Sheet1.Range("T1").Value = "approved")
    
    Set matchRange = Sheet2.Range("G9:G16")
    Set resultRange = Sheet2.Range("H9:H16")
    
    If isApproved Then
        ' 用Evaluate执行数组运算，模拟原LOOKUP的逻辑
        x = Application.Evaluate("LOOKUP(2,1/(" & matchRange.Address(External:=True) & "=""" & matchCriteria & """)," & resultRange.Address(External:=True) & ")")
        ' 处理错误，对应原公式的IFERROR
        If IsError(x) Then x = ""
    Else
        x = ""
    End If
    
    MsgBox x
End Sub

关键说明 #

• 替换VBA关键字变量名，改用matchCriteria这类语义清晰的名称
• 用Application.Evaluate执行数组运算：WorksheetFunction.Lookup不支持直接传入数组运算结果，Evaluate可以完整还原原数组公式的逻辑
• 还原原公式的条件判断和错误处理：通过If isApproved Then实现原IF逻辑，用IsError处理匹配出错的情况
• 使用Address(External:=True)确保跨工作表的区域地址能被正确识别

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