Excel VBA MATCH Error Handling: Avoid Run-Time Errors

For safer Excel VBA MATCH error handling, use Application.Match and test its result with IsError before using it. Unlike Application.WorksheetFunction.Match, which raises a run-time error when there is no match, Application.Match returns an error value that your code can handle directly.

Why MATCH can cause a VBA error

Excel’s MATCH function searches a one-dimensional range and returns the position of a matching item within that range. A missing item is not an unusual VBA failure: Excel represents that result as #N/A. The difference between the two common VBA call styles is how that result reaches your code.

Application.WorksheetFunction.Match behaves like a worksheet function called through the WorksheetFunction object. If MATCH returns an Excel error, VBA raises a run-time error, commonly error 1004 for a missing match.
Application.Match returns a Variant. If Excel cannot find the item, the Variant contains an error value, which you can detect using IsError.

That distinction matters because a missing lookup value is often an expected outcome—for example, a product code may not yet exist in a list. A macro should handle that case deliberately rather than stopping with a dialog. Use Application.Match when you want to branch on the result; reserve VBA error handling for exceptional conditions that your code cannot handle through normal checks.

How MATCH search modes affect the result

MATCH has three search modes, controlled by its third argument, often called match_type. For most code that checks whether a particular ID or name exists, use 0 for an exact match. Omitting the argument is not equivalent: Excel defaults to approximate matching with type 1.

0 searches for an exact match and does not require sorted data. For text, wildcard characters can be meaningful: * matches a sequence of characters and ? matches one character. Use a tilde to escape a wildcard when you need it treated literally.
1, or an omitted match type, finds the largest value less than or equal to the lookup value. The lookup range must be sorted in ascending order for the result to be reliable.
-1 finds the smallest value greater than or equal to the lookup value. The lookup range must be sorted in descending order.

Approximate matching is useful for threshold tables, such as bands or rate brackets, but can produce a plausible-looking wrong answer if the range is unsorted. State the third argument explicitly so the macro’s intent is clear. MATCH is also case-insensitive for text, so it does not distinguish between “abc” and “ABC.”

Use Application.Match and check the Variant

In the following example, assume the worksheet named Inventory has product codes in A2:A100, the code to find in E2, and a result cell in F2. The macro searches for an exact match. MATCH returns a position relative to A2:A100, so the code adds the range’s starting row minus one to write the corresponding worksheet row number.

Sub FindProductRow()
    Dim ws As Worksheet
    Dim lookupRange As Range
    Dim lookupValue As Variant
    Dim matchResult As Variant

    Set ws = ThisWorkbook.Worksheets("Inventory")
    Set lookupRange = ws.Range("A2:A100")
    lookupValue = ws.Range("E2").Value

    ' Use exact match; the result may be a position or an error value.
    matchResult = Application.Match(lookupValue, lookupRange, 0)

    If IsError(matchResult) Then
        ws.Range("F2").Value = "No exact match found"
    Else
        ws.Range("F2").Value = CLng(matchResult) + lookupRange.Row - 1
    End If
End Sub

For example, if the matching code is the third item in A2:A100, MATCH returns 3 and the macro writes 4, the worksheet row containing that item. If the code is absent, the macro writes the message instead of stopping. The result is declared as a Variant because a successful match is numeric while an unsuccessful one is an Excel error value. Declaring it as a Long would prevent the code from safely holding both kinds of result.

Save a copy of the workbook before running a macro. VBA changes cannot be undone with Ctrl+Z, even when the macro appears to make only a small edit.

Understand positions, rows, and columns

A frequent source of “wrong result” reports is confusing MATCH’s position with a worksheet row number. Given a lookup range of A2:A100, a result of 1 means the first item in that range—cell A2—not worksheet row 1. For a range beginning at row 2, convert a successful position to a row with CLng(matchResult) + lookupRange.Row - 1. For a range beginning at row 10, the same expression correctly adds 9.

If the range is horizontal, MATCH still returns a position, now counted from the range’s first column. For example, the fourth item in D5:H5 is in column G. The result is not a column letter or absolute column number. Convert it only after checking IsError; attempting arithmetic on an error Variant can raise another VBA error.

