VBA Find Match: Search Excel Cells and Return Results

In Excel VBA, use the Range.Find method to search a range and return the first matching cell. For an exact whole-cell match, set LookAt:=xlWhole; for a match contained within a cell, use LookAt:=xlPart. Set the other search options explicitly, because Excel can retain Find settings from earlier searches.

What VBA Find does

Range.Find searches cells in a specified range and returns a Range object for the first cell that matches your search. It does not return the cell’s row number or value directly; once you have the returned cell, you can read its Address, Row, Column, or Value property. If no match exists, the result is Nothing, so test for that before using the returned object.

The method is useful when a macro needs to locate a label, product code, date, or other value without checking every cell itself. You choose the search range, what to look for, whether to match the whole cell or part of it, and whether capitalization matters. For example, searching column A for M-1042 can locate a product row; the macro can then read related information from the same row.

Find is a search operation, not a replacement operation. It does not alter the cell it finds. To change a value, a macro must deliberately assign a new value after locating the cell. That separation is helpful: a search can be used to report a result, select a related record, or check whether an entry exists without modifying the workbook.

Find the first matching cell

This complete example searches cells A2:A100 on a worksheet named Data. It looks for an exact match without regard to capitalization, then prints the address and value in Excel’s Immediate window. In the Visual Basic Editor, open the Immediate window with Ctrl+G on Windows; on Mac, the editor and shortcut can vary by version.

Save a copy of the workbook before running a macro. Although this example only searches and prints information, VBA changes cannot be undone with Ctrl+Z. If the workbook needs to retain VBA code, save it as an Excel Macro-Enabled Workbook (.xlsm), not .xlsx.

Sub FindProductCode()
    Dim ws As Worksheet
    Dim searchRange As Range
    Dim foundCell As Range

    ' Search the specified worksheet and cells.
    Set ws = ThisWorkbook.Worksheets("Data")
    Set searchRange = ws.Range("A2:A100")

    ' Search for a whole-cell match, starting at the top after wrapping.
    Set foundCell = searchRange.Find( _
        What:="M-1042", _
        After:=searchRange.Cells(searchRange.Cells.Count), _
        LookIn:=xlValues, _
        LookAt:=xlWhole, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, _
        MatchCase:=False, _
        SearchFormat:=False)

    If foundCell Is Nothing Then
        Debug.Print "No match found."
    Else
        Debug.Print "Found at " & foundCell.Address & ": " & foundCell.Value
    End If
End Sub

The After argument must refer to one cell inside the search range. Here it is set to the range’s last cell, so searching forward begins at the first cell after wrapping around. If you omit After, Excel uses its default starting behavior; specifying it makes the intended starting point clearer. Change the worksheet name, range, and search text to fit the workbook.

Choose exact or partial matching

The LookAt argument controls whether the search text must match the whole cell or only appear somewhere in it. Use xlWhole to find a cell containing exactly the searched text, and xlPart to find a cell whose contents include it. This choice is often the difference between finding the intended record and returning an unintended one.

Whole-cell match: searching for North with xlWhole matches a cell containing North, but not North Region.
Partial match: searching for North with xlPart can match North Region as well as a cell containing North by itself.
Case sensitivity: MatchCase:=False treats uppercase and lowercase letters as equivalent; MatchCase:=True requires the capitalization to match.

“Exact match” in this context means a whole-cell match according to Find’s search rules; it is not a general guarantee of strict data-type comparison. If a workbook mixes text that looks like a number with numeric values, check the actual cell contents and format when the distinction matters. A displayed value may also differ from the underlying value: for instance, a date can display as a formatted date while Excel stores it as a serial number.

Understand the Find arguments

Find has several optional arguments. For dependable code, provide the options that define the search instead of relying on settings left over from Excel’s Find dialog or another macro. The example uses these arguments:

What is the text, number, or other value to search for.
After identifies the cell after which the search begins. It must be a single cell within the range.
LookIn selects what Excel searches. xlValues searches evaluated cell values, while xlFormulas searches formula text.
LookAt selects whole-cell matching (xlWhole) or partial matching (xlPart).
SearchOrder chooses row-by-row (xlByRows) or column-by-column (xlByColumns) traversal when the range has multiple rows or columns.
SearchDirection sets forward (xlNext) or reverse (xlPrevious) search direction.
MatchCase controls whether capitalization matters, and SearchFormat controls whether formatting is part of the search.

Use xlValues when the goal is to locate what a user sees as the result of a formula. Use xlFormulas when searching for formula text itself, such as a formula containing a particular reference. These are different questions: a cell with a formula may display the value 12 while its formula text is =SUM(B2:B4). Pick the setting that matches what you intend to find.

Find every matching cell

