Excel VBA: Find the Row Number of a Matching Value

In Excel VBA, use the Range.Find method to locate a value, then read the matching cell’s .Row property. For an exact match in column A, search a defined range with LookAt:=xlWhole, check whether Find returned Nothing, and use foundCell.Row when it found a cell.

A practical VBA example

This macro searches cells A2:A100 on a worksheet named Data for the exact text INV-204. It reports the matching worksheet row, or a message if the value is absent. The search range starts at row 2 so the header in A1 is not included.

Sub FindMatchingRow()
    Dim ws As Worksheet
    Dim searchRange As Range
    Dim foundCell As Range
    Dim targetValue As Variant
    Dim rowNumber As Long

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

    ' Search for a complete cell value, not a partial match.
    Set foundCell = searchRange.Find( _
        What:=targetValue, _
        After:=searchRange.Cells(searchRange.Cells.Count), _
        LookIn:=xlValues, _
        LookAt:=xlWhole, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, _
        MatchCase:=False, _
        SearchFormat:=False)

    If foundCell Is Nothing Then
        MsgBox "Value not found."
    Else
        rowNumber = foundCell.Row
        MsgBox "Found on worksheet row " & rowNumber & "."
    End If
End Sub

Change the worksheet name, range, or target value to suit the workbook. In this example, if INV-204 is in cell A17, foundCell.Row is 17. The code uses Long for the row number, which is suitable for Excel’s worksheet row numbers.

Save a copy of the workbook before running a macro. VBA macros cannot be undone with Ctrl+Z, even if their effects appear simple. Save a workbook containing VBA as an Excel Macro-Enabled Workbook (.xlsm) if you need to keep the code.

What Find returns, and what Row means

Range.Find returns a Range object for the first cell that meets the search conditions. It does not return a row number directly. The .Row property of that object supplies the cell’s worksheet row number, so a match in D28 returns 28 regardless of which column was searched.

That is different from the match’s position within the search range. If the search range is A2:A100 and the match is in A17, the worksheet row is 17, while the match is the sixteenth cell in that range. This distinction matters when code uses the position to index an array or another range. For a one-column range, the relative position can be calculated as foundCell.Row - searchRange.Row + 1; use foundCell.Row when the needed result is the actual worksheet row.

Keep the search range as narrow as practical. Searching only the relevant column and data rows is faster to reason about than searching an entire worksheet, and it avoids accidentally finding a matching note or value elsewhere. If the dataset grows, you can set the range dynamically or use an Excel table’s data column rather than relying on a fixed last row.

Choose exact, partial, and case-sensitive matching

The LookAt argument controls whether Find looks for a complete cell value or a piece of one. Use xlWhole for an exact cell match: searching for INV-20 will not match INV-204. Use xlPart when a value can appear within a longer cell entry, such as finding “North” in “North Region.” For IDs, codes, and names, exact matching is usually less likely to return an unintended row.

Find is case-insensitive when MatchCase:=False, as in the example. Set it to True if uppercase and lowercase letters must be distinguished. A search for “abc” can otherwise match “ABC.” Case sensitivity is separate from exactness: LookAt:=xlWhole controls whether the whole cell must match, while MatchCase controls letter case.

When using partial matching, remember that Find supports wildcard characters. An asterisk represents any sequence of characters, and a question mark represents one character. A tilde can be used to search for a literal wildcard character. If the lookup text might contain these symbols, account for them rather than assuming they will always be treated as ordinary characters.

LookIn values versus formulas

The LookIn setting determines what Find searches in a cell. xlValues searches the cell’s value, including the calculated result of a formula. xlFormulas searches the formula content. For example, if B4 contains =A1&A2 and displays the text North, a search for North with LookIn:=xlValues can find the displayed result; a search for part of the formula text calls for xlFormulas.

Choosing the right setting avoids a common misunderstanding: a formula cell’s displayed result and its underlying formula are not the same search target. Also, a cell may display a rounded or formatted version of its stored value. Find is not a general-purpose search of every formatted character on screen, so if the distinction between the underlying value and its display matters, inspect the actual cell value or use a deliberately formatted comparison in code.

Why Find needs explicit settings

Excel remembers several Find options between calls and between uses of the Find dialog. If code leaves an argument unspecified, a setting left over from an earlier search can affect the result. That is why the example supplies LookIn, LookAt, search order, direction, case handling, and format handling explicitly. This makes the macro’s intended behavior clearer and more repeatable.

The After argument also affects where the search begins. It must refer to a single cell within the range being searched. Find begins after that cell and wraps around the range if needed. Setting it to the last cell in the range makes the next cell in the search order the range’s first cell, a convenient way to search from the start. If the range contains only one cell, that same approach remains valid.

