Excel VBA: Find a Match and Return Its Row Number

In Excel VBA, use the column’s Range.Find method to locate a value, then read the found cell’s .Row property. For example, if you search column A for “M-204” and find it in cell A4, foundCell.Row returns 4—the worksheet row number.

How Find returns a worksheet row

Find searches a range and returns a Range object for the first matching cell it encounters. The object represents the cell itself, so its Row property gives the cell’s row on the worksheet. This is different from the match’s position within the range: A4 is in position 3 of A2:A100, but its worksheet row is 4.

That distinction matters when the search range does not start in row 1. If you need a row number to pass to another VBA operation—such as reading a value from another column, updating a record, or selecting the matching row—use the absolute worksheet row returned by .Row. Do not assume the search position and worksheet row are interchangeable.

A basic VBA example

This macro looks for the exact value “M-204” in A2:A100 on a worksheet named Data. Change the worksheet name, search range, and lookup value to suit your workbook.

Sub FindMatchAndGetRow()
    Dim ws As Worksheet
    Dim searchRange As Range
    Dim foundCell As Range
    Dim lookupValue As String

    Set ws = ThisWorkbook.Worksheets("Data")
    Set searchRange = ws.Range("A2:A100")
    lookupValue = "M-204"

    ' Search values and require the entire cell to match.
    Set foundCell = searchRange.Find( _
        What:=lookupValue, _
        After:=searchRange.Cells(searchRange.Cells.Count), _
        LookIn:=xlValues, _
        LookAt:=xlWhole, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, _
        MatchCase:=False, _
        SearchFormat:=False)

    If Not foundCell Is Nothing Then
        MsgBox "Match found on worksheet row " & foundCell.Row
    Else
        MsgBox "No match found."
    End If
End Sub

With the example range A2:A100, a match in A4 produces the message “Match found on worksheet row 4.” If the value is absent, Find returns Nothing, and the macro displays the not-found message. Checking for Nothing is essential: trying to read foundCell.Row when no cell was found causes a VBA error.

Why the Find arguments matter

Excel’s Find dialog and VBA’s Find method share some search settings. Excel can retain settings from earlier searches, so leaving arguments out may make a macro behave differently depending on what someone searched for previously. Supplying the important arguments makes the search more predictable.

What is the value or text to search for.
LookIn:=xlValues searches cell values, including the displayed results of formulas. Use xlFormulas when you need to search formula text instead.
LookAt:=xlWhole requires the entire cell content to match. Use xlPart to find a value contained within a longer cell entry.
SearchOrder:=xlByRows and SearchDirection:=xlNext define the search order and direction.
MatchCase:=False makes the text search case-insensitive. Set it to True when letter case must match.
SearchFormat:=False avoids applying a saved formatting criterion to the search.

The After argument in the example starts the search after the last cell in the range. Excel then wraps around to the beginning, so the top of the range is searched first. If you change the starting cell or search direction, the first match encountered may change.

Duplicates and the first match

Find returns one cell, not every matching row. When a column contains duplicates, the result is the first match encountered in the chosen search order. For example, if “M-204” appears in A4 and A20, the example returns row 4 because the search starts after the last cell and proceeds forward through the range.

If the task requires every matching row, repeat the search with FindNext until it cycles back to the first address. Save that first address before the loop; otherwise, a search that wraps around can keep running. For a one-off lookup where duplicates are possible, decide whether the first match is acceptable or whether the macro should report that more than one match exists.

Duplicates can also be meaningful rather than erroneous. A product code might appear on several transaction rows, for instance. In that case, returning just one row may be insufficient: the macro may need to total all matching records, collect their row numbers, or process each matching row in turn.

Searching a whole column or a defined range

Searching a defined range such as A2:A100 is often clearer and faster than searching an entire column. It also avoids finding a heading in row 1 and gives you control over whether blank or unrelated cells are included. Adjust the last row when the data grows, or determine the last populated row in VBA before creating the search range.

