Excel VBA: Find a Value in a Column and Return Its Row

To find a value in a column with Excel VBA, use the Range.Find method on the cells you want to search. It returns the matching cell, so you can read its address, row number, or value; if there is no match, it returns Nothing.

How Range.Find works

Find searches a range and returns a Range object for the first matching cell it encounters. For example, searching A2:A100 restricts the search to that part of column A. Searching the entire worksheet column is also possible, but a defined range is often clearer and avoids searching many unnecessary cells.

A search can match the entire cell or find text within a cell. It can also look at a cell’s displayed value or its formula. These choices matter: a search for Pending with a whole-cell match will not match Pending review, and a search of displayed values behaves differently from a search of formulas.

Excel remembers some Find dialog settings from earlier searches. VBA calls can therefore behave unexpectedly if important arguments are omitted. Specify the search options in the code rather than relying on settings left behind by a user or another macro.

A basic macro to find an exact value

This example searches A2:A100 on a worksheet named Sheet1 for the exact value Pending. It reports the cell address and worksheet row. Change the worksheet name, range, and search value to suit your workbook.

Sub FindValueInColumn()
    Dim ws As Worksheet
    Dim searchRange As Range
    Dim foundCell As Range
    Dim searchValue As Variant

    ' Set the worksheet and the cells to search.
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    Set searchRange = ws.Range("A2:A100")
    searchValue = "Pending"

    ' Search for a whole-cell match among displayed values.
    Set foundCell = searchRange.Find( _
        What:=searchValue, _
        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
        MsgBox "Found at " & foundCell.Address(False, False) & _
            " on row " & foundCell.Row & "."
    End If
End Sub

The After argument sets where the search begins. Here it starts after the last cell in the range, so the next cell considered is the beginning of the range. Excel’s search wraps around the range. The code checks for Nothing before trying to use the result; omitting that check can cause a run-time error when there is no match.

The example uses ThisWorkbook, which means the workbook containing the macro. If the code is stored in a personal macro workbook or another file, that may not be the workbook containing the data. In that case, set ws to the intended workbook and worksheet explicitly.

Choosing the search options

The named arguments control what counts as a match and how Excel searches. The example sets the commonly important options explicitly:

LookIn:=xlValues searches the cell’s value, including a formula’s calculated result. Use xlFormulas when you want to search formula text instead.
LookAt:=xlWhole requires the whole cell to match. xlPart finds the search text anywhere inside the cell.
SearchOrder:=xlByRows specifies row-wise search order. For a single column, the distinction is usually less noticeable; it matters more across a multi-column range.
SearchDirection:=xlNext searches forward from the starting point. xlPrevious searches in the opposite direction.
MatchCase:=False makes a text search case-insensitive. Set it to True if uppercase and lowercase letters must differ.
SearchFormat:=False prevents a saved formatting criterion from affecting the search.

For a formula such as =A1&"-done", xlValues searches the result shown in the cell, while xlFormulas searches the formula expression. A number formatted as a date is still stored as a numeric value, so formatting what you see is not always the same as the underlying value being searched.

Finding part of a cell or matching text case

To find a word within a longer entry, change LookAt to xlPart. For example, searching for Pending in cells containing Pending review and Not Pending can return either one, depending on the search start and order. If the macro must distinguish those cases, use an exact match or add code that checks the returned cell’s full value.

Excel’s Find method supports wildcard characters in text searches. An asterisk represents any sequence of characters, and a question mark represents one character. To search for a literal asterisk or question mark, prefix it with a tilde. Wildcards are useful for flexible text matching, but they can produce broader results than an exact search. They are not a substitute for checking the value when the matching rule is important.

Case-insensitive matching is the default behavior when MatchCase is False. Set it to True for case-sensitive searches. This option applies to text; it does not change how numeric values compare.

Finding every matching cell

Find returns one match. To process duplicates, call FindNext repeatedly and stop when the search returns to the first matching address. This example prints each match’s address and value to the VBA Immediate window, which you can display in the Visual Basic Editor with View > Immediate Window.

Sub FindAllValuesInColumn()
    Dim ws As Worksheet
    Dim searchRange As Range
    Dim foundCell As Range
    Dim firstAddress As String
    Dim searchValue As Variant

    ' Set the worksheet, search range, and value.
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    Set searchRange = ws.Range("A2:A100")
    searchValue = "Pending"

    ' Find the first whole-cell match.
    Set foundCell = searchRange.Find( _
        What:=searchValue, _
        After:=searchRange.Cells(searchRange.Cells.Count), _
        LookIn:=xlValues, _
        LookAt:=xlWhole, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, _
        MatchCase:=False, _
        SearchFormat:=False)

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

The loop ends when it reaches the original match again, preventing an endless cycle. If you change or delete searched cells while looping, the search results can shift; collect the matching addresses first or process them carefully. The Immediate window is useful for inspection, but a production macro can instead write results to another range or build a report.

Using the result and choosing a search range

Once foundCell is set, its properties provide different useful answers. foundCell.Row gives the worksheet row number, foundCell.Address gives a cell address such as $A$12, and foundCell.Value gives the cell’s value. To return a related value from another column, use the matching row number with that column—for instance, if identifiers are in A and names are in B, the corresponding name is in column B on foundCell.Row.

Use a range that reflects the data you intend to search. A fixed range such as A2:A100 is simple, but it will not include new entries below row 100. A table column or a range calculated from the last used row can adapt better as data grows. Be deliberate about the header: searching A:A can return a heading if it happens to match the search text. Starting at A2 avoids that when row 1 contains headers.

Searching a whole column is not automatically wrong, but it can make the scope less obvious and may include cells outside the current data list. For a large workbook or a frequently run macro, limiting the range to the actual data is easier to maintain and can reduce unnecessary work.

Common problems and how to fix them

The macro says there is no match: Check the worksheet, range, spelling, and whether you need xlWhole or xlPart. Also check whether the cells contain formulas and whether LookIn should be xlValues or xlFormulas.
The result is the wrong occurrence: A range can contain duplicates. Use the all-matches loop, or choose a search direction and start point that match your intended rule.
The macro finds text that looks different: Values may contain leading or trailing spaces, or one entry may be stored as text while another is numeric. Inspect the actual cell value and data type rather than relying only on how the cells look.
The search behaves differently after using Excel’s Find dialog: Set the relevant arguments in every call, especially LookIn, LookAt, and SearchFormat.
A run-time error occurs after a failed search: Test whether the result is Nothing before reading its address, row, or value.

Cells with errors, merged cells, and unusual data layouts can make a simple search less intuitive. If the macro processes important data, try it on a copy and verify the returned addresses against known examples. Also check whether hidden rows or filtered-out records should count: Find searches the specified range, not just the rows currently visible on screen.

Find compared with other VBA approaches

Range.Find is a practical choice when you need a matching cell, especially for a bounded range, flexible text matching, or repeated searches for duplicates. It also makes it straightforward to retrieve the match’s row and address.

A loop through each cell is easier to customize when the rule is more complex than equality—for example, when you need to trim spaces, compare case-sensitively, or apply several conditions. For a very large range, a macro can read values into an array and inspect them in memory, but that approach requires more code and care when translating array positions back to worksheet rows. Choose the simplest method that expresses the matching rule correctly; do not switch approaches merely because a worksheet formula or the Find dialog can also locate values.

Running and saving the macro safely

In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11, insert a standard module through Insert > Module, and paste in the macro. Run it from the editor with F5, or run a selected macro from Developer > Macros. If the Developer tab is hidden, enable it in File > Options > Customize Ribbon.

On Mac, the Visual Basic Editor is available from Developer > Visual Basic when the Developer tab is enabled; the keyboard shortcut can depend on the keyboard’s function-key settings. Excel for the web does not run VBA macros, so open the workbook in the desktop Excel app to use this code. Save a macro-enabled workbook as .xlsm if it needs to retain VBA.

Save a copy of the workbook before running a macro, especially if you later modify the code to delete, overwrite, or move data. VBA actions generally cannot be reversed with Ctrl+Z, and Excel may clear the undo history when a macro runs. Only enable macros in workbooks from sources you trust.

Frequently Asked Questions

How do I get the row number of a found value?
After a successful search, use foundCell.Row. First check that foundCell is not Nothing; otherwise there is no match from which to read a row.

How do I find an exact match in a column?
Set LookAt:=xlWhole in the Find call and search only the intended column range. This avoids treating a longer cell entry containing the search text as an exact match.

How do I find all duplicates with VBA?
Find the first match, record its address, and use FindNext until the search returns to that address. The example above prints each matching cell to the Immediate window.

Does Range.Find distinguish uppercase and lowercase?
Not when MatchCase:=False, as in the examples. Set MatchCase:=True when the case of text must match.

Can I use this VBA code in Excel for the web?
No. Excel for the web does not execute VBA macros. Open the workbook in desktop Excel for Windows or Mac to run VBA.

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.