Access VBA Match Function: What It Does and How to Use It

Microsoft Access does not provide Excel’s worksheet MATCH function as a native VBA function. To find a value in Access data, use DAO methods such as Recordset.FindFirst or a query; use Excel’s MATCH only when your Access code is automating Excel and searching an Excel range.

What “Match” Means in Access VBA

The phrase “Access VBA Match function” can refer to two different things: finding a record in an Access table, or calling the Excel worksheet function named MATCH. The distinction matters because Access and Excel have separate object models. An Access VBA module does not automatically gain Excel’s worksheet functions just because both applications use VBA.

In an Excel worksheet, MATCH searches a one-dimensional range and returns the item’s relative position. It does not return the matching cell’s value. Access has no built-in VBA function with the same name and behavior. Instead, Access offers database-oriented tools that search records, evaluate criteria, or retrieve field values.

There are also VBA functions with related-sounding names, but they solve different problems. StrComp compares two strings, and InStr finds one string inside another. Neither searches an Access table or returns an Excel-style position in a range.

How Excel MATCH Works

Excel’s MATCH function takes a value to look for, a single row or column to search, and an optional match type. It returns a position counted from the start of that search range. For example, if the match is the third item in A2:A10, the result is 3, not the worksheet row number 4.

0 requests an exact match. The range does not need to be sorted. If the value occurs more than once, the first matching position is returned.
1, or an omitted match type, requests an approximate match and assumes the values are sorted in ascending order. It returns the position of the largest value that is less than or equal to the lookup value.
-1 requests an approximate match with values sorted in descending order. It returns the position of the smallest value that is greater than or equal to the lookup value.

For ordinary code or name lookups, exact matching is usually the appropriate choice. A missing exact match causes Excel’s worksheet function to return an error value. Approximate matching can produce a plausible-looking but incorrect position if the range is not sorted as required, so do not use it as a shortcut for exact searching.

Calling Excel MATCH from Access

Access can call Excel through Automation if Excel is installed on the computer. The example below uses late binding, so it does not require adding an Excel object-library reference in the Access VBA editor. It opens a workbook read-only, searches a single column with an exact match, and treats Excel’s not-found result as an error value.

Before running a macro, save a copy of the Access file and any workbook it may change. Macros cannot be undone with Ctrl+Z. This example opens the workbook read-only, but the backup reminder is still good practice when adapting code that works with files.

Public Sub FindValueInExcel()
    Dim xlApp As Object
    Dim xlBook As Object
    Dim lookupRange As Object
    Dim lookupValue As String
    Dim matchResult As Variant
    Dim position As Long

    lookupValue = "P-104"
    On Error GoTo Failed

    Set xlApp = CreateObject("Excel.Application")
    xlApp.Visible = False
    Set xlBook = xlApp.Workbooks.Open("C:\Data\Lookup.xlsx", 0, True)
    Set lookupRange = xlBook.Worksheets("Sheet1").Range("A2:A100")

    ' Match type 0 asks Excel for an exact match.
    matchResult = xlApp.Match(lookupValue, lookupRange, 0)

    If IsError(matchResult) Then
        Debug.Print "No exact match was found."
    Else
        position = CLng(matchResult)
        Debug.Print "Matched value: " & lookupRange.Cells(position, 1).Value
    End If

CleanUp:
    On Error Resume Next
    If Not xlBook Is Nothing Then xlBook.Close False
    If Not xlApp Is Nothing Then xlApp.Quit
    Set lookupRange = Nothing
    Set xlBook = Nothing
    Set xlApp = Nothing
    Exit Sub

Failed:
    Debug.Print "Excel lookup failed: " & Err.Description
    Resume CleanUp
End Sub

Change the file path, sheet name, range, and lookup value to fit your workbook. A returned position of 1 refers to the first cell in the supplied range, which is A2 here. The code uses xlApp.Match, Excel’s application-level function, so it can inspect the result with IsError. If the file cannot be opened, Excel is unavailable, or the sheet name is wrong, the error handler reports the failure and closes the Excel objects where possible.

For a project that uses early binding instead, set a reference to the appropriate Microsoft Excel Object Library under the VBA editor’s Tools > References menu. Early binding enables Excel-specific constants and type checking, but a project that must run on computers with different Office versions may be easier to distribute with late binding.

Searching Access Tables with DAO

If the data is in an Access table or query, use Access’s database tools rather than opening Excel merely to search it. DAO’s Recordset.FindFirst searches the records in an open recordset using a criteria expression. The example assumes a table named Products with text field ItemCode and identifier field ProductID.

Save a copy of the database before running a macro that you are adapting, particularly if you later change the code to update or delete records. The example only reads data.

