Application.WorksheetFunction.Match method

Mastering the Application.WorksheetFunction.Match Method in VBA

Unlock the power of Excel's MATCH function directly in your VBA code to efficiently locate items within ranges and retrieve their relative positions.

The Application.WorksheetFunction.Match method in VBA provides a powerful way to replicate the functionality of Excel's MATCH worksheet function directly within your macros. This method is invaluable for finding the relative position of an item in a range of cells, which can then be used with other functions like INDEX to retrieve corresponding values. Understanding its parameters and behavior is crucial for writing efficient and robust VBA solutions.

Understanding the Match Method Syntax

The Match method in VBA mirrors the Excel worksheet function, taking three primary arguments: Arg1 (Lookup_value), Arg2 (Lookup_array), and Arg3 (Match_type). It returns a Variant representing the relative position of the Lookup_value within the Lookup_array.

Application.WorksheetFunction.Match(Arg1, Arg2, [Arg3])

Syntax for the Application.WorksheetFunction.Match method

Let's break down each argument:

Practical Examples of Using Match

Let's explore some common scenarios where Application.WorksheetFunction.Match proves incredibly useful.

flowchart TD
    A["Start VBA Subroutine"]
    B["Define Lookup Value"]
    C["Define Lookup Range"]
    D{"Match Type = 0 (Exact Match)?"}
    E["Call Application.WorksheetFunction.Match"]
    F["Handle Match Result (Position)"]
    G["Handle No Match (Error)"]
    H["End Subroutine"]

    A --> B
    B --> C
    C --> D
    D -- Yes --> E
    D -- No --> E
    E --> F
    E -- Error --> G
    F --> H
    G --> H

Flowchart illustrating the process of using the Match method in VBA

Sub FindExactMatch()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    Dim lookupValue As String
    lookupValue = "Apple"
    
    Dim lookupRange As Range
    Set lookupRange = ws.Range("A1:A10") ' Assuming data in column A
    
    Dim matchPosition As Variant
    
    On Error GoTo NoMatchFound
    
    ' Find exact match (Match_type = 0)
    matchPosition = Application.WorksheetFunction.Match(lookupValue, lookupRange, 0)
    
    MsgBox "'" & lookupValue & "' found at relative position: " & matchPosition & " in range " & lookupRange.Address
    Exit Sub
    
NoMatchFound:
    MsgBox "'" & lookupValue & "' not found in range " & lookupRange.Address, vbCritical
End Sub

Example: Finding an exact match for a text value

In this example, if 'Apple' is in cell A5, matchPosition will be 5. If 'Apple' is not found, the On Error GoTo NoMatchFound statement will catch the error, and a message box will inform the user.

Sub FindApproximateMatch()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    Dim lookupValue As Long
    lookupValue = 75
    
    Dim lookupRange As Range
    Set lookupRange = ws.Range("B1:B10") ' Assuming sorted numbers in column B
    
    Dim matchPosition As Variant
    
    ' Ensure lookupRange is sorted ascending for Match_type = 1
    ' Example data in B1:B10: 10, 20, 30, 40, 50, 60, 70, 80, 90, 100
    
    On Error GoTo NoMatchFound
    
    ' Find largest value less than or equal to (Match_type = 1)
    matchPosition = Application.WorksheetFunction.Match(lookupValue, lookupRange, 1)
    
    MsgBox "For " & lookupValue & ", largest value <= found at relative position: " & matchPosition & " (Value: " & lookupRange.Cells(matchPosition).Value & ")"
    Exit Sub
    
NoMatchFound:
    MsgBox "No suitable match found for " & lookupValue, vbCritical
End Sub

Example: Finding an approximate match for a numeric value (Match_type = 1)

If lookupValue is 75 and lookupRange contains 10, 20, ..., 70, 80, ..., matchPosition will be 7 (corresponding to the value 70).

Combining Match with Index for Powerful Lookups