A whole-column search can be appropriate when the column’s size varies and the lookup value could appear anywhere in it. In that case, set the range explicitly, such as ws.Columns("A"). Remember that .Row still returns the actual worksheet row. If the match is in A500, the returned value is 500 regardless of whether you searched A2:A1000 or all of column A.

Use a worksheet variable and qualify the range with that worksheet, as the example does. An unqualified Range("A:A") refers to the active sheet, which might not be the sheet your macro intends to search. Explicit references help prevent a macro from quietly searching the wrong data when the user changes sheets.

Exact matching and common data issues

For identifiers, codes, and names, LookAt:=xlWhole is usually the safer choice. With xlPart, searching for “204” could match a cell containing “M-204” or “1204.” Partial matching is useful for deliberate substring searches, but it can return a row that is not an exact record match.

Values that look identical may be stored differently. A number stored as text, a number stored as a numeric value, leading or trailing spaces, and nonbreaking spaces can affect whether a search finds the expected cell. If a lookup fails unexpectedly, inspect the cell’s underlying value and data type, and check for extra spaces. Standardizing the source data before searching is often more reliable than trying to compensate for inconsistent entries inside the search.

Wildcards are another consideration. In Excel searches, an asterisk can represent a sequence of characters and a question mark can represent one character. If the lookup value itself contains wildcard characters and you need to search for them literally, escape them with a tilde in the search text. This is especially relevant for codes or text that can contain a literal asterisk or question mark.

Using Match instead of Find

VBA’s Find is a good fit when you want the matching cell and its properties, such as its address or row. Excel’s worksheet MATCH function is an alternative when you only need the match’s position within a range. In a worksheet with the lookup value in D1 and search values in A2:A100, enter =MATCH(D1,A2:A100,0) in another cell. If D1 contains “M-204” and the first match is in A4, the formula returns 3 because A4 is the third cell in A2:A100—not worksheet row 4.

To convert that position into the worksheet row, the same layout can use =ROW(A2)-1+MATCH(D1,A2:A100,0). With the match in A4, this returns 4. The final argument of zero requests an exact match. If you use Application.Match in VBA, handle the possibility of no match: it returns an error value rather than a normal position. For retrieving a cell object and then reading its row, Range.Find is often more direct.

Running and adapting the macro safely

In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, insert a standard module, and paste the procedure. Run it from the editor with F5 while the cursor is inside the procedure, or run it through Excel’s Macro dialog. Save a workbook containing VBA as an Excel Macro-Enabled Workbook (.xlsm) if you need to retain the code.

On a Mac, Excel also supports VBA macros, though keyboard shortcuts can depend on the keyboard and system settings; Option+F11 may open the Visual Basic Editor, and some keyboards require Fn as well. Excel for the web does not run VBA macros, so open the workbook in the desktop Excel application to use this code. Macro security settings may also require you to allow a trusted workbook’s macros before running them.

Before running a macro that changes workbook data, save a separate copy. VBA actions cannot be undone with Ctrl+Z, even if the macro appears to make only a small change. The sample above only displays a message, but a modified version that edits or deletes rows should be tested on a copy first.

Frequently Asked Questions

How do I get the row number of a match in Excel VBA? Use Find on the target range, check that the returned range is not Nothing, and read its .Row property. That property gives the worksheet row number.

Does Find return the position in the range or the sheet row? Find returns a cell reference. Its .Row property is the worksheet row. This differs from MATCH, which returns a position relative to the range searched.

How can I make VBA find an exact match? Set LookAt:=xlWhole in the Find call. Also decide whether the search should distinguish uppercase and lowercase by setting MatchCase.

What happens if VBA cannot find the value? Find returns Nothing. Test If Not foundCell Is Nothing Then before accessing its row or other properties.

How do I find every matching row instead of the first one? Use Find for the first result, then continue with FindNext. Keep the first result’s address and stop when the search returns to it, so the loop ends after Excel wraps through the 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.