SearchOrder:=xlByRows and SearchDirection:=xlNext specify a forward, row-by-row search. They are especially useful when a search range spans more than one column. In a one-column range, row-by-row order is intuitive, but stating it explicitly documents the intended behavior.

Handle duplicates and find every matching row

A single call to Find returns one matching cell, not every duplicate. To visit all matches, store the first match’s address, then use FindNext until the search wraps back to that address. The following example prints each matching worksheet row to the VBA Immediate window. In the Visual Basic Editor, open that window with Ctrl+G on Windows.

Sub PrintAllMatchingRows()
    Dim ws As Worksheet
    Dim searchRange As Range
    Dim foundCell As Range
    Dim firstAddress As String
    Dim targetValue As Variant

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

    Set foundCell = searchRange.Find( _
        What:=targetValue, _
        After:=searchRange.Cells(searchRange.Cells.Count), _
        LookIn:=xlValues, _
        LookAt:=xlWhole, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, _
        MatchCase:=False, _
        SearchFormat:=False)

    If foundCell Is Nothing Then Exit Sub

    firstAddress = foundCell.Address
    Do
        Debug.Print foundCell.Row
        Set foundCell = searchRange.FindNext(After:=foundCell)
        If foundCell Is Nothing Then Exit Do
    Loop While foundCell.Address <> firstAddress
End Sub

The address check prevents an endless loop: after reaching the last match, FindNext can wrap around and return the first one again. This example prints row numbers; replace Debug.Print with an operation suited to the task, such as copying a value. If you change cells while looping, take care not to alter the search results in a way that skips or repeats matches.

Use Match when you need a position instead

Application.Match is an alternative when the goal is to find a position in a one-dimensional range. With its third argument set to zero, Match looks for an exact value and returns a relative position, not a worksheet row number. If the search range begins at row 2 and the match is its sixteenth item, adding the range’s starting row minus one converts that position to worksheet row 17.

Find is often a natural choice when you want the matching cell itself, need options such as case matching, or may want to inspect the cell after locating it. Match can be concise for a simple one-column lookup, but handle its not-found result: Application.Match returns an error value when there is no match. Do not treat its returned position as a worksheet row without converting it for the range’s starting row.

Common causes of a missing match

If Find unexpectedly returns Nothing, check the search range and the type and contents of the target before changing the code. These are frequent causes:

The range excludes the row. A range set to A2:A100 will not find a value in A101.
Text and numbers differ. A numeric 204 and text “204” may not behave as the same lookup value in all comparisons. Check how the source data is stored.
Extra spaces or characters are present. A trailing space, nonbreaking space, or punctuation can prevent an exact match. Inspect or clean the source data if appropriate.
The wrong content is being searched. A formula result and its formula text require different LookIn settings.
A previous Find option leaked into the search. Specify the relevant arguments explicitly, especially LookAt and LookIn.
The lookup value is an error or unexpected Variant. Check what the code actually assigns to the target before searching.

Use the VBA debugger or a temporary Debug.Print targetValue statement to inspect the value being searched. Also verify the worksheet reference: ThisWorkbook means the workbook containing the macro, while ActiveWorkbook means the workbook currently active in Excel. If users can switch workbooks before running the macro, the distinction can determine whether the code searches the intended file.

Run the macro safely in Excel

In Microsoft 365 for Windows, open the Visual Basic Editor with Alt+F11, insert a standard module, and paste the procedure there. If the Developer tab is visible, use Developer > Visual Basic to open the editor. Run the macro from the editor with the cursor inside it, or open the macro list with Alt+F8 and select the procedure.

On a Mac, the Visual Basic Editor and macro dialog shortcuts can depend on the keyboard and system settings; the Developer tab provides a menu route where available. Excel for the web can open and edit many workbook contents, but it cannot create, run, or edit VBA macros. Use the desktop Excel application for this VBA procedure. Only enable macros in files you trust, and keep a backup before running code that changes workbook data.

Frequently Asked Questions

How do I get the row number from a Find result in VBA?
Store the returned Range object, confirm it is not Nothing, and read foundCell.Row. That returns the worksheet row containing the match.

How can I find an exact match in Excel VBA?
Set LookAt:=xlWhole in the Find call. Also specify the search range and LookIn setting so the macro searches the intended cells and their values or formulas.

What does Find return if the value is not found?
It returns Nothing. Test for that result before reading properties such as .Row; otherwise, the code will fail when it tries to use a nonexistent Range object.

How do I find all rows containing the same value?
Call Find once, save the first match’s address, and use FindNext repeatedly until it returns to that address. This handles duplicates while stopping when the search wraps around.

Does Find return a relative row number?
No. The matching cell’s .Row is its absolute worksheet row. A relative position within the range is a separate calculation and depends on where that range starts.

Can I run VBA in Excel for the web?
No. Excel for the web does not run VBA macros. Open the workbook in the desktop version of Excel to use this code.

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.