A single call to Find returns only the first match. To collect all matches, store the first cell’s address, process it, and repeatedly call FindNext. Excel wraps around to the beginning of the search range, so the loop must stop when it reaches the first address again. Without that stopping condition, a loop can keep finding the same matches indefinitely.

For example, the first result might be printed before a loop calls FindNext for the next result. Each subsequent match can be handled by reading its row, copying a value, or adding its address to a report. Be clear about what the macro will do with each result; if it writes to cells, test on a duplicate workbook because those edits are not reversible with Ctrl+Z.

When using FindNext, keep the original search range and settings in mind. The method continues the most recent Find search; it does not take a new search term or a new collection of options. If the searched range contains no match, do not call FindNext on a missing result. A practical multi-match loop first checks that the initial result is not Nothing, records its address, then exits when a later result is missing or returns to that address.

Wildcards and common search problems

With partial matching, Excel Find supports wildcard characters. An asterisk (*) represents any sequence of characters, and a question mark (?) represents one character. For example, a search for AB* can match text beginning with AB. To search for a literal asterisk, question mark, or tilde, prefix that character with a tilde: ~*, ~?, or ~~. Wildcards are useful for variable codes, but can create unexpected matches if the search text contains them unintentionally.

A frequent bug is omitting arguments such as LookIn, LookAt, or SearchFormat. Excel’s Find dialog remembers several settings, and the VBA Find method can share that state. A search that worked once may behave differently after a user changes the dialog’s options. Explicitly passing the important arguments makes the macro’s behavior more predictable.

No result: confirm the range includes the target cell, the worksheet name is correct, and the search term matches the cell content. Also check whether you need xlValues or xlFormulas.
Unexpected partial result: use LookAt:=xlWhole if extra text in the cell should disqualify it.
Wrong capitalization behavior: set MatchCase explicitly.
Search depends on formatting: set SearchFormat:=False unless matching formatting is intentional.
Macro reports an error: verify that the named worksheet exists and that the After cell is inside the range being searched.

A related misconception is that Find searches only visible cells. The method searches the range you pass to it; hiding rows or columns does not itself redefine that range as visible-only. If only visible cells should be considered, the macro needs additional logic to identify them rather than assuming Find automatically excludes hidden cells.

Use the result to locate related data

Once Find returns a cell, its position can guide the next operation. If the match is in a product-code column, foundCell.Row gives the row number, and the macro can read another column on that row. For example, a code found in column A might have its description in column B and stock quantity in column C. This is often more useful than selecting the found cell, because code can read the related values directly without changing the user’s active selection.

Before using the row this way, make sure the search range is aligned with the table and that the matching code is unique if the macro expects one record. If several rows can share the same label, a first-match search is not enough to identify a unique record; use a FindNext loop or add another condition. Also decide what should happen when no match exists: show a message, log the missing code, or skip the operation. An explicit no-match path prevents later code from trying to read properties from Nothing.

Find compared with other Excel search methods

Choose Find when VBA needs to search a range using Excel’s familiar search options and return the matching cell. For a small fixed list, a loop through cells can be easier to customize, especially if the rule involves multiple conditions or data types. A loop also makes it straightforward to compare normalized text, such as trimming spaces before comparing, though it requires more code and care around blank or error-valued cells.

Worksheet functions such as MATCH or XMATCH are alternatives when the goal is to return a position as a worksheet calculation rather than work directly with a cell object in VBA. XMATCH is available in Microsoft 365 and Excel 2021 or later. Excel’s Find dialog is appropriate for a one-time manual search: press Ctrl+F on Windows or Command+F on Mac. The VBA method is the better fit when the search should run repeatedly as part of a macro.

Frequently Asked Questions

How do I make VBA Find match the whole cell?
Pass LookAt:=xlWhole in the Range.Find call. Use xlPart when the searched text can appear inside a longer cell entry.

What does VBA Find return if it finds nothing?
It returns Nothing. Check with If foundCell Is Nothing Then before reading the address, value, or row of the result.

How do I find all matches instead of the first one?
After the first successful Find, call FindNext repeatedly on the same search range. Save the first match’s address and stop when the search wraps back to it.

Should I use xlValues or xlFormulas?
Use xlValues to search calculated or displayed results, and xlFormulas to search the formula text stored in cells. Choose based on whether you are looking for the result or the formula expression.

Why does Find return a different result than I expect?
Check the range, LookAt, capitalization, wildcards, and LookIn. Also set SearchFormat:=False if formatting should not affect the search, and specify the other important options explicitly because Find settings can persist.

Can VBA Find search a whole worksheet?
Yes. Pass a worksheet range such as ws.Cells, but consider the size and the kind of data being searched. A defined range such as the relevant column or used data area is often clearer and avoids searching more cells than the task requires.

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.