Excel VBA双条件精确匹配IndexMatch问题：未匹配时返回"CriteriasNotMet"

Hey there! Let's get that VBA code sorted so it behaves properly when your lookup conditions don't find a match. The problem with your original code is that using WorksheetFunction.Index and WorksheetFunction.Match throws a runtime error when no matching result exists—unlike the regular worksheet functions, which just return #N/A. Here are two reliable ways to fix this:

Method 1: Use Error Handling with On Error Resume Next #

This approach catches the error that pops up when no match is found, then sets cell H2 to your desired message. Here's the revised code:

Sub IndexMatch()
    Dim myName As Variant
    Dim mySubject As Variant
    Dim mark As Variant
    
    ' Grab the values from your condition cells
    myName = [F2].Value
    mySubject = [G2].Value
    
    ' Turn on error handling to skip over runtime errors
    On Error Resume Next
    ' Attempt the Index-Match lookup
    mark = Application.WorksheetFunction.Index([StMark], _
        Application.WorksheetFunction.Match(myName & mySubject, [StName] & [StSubject], 0))
    
    ' Check if an error occurred (meaning no match was found)
    If Err.Number <> 0 Then
        [H2].Value = "CriteriasNotMet"
    Else
        [H2].Value = mark
    End If
    
    ' Reset error handling to default (always a good practice!)
    On Error GoTo 0
End Sub

Key Notes: #

• On Error Resume Next tells VBA to keep running even if it hits an error (like no match found).
• Err.Number <> 0 checks if the lookup failed.
• Don't forget On Error GoTo 0 at the end—it turns off the custom error handling so future errors in your code will behave normally.

Method 2: Use Application.Index/Application.Match (No WorksheetFunction) #

When you use Application instead of WorksheetFunction, the methods return an error value (like #N/A) instead of throwing a runtime error. You can then check for this error with IsError():

Sub IndexMatch()
    Dim myName As Variant
    Dim mySubject As Variant
    Dim mark As Variant
    
    myName = [F2].Value
    mySubject = [G2].Value
    
    ' Use Application.Match—returns an error value if no match exists
    mark = Application.Index([StMark], _
        Application.Match(myName & mySubject, [StName] & [StSubject], 0))
    
    ' Check if the result is an error
    If IsError(mark) Then
        [H2].Value = "CriteriasNotMet"
    Else
        [H2].Value = mark
    End If
End Sub

Key Notes: #

• Ditching the WorksheetFunction prefix makes the lookup behave more like the worksheet version of Index/Match.
• IsError(mark) is a clean way to detect when no match was found.
• This method avoids error handling boilerplate, making the code shorter and easier to read.

Quick Extra Tips #

• Instead of using shorthand like [F2], consider using explicit references like ThisWorkbook.Sheets("YourSheetName").Range("F2")—this prevents issues if the active sheet changes while the code runs.
• Double-check that your named ranges StName, StSubject, and StMark cover the correct data range (no extra empty rows or invalid data).

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