Keep the lookup range to one row or one column. MATCH is designed to search a one-dimensional lookup array, not a two-dimensional block. If you need to search a table and return a value from another column, MATCH can provide the position used with INDEX, or you can use a lookup function suited to that task.

Common causes and practical checks

When the result is an error value, first establish whether the item genuinely is absent. These checks can uncover less obvious causes:

Spaces or inconsistent text: “P-104” and “P-104 ” are different strings. Imported data can contain leading or trailing spaces or nonbreaking spaces that ordinary trimming may not remove.
Numbers stored as text: A numeric-looking code stored as text may not compare as expected with a numeric lookup value. Check the values’ types and standardize the source rather than blindly converting identifiers; leading zeros may be significant.
Wrong range: Confirm that the range covers the intended data and is on the expected worksheet. Qualify ranges with a worksheet object, as in ws.Range("A2:A100"), instead of relying on whichever sheet happens to be active.
Wrong match type: An omitted third argument selects approximate mode, which can return an incorrect nearby position or behave unexpectedly with unsorted data. Supply 0 for exact lookup.
Errors in the source data: Cells containing Excel error values can complicate a search. Inspect the lookup value and array if IsError is true but the item seems to be present.

Do not interpret every error result as proof that the value is absent if the range or input might itself be invalid. A useful message can say “No match or invalid lookup data,” or the macro can validate the range and input separately before searching.

Why not just use On Error Resume Next?

On Error Resume Next tells VBA to continue after a run-time error. It is sometimes used around WorksheetFunction.Match, but it can conceal unrelated problems if left active too long. A bad object reference, a misspelled worksheet name, or another programming mistake could be mistaken for an ordinary missing item.

With Application.Match, you generally do not need an error handler for the expected “not found” result: test the returned value with IsError. If you must call WorksheetFunction.Match, use a narrowly scoped handler, inspect Err.Number, and restore normal handling immediately with On Error GoTo 0. Do not label every caught error “not found” without checking the cause. For maintainable lookup code, the Variant-and-IsError pattern is usually simpler.

Choosing between MATCH and other lookup methods

MATCH is a good fit when the macro needs a position—for example, to identify which row in a range contains an ID or to pair the position with another operation. If the task is simply to retrieve a value from a related column, an index-and-match approach or a dedicated lookup function may express the goal more clearly.

In Microsoft 365 for Windows, XLOOKUP is available and can return a chosen value directly, with an optional value for the not-found case. XMATCH is a newer position-finding alternative with additional search options. These functions are available in Microsoft 365 and Excel 2021, but workbooks shared with older Excel versions may not calculate them there. MATCH remains useful for compatibility and for code that specifically needs a relative position. Regardless of function choice, decide how the macro should respond when there is no result instead of allowing an unexpected error to stop it.

Frequently Asked Questions

What error does WorksheetFunction.Match give when nothing is found?
Calling Application.WorksheetFunction.Match when no item matches commonly raises VBA run-time error 1004. Calling Application.Match instead lets you receive an error Variant and test it with IsError.

How do I test whether Application.Match found a value?
Store the return value in a Variant, then use If IsError(matchResult) Then to handle an unsuccessful search. Use the numeric position only in the other branch, after that check.

Why does MATCH return 1 instead of the worksheet row?
MATCH returns a position relative to the lookup range. If the range starts at A2, position 1 refers to A2. Add the range’s starting row offset to convert a successful position into an absolute row number.

Does MATCH default to exact matching?
No. If the third argument is omitted, MATCH defaults to approximate mode with type 1. For an exact lookup, pass 0 explicitly and use a one-dimensional range.

Is MATCH case-sensitive?
No. MATCH does not distinguish uppercase from lowercase text. If your requirement is case-sensitive matching, use a different comparison approach, such as looping through the cells and comparing strings with VBA’s StrComp using vbBinaryCompare.

Can I use MATCH to return the value from another column?
MATCH returns a position, not the related value. Use that position with another lookup method, or use XLOOKUP in a supported Excel version when you want to return a value directly.

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.