Excel VBA Match: How It Works and How to Use It Safely

VBA & macros · Updated November 18, 2026

In Excel VBA, Match finds a value in a one-dimensional range and returns its position within that range—not the value itself. Call it as Application.Match(lookup_value, lookup_array, match_type); use 0 for an exact match, and check for an error result when the value might be missing.

What MATCH Returns in Excel VBA

Excel’s MATCH function is useful when a macro needs to locate an item in a list. If the lookup range is A2:A6 and the matching item is in A4, the result is 3: the item is third in the supplied range. It is not the worksheet row number, which is 4 in this example. Keeping that distinction in mind prevents a common off-by-one error when code translates a match position into a cell address.

MATCH only tells you where the match occurs. It does not return data from a neighboring column. A macro can use the returned position to read another range, select the matched cell, or calculate a worksheet row. This makes MATCH a useful building block for search and lookup routines, especially when the list’s position may change as rows are added or reordered.

In VBA, the usual call is Application.Match. It invokes the Excel worksheet function through the Application object. Its result is best stored in a Variant, because a successful search returns a number while an unsuccessful search returns an Excel error value.

Syntax and Match Types

The call takes three arguments: the item to find, the single-row or single-column range to search, and a number that defines the matching behavior. Specify the third argument rather than relying on a default; being explicit makes the search logic easier to read and reduces the risk of an unintended approximate match.

  • match_type = 0 searches for an exact match. The values do not need to be sorted.
  • match_type = 1 finds the largest value less than or equal to the lookup value. The lookup range must be sorted in ascending order.
  • match_type = -1 finds the smallest value greater than or equal to the lookup value. The lookup range must be sorted in descending order.

With match type 0, text matching is not case-sensitive: “north” and “North” are treated as equivalent for this purpose. Excel also supports wildcard matching in exact-match mode: an asterisk represents any sequence of characters, and a question mark represents one character. Use a tilde before a wildcard character when you need to search for the literal character rather than use it as a wildcard. For identifiers that contain those symbols, account for this behavior when designing a search.

Match type 1 is the default for the worksheet function when the match type is omitted, but a VBA call should include it. An omitted or mistaken match type can cause code to return a plausible-looking position from an unsorted list, which is more difficult to notice than a clear error.

Find an Exact Match with VBA

For a code or name list, exact matching is usually the right choice. This example searches A2:A100 on a worksheet named Inventory. It reports the worksheet row if the value is found, and handles the not-found result without stopping the macro.

Before running a macro, save a copy of the workbook. VBA macros cannot be undone with Ctrl+Z, so a backup is important if you adapt the code to change cells or other workbook content.

Sub FindProduct()
    Dim ws As Worksheet
    Dim result As Variant
    Dim worksheetRow As Long

    ' Search the product-code list on the Inventory sheet.
    Set ws = ThisWorkbook.Worksheets("Inventory")
    result = Application.Match("P-104", ws.Range("A2:A100"), 0)

    If IsError(result) Then
        MsgBox "Product code not found."
    Else
        ' Convert the position in A2:A100 to a worksheet row number.
        worksheetRow = ws.Range("A2").Row + CLng(result) - 1
        MsgBox "Product found on row " & worksheetRow
    End If
End Sub

If P-104 is in A2, MATCH returns 1 and the calculation gives row 2. If it is in A10, MATCH returns 9 and the calculation gives row 10. The starting cell matters: for a search range beginning at A5, add the match position to row 5 and subtract 1. If you only need to use a relative position in another range of the same size, you may not need to convert it to a worksheet row at all.

Approximate Matches and Sorted Lists

Approximate matching is useful for tasks such as assigning a rate or category based on thresholds. Suppose a column contains lower bounds of 0, 10, 25, and 50 in ascending order. Searching for 31 with match type 1 returns the position of 25, the largest listed lower bound that does not exceed 31. This pattern works only when the range is sorted as required; it is not a general “closest value” search.

For example, a workbook might map order quantities to shipping bands. A quantity of 31 could match the threshold 25 and therefore use that band. The code still needs to verify that the lookup result is not an error—for example, a value below the smallest threshold may have no qualifying match. Exact matches and approximate matches answer different questions, so choose the type based on the rule being modeled rather than on which option happens to return a result.

With match type -1, the range must instead be in descending order. This mode can be useful when the listed thresholds are arranged from largest to smallest and the desired result is the smallest listed value that is at least the lookup value. If the required sort order is uncertain, use an exact match or sort and validate the data before relying on an approximate result.

Handling Missing Values and Errors

When no match is found, Application.Match returns an error value. That result is different from the number zero, so a numeric test such as result = 0 is not a safe not-found check. Use VBA’s IsError function before converting the result to a number or using it as an index.

There is a related VBA option: WorksheetFunction.Match. It returns the match position when successful, but a not-found result raises a run-time error. That can be handled with VBA error handling, but it is often simpler for ordinary searches to use Application.Match and test the returned Variant with IsError. The two call styles are not interchangeable in how they expose a missing match.

After confirming that a result is not an error, convert it to a suitable numeric type if needed. For ordinary range positions, CLng is commonly used. Do not convert before checking: attempting to treat an error value as a number can trigger another run-time error and obscure the original reason the lookup failed.

