[Excel VBA] How to Search for Exact Match Values Using the MATCH Function

When you want to search for a specific value in Excel table data, the MATCH function allows you to retrieve the relative row number of the corresponding cell.

In this article, I will introduce how to use the MATCH function from VBA to search for cells that completely match a specific string and branch processing based on the result.

Intended Use Cases

• Retrieving the row number by searching for unique information such as a name or ID.
• Extracting only information that matches exactly from a dataset.
• Jumping to the target cell based on the search result.

VBA Code Example

The following code searches for the string “Forest” (森) within the cell range B1:B11 for an exact match and executes processing based on the position found.

Sub FindExactMatchRow()

    Dim resultRow As Long
    resultRow = 0

    ' Match throws an error if not found, so we temporarily ignore errors
    On Error Resume Next
    resultRow = WorksheetFunction.Match("Forest", Range("B1:B11"), 0)
    On Error GoTo 0

    If resultRow = 0 Then
        MsgBox "No matching data was found.", vbExclamation
    Else
        MsgBox "The match is located at row " & resultRow & " within the range.", vbInformation
        ' Select the corresponding cell based on column B (Column index 2)
        Cells(resultRow, 2).Select 
    End If

End Sub

Explanation of Key Syntax Points

Notes and Supplementary Information

1. Relative vs. Absolute: The MATCH function returns the relative position (order) within the search range, not necessarily the absolute row number of the worksheet. (In the example above, since the range starts at B1, the relative position equals the worksheet row number).
2. Case Insensitivity: Differences between uppercase and lowercase letters are generally ignored.
3. Error Handling: Since the function throws a runtime error if the data does not exist, it is safe to include On Error statements to handle cases where the value is missing.

Summary

By using the MATCH function in VBA, you can succinctly implement a process to search for specific strings in Excel data using an exact match and retrieve their position.

Once the target cell is found, you can freely perform operations such as selecting, editing, or deleting it. This can be utilized for automatic data processing and user accessibility features. Please try incorporating this method to streamline search processes in your daily work.