The true power of Match often comes when it's combined with Application.WorksheetFunction.Index. This combination allows you to perform two-way lookups, similar to VLOOKUP or HLOOKUP, but with much greater flexibility, as Match can find items in any column or row, not just the first.

Sub IndexMatchExample()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    Dim lookupProduct As String
    lookupProduct = "Laptop"
    
    Dim lookupHeader As String
    lookupHeader = "Price"
    
    Dim dataRange As Range
    Set dataRange = ws.Range("A1:C10") ' Assuming headers in A1:C1, data in A2:C10
    
    Dim productColumn As Range
    Set productColumn = dataRange.Columns(1) ' Column A contains product names
    
    Dim headerRow As Range
    Set headerRow = dataRange.Rows(1) ' Row 1 contains headers
    
    Dim productRow As Variant
    Dim headerCol As Variant
    Dim result As Variant
    
    On Error GoTo ErrorHandler
    
    ' Find the row number of the product
    productRow = Application.WorksheetFunction.Match(lookupProduct, productColumn, 0)
    
    ' Find the column number of the header
    headerCol = Application.WorksheetFunction.Match(lookupHeader, headerRow, 0)
    
    ' Use Index to retrieve the value at the intersection
    ' Note: Index expects the full data range, and Match returns relative positions within that range
    result = Application.WorksheetFunction.Index(dataRange, productRow, headerCol)
    
    MsgBox "The " & lookupHeader & " of " & lookupProduct & " is: " & result
    Exit Sub
    
ErrorHandler:
    MsgBox "Error: Could not find product or header. Please check values.", vbCritical
End Sub

Example: Using Index and Match for a two-way lookup

Handling Errors and Best Practices

As mentioned, Match will throw an error if no match is found. There are several ways to handle this in VBA.

Sub RobustMatch()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    Dim lookupValue As String
    lookupValue = "NonExistentItem"
    
    Dim lookupRange As Range
    Set lookupRange = ws.Range("A1:A10")
    
    Dim matchPosition As Variant
    
    ' Method 1: Using On Error GoTo
    On Error Resume Next ' Temporarily disable error handling
    matchPosition = Application.WorksheetFunction.Match(lookupValue, lookupRange, 0)
    On Error GoTo 0 ' Re-enable default error handling
    
    If IsError(matchPosition) Then
        MsgBox "Item not found using WorksheetFunction.Match.", vbExclamation
    Else
        MsgBox "Item found at position: " & matchPosition
    End If
    
    ' Method 2: Using Application.Match (without WorksheetFunction)
    ' This method returns an error value directly, which can be checked with IsError
    matchPosition = Application.Match(lookupValue, lookupRange, 0)
    
    If IsError(matchPosition) Then
        MsgBox "Item not found using Application.Match.", vbExclamation
    Else
        MsgBox "Item found at position: " & matchPosition
    End If
End Sub

Robust error handling for the Match method

The Application.Match method (without WorksheetFunction) is often preferred for its cleaner error handling. When a match is not found, Application.Match returns an error value (specifically, Error 2042, which is equivalent to #N/A in Excel), which can be checked using the IsError function, avoiding the need for On Error GoTo statements for this specific scenario.

1. Define Your Lookup Criteria

Clearly identify the Lookup_value you need to find and the Lookup_array (the range) where you expect to find it. Ensure the Lookup_array is a single row or column.

2. Choose the Correct Match Type

Decide if you need an exact match (0) or an approximate match (1 or -1). If using approximate match, verify that your Lookup_array is sorted correctly (ascending for 1, descending for -1).

3. Implement Error Handling

Always wrap your Match call with error handling. Use On Error GoTo for Application.WorksheetFunction.Match or check IsError for Application.Match to gracefully handle cases where no match is found.

4. Utilize the Result

The returned position can be used directly or passed to other functions like Application.WorksheetFunction.Index to retrieve associated data, enabling powerful lookup capabilities.