Common Reasons MATCH Does Not Find a Value

A visually identical value may not be stored identically. One cell might contain the number 104 while another contains text “104”; leading or trailing spaces can also make text values differ. Imported data is a frequent source of such inconsistencies. Check the underlying cell contents and data types, and clean or standardize the source list when appropriate rather than assuming the displayed appearance guarantees a match.

  • Wrong range: Confirm that the search range contains the intended list and is one row or one column, not a two-dimensional block.
  • Wrong match type: Use 0 for exact matching; use 1 or -1 only when the range is sorted in the corresponding order.
  • Relative position confused with row number: Add the range’s starting row minus one to convert a vertical-list position to a worksheet row.
  • Extra spaces or inconsistent types: Compare source values and clean data where necessary.
  • Wildcard characters: In exact text searches, * and ? have wildcard meanings. Escape them with a tilde when the symbols themselves are the target.

Also check whether the lookup range includes a header. If the code searches A1:A100 but the list begins in A2, the position returned is relative to the larger range and will be offset from expectations. Decide deliberately whether headers, blank cells, or duplicate values belong in the search range. If a value appears more than once, MATCH returns the position of the first matching occurrence in the search direction.

Using the Position to Retrieve Related Data

A typical lookup has a key column and a data column. For example, column A might contain product codes and column B their descriptions. MATCH can identify the position of a product code in A2:A100; code can then use the same position in B2:B100 to retrieve the corresponding description. Both ranges must start on corresponding rows and have the same number of cells. If they do not align, the position can point to the wrong record even though MATCH itself worked correctly.

This approach is useful when the macro needs to perform additional actions after finding a record, such as updating a status, selecting a row, or checking several fields. Use the matched position as a relative index and keep the lookup and return ranges aligned. For a table that can grow, a structured Excel Table can make the workbook easier to maintain than fixed addresses, though the VBA code must then refer to the table and its columns deliberately.

When duplicate keys are possible, decide whether the first match is sufficient. MATCH does not return every matching row. If the task requires all occurrences, use a loop or another method that explicitly collects multiple matches instead of assuming a single MATCH result represents every record.

MATCH Compared with Other Lookup Options

MATCH is a good fit when the macro needs a position, when it must support older Excel environments, or when the rest of the VBA procedure will use that position for its own logic. Combined with INDEX, the position can retrieve a related value without hard-coding a return-column number. The separate steps can also be easier to adapt when the macro needs the position for more than one operation.

For many worksheet lookups, XLOOKUP is a more direct alternative because it can return a corresponding value without requiring a separate MATCH position. XLOOKUP is available in Microsoft 365 and Excel 2021 or later. In VBA, using a worksheet function is not the same as writing a worksheet formula, and availability depends on the Excel version that runs the macro. If a workbook must work in older Excel versions, MATCH with INDEX is often a more compatible pattern.

For a small list, a VBA loop may be easier to understand, especially if each candidate needs custom checks. For larger lists or repeated lookups, repeatedly scanning cells one by one can be slower than using an appropriate lookup approach. Choose based on the output needed: a position, a single return value, multiple matching records, or custom logic.

Running VBA Safely in Excel

VBA runs in the desktop Excel application, not in Excel for the web. Excel for the web can open and edit many macro-enabled workbooks, but it cannot create, run, or edit VBA macros. On Windows, use the Developer tab to open Visual Basic, or press Alt+F11 to open the Visual Basic Editor. If the Developer tab is not visible, enable it through File > Options > Customize Ribbon. On Mac, use Tools > Macro > Visual Basic Editor; keyboard shortcuts can differ by keyboard and Excel version.

Save a workbook containing VBA in a macro-enabled format such as .xlsm; saving as .xlsx removes VBA code. Only enable macros in workbooks from sources you trust. Macro security is a protection against code that can change workbook data or interact with the computer, not a setting to bypass casually. Keep a backup before running code, particularly if you later modify a search macro to edit or delete records. Ctrl+Z does not undo actions performed by VBA.

Frequently Asked Questions

Does MATCH return a row number?
MATCH returns a position relative to the lookup range. If the range starts at A2 and the result is 3, the matching cell is on worksheet row 4. Convert the relative position when your code needs an absolute row number.

How do I make MATCH use an exact match in VBA?
Pass 0 as the third argument, as in Application.Match(valueToFind, searchRange, 0). Then check the result with IsError, because a value that is not present produces an error result.

Why does MATCH return the wrong position?
Check the match type first. Approximate modes require a particular sort order and can return a valid-looking position from unsuitable data. Also confirm the search range’s starting cell, whether a header is included, and whether the values are text or numbers.

Is Application.Match better than WorksheetFunction.Match?
For a lookup where a missing value is an ordinary possibility, Application.Match is often convenient because it returns an error value that can be tested with IsError. WorksheetFunction.Match raises a VBA run-time error when no match is found, so it needs error handling.

Can MATCH search a row instead of a column?
Yes. The lookup array can be a single row or a single column. The returned position is relative to the order of cells in that one-dimensional range; it does not automatically become a worksheet column number or row number.

Related

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.