Public Sub FindProductByCode()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim lookupCode As String

    lookupCode = "P-104"
    Set db = CurrentDb
    Set rs = db.OpenRecordset( _
        "SELECT ProductID, ItemCode FROM Products", dbOpenSnapshot)

    ' Escape apostrophes because the criterion is a text expression.
    rs.FindFirst "[ItemCode] = '" & Replace(lookupCode, "'", "''") & "'"

    If rs.NoMatch Then
        Debug.Print "No product was found."
    Else
        Debug.Print "Product ID: " & rs!ProductID
    End If

    rs.Close
    Set rs = Nothing
    Set db = Nothing
End Sub

FindFirst positions the recordset on the first record satisfying the criterion. Check NoMatch before reading a field; otherwise, the recordset is not positioned on a valid match. The example doubles apostrophes in the search text so a code containing an apostrophe does not break the criteria expression. Field names and table names must match your database.

If duplicates are possible, “first” means the first record encountered in the recordset, not necessarily the record with the lowest identifier or earliest date. To control which duplicate comes first, open a query that sorts the records in the order you want before calling FindFirst. For more complex searches, use a parameterized query rather than assembling a long criteria string.

Choosing FindFirst, DLookup, or a Query

The right option depends on what the code needs back and how much data it must search. These tools are not interchangeable with Excel’s position-returning function, but each can handle a common Access lookup.

Use Recordset.FindFirst when you already have a recordset and want to move to a matching record, then read several fields from it.
Use DLookup when you need one field value from a table or query and the lookup is simple. It returns a value, not a recordset position, and returns Null if it finds no matching record.
Use a query when the lookup is part of a report, form, join, or multi-record operation. Queries make criteria and relationships visible and are often easier to maintain than repeated VBA lookups.
Use Excel MATCH through Automation only when the target data genuinely resides in an Excel workbook or the required result is specifically an Excel range position.

Repeatedly calling DLookup inside a loop can be inefficient because each call performs another lookup. For many records, open a suitable recordset or build a query that returns the needed rows together. If all you need is to test whether a record exists, a query or recordset check is clearer than attempting to reproduce Excel’s positional result.

Common Matching Problems

A common mistake is confusing a position with a row number. Excel’s MATCH result is relative to the range. If the search range begins at row 2 and the result is 5, the match is in the fifth cell of that range, or row 6 on the worksheet. Add the range’s starting row offset only if your code actually needs a worksheet row number.

Another frequent problem is using approximate matching without sorting the Excel range correctly. Match type 1 expects ascending order, while -1 expects descending order. For an exact lookup, explicitly use 0 so the intended behavior is obvious and does not depend on a default.

In Access, criteria syntax depends on the field’s data type. Text criteria need quoted values, while numeric criteria normally do not. Dates have their own Access expression syntax, and Null is not the same as an empty string. When using text criteria, escape apostrophes as shown in the DAO example. For untrusted or complicated input, a parameterized query is safer and less error-prone than concatenating values into SQL or a criteria expression.

Finally, string matching rules may differ according to the database’s comparison settings, and Excel’s exact text matching is not generally case-sensitive. If capitalization itself must distinguish values, make that requirement explicit and use a comparison method designed for it. Do not assume that the word “exact” means case-sensitive in every Access or Excel search.

Frequently Asked Questions

Does Access VBA have a built-in Match function?

No. Access VBA does not include Excel’s worksheet MATCH function as a native function. Use DAO or a query to search Access data, or automate Excel when you need to search a range in a workbook.

Can I use Application.Match in Access?

Not as an Access-native worksheet function. You can create an Excel application object and call its Match method, as in the example, but that requires Excel Automation and Excel installed on the machine.

What is the Access equivalent of Excel MATCH?

There is no single exact equivalent because Excel returns a range-relative position. DAO’s Recordset.FindFirst is a practical choice when you need to locate a record; a query or DLookup may be better when you need returned data.

How do I check whether Excel MATCH found a value?

When calling Excel’s application-level Match method, store the result in a Variant and use VBA’s IsError function to check for a not-found result. With WorksheetFunction.Match, a missing match instead raises a VBA runtime error, so error handling is needed.

Does MATCH return the matching value?

No. Excel’s MATCH returns the value’s position within the search range. Use that position with an appropriate lookup or range reference to retrieve a value, or use Access recordset and query methods when working with Access records.

Bottom Line

For Access tables, choose Access-native tools: FindFirst to move through a recordset, DLookup for a simple single-value lookup, or a query for relational and multi-record work. Excel’s MATCH is available to Access VBA only through Excel Automation; it is not a built-in Access function, and its result is a position relative to an Excel range.

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.