VBA Find Exact Match: Use Find with xlWhole

To find a cell whose entire contents match a value in Excel VBA, use Range.Find with LookAt:=xlWhole. For example, search a defined range with LookIn:=xlValues to find a cell whose value is exactly “North,” rather than a cell containing “Northwest.”

What “exact match” means in VBA

In Excel’s Find method, “exact” usually means that the search text must match the whole cell content, rather than appearing somewhere within it. The setting that controls this is LookAt: xlWhole searches for a whole-cell match, while xlPart permits a partial match. If the cell contains “North Region,” searching for “North” with xlWhole does not match; searching with xlPart can.

Whole-cell matching is separate from case sensitivity and data type. Excel’s Find search is case-insensitive unless you set MatchCase:=True. Also, a number stored as text can behave differently from a numeric value in later comparisons or lookups. So decide what you mean by exact: the whole cell’s searchable content, an exact letter case, or a value of a particular data type.

For the common task of locating an item in a worksheet, setting LookAt:=xlWhole is the key step. The other arguments matter too: explicitly setting them makes a macro more predictable than relying on Excel’s current Find settings.

How Range.Find works

Find is a method of a Range object. You call it on the cells you want to search, then give it the value to look for and the search options. It returns a Range pointing to the first matching cell it finds. If there is no match, it returns Nothing, so VBA code should check for that before reading the result’s address or value.

For an ordinary search of cell values, these arguments are useful:

What specifies the target text or value.
LookIn:=xlValues searches cell values, including the calculated results shown by formulas.
LookAt:=xlWhole requires a whole-cell match.
SearchOrder:=xlByRows searches across rows, while xlByColumns searches down columns first.
SearchDirection:=xlNext searches forward through the selected range.
MatchCase:=False makes the search case-insensitive; use True when letter case must match.
SearchFormat:=False prevents a saved format criterion from affecting the search.

The After argument also affects where the search begins. It must refer to one cell within the range being searched. In the example below, it is set to the last cell, so the next search position is the beginning of the range. Excel Find wraps around at the end of the range, which is another reason to specify a clear search range and check the result.

A basic exact-match macro

This example searches A2:A100 on a worksheet named Data for the whole-cell value “North.” It displays the address of the first match, or reports that no match was found. Change the sheet name, range, or search value to suit your workbook.

Sub FindExactMatch()
    Dim searchRange As Range
    Dim foundCell As Range
    Dim searchValue As String

    ' Set the cells to search and the exact target.
    Set searchRange = ThisWorkbook.Worksheets("Data").Range("A2:A100")
    searchValue = "North"

    ' Search cell values and require a 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 foundCell Is Nothing Then
        MsgBox "No exact match was found."
    Else
        MsgBox "Exact match found in " & foundCell.Address
    End If
End Sub

Put the procedure in a standard VBA module. In Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11; on Mac, use Option+F11 (some keyboards also require Fn). The macro uses ThisWorkbook, meaning the workbook containing the code. If the data is in a different workbook, qualify the worksheet reference with that workbook instead.

Save a copy of the workbook before running a macro. VBA actions cannot be undone with Ctrl+Z, even when the macro appears to make a small change.

Searching formulas or displayed results

Choose LookIn according to what you are trying to locate. With LookIn:=xlValues, Find searches the cell values, including results calculated by formulas. If a cell contains a formula that evaluates to “North,” a search for “North” can find that result. This is usually the right choice when searching what the worksheet data represents.

Use LookIn:=xlFormulas when the target is formula text itself. For example, searching for part of a formula can locate cells based on what their formulas contain, rather than on the values those formulas currently return. That is a different task from finding a cell whose result equals a target. Be explicit about this distinction: a cell can display the same result as another cell while containing a completely different formula.

Displayed formatting can also mislead. A cell may show a rounded number, a date, or a formatted number with leading zeros, even though its underlying value is stored differently. If the visual text is what matters, inspect the value and number format deliberately rather than assuming that what appears on screen is the cell’s underlying data.

Finding every exact match

A single call to Find returns one matching cell, not a collection of all matches. To process duplicates, store the address of the first result, then use FindNext to continue until the search returns to that address. The search wraps around, so ending the loop based only on whether a result exists can make it run indefinitely.

In a duplicate-finding routine, check that the initial result is not Nothing, save its address, process that cell, and then repeatedly call FindNext. Stop when the result is Nothing or when its address equals the saved first address. For each result, you might record the address, highlight the cell, or copy its value to a report. If changing cells during the search, consider collecting the addresses first; edits to the searched range can affect what subsequent searches encounter.

Be clear about whether duplicate matches should be case-sensitive. Set MatchCase consistently in the initial Find call; FindNext continues that search. Also keep the search range fixed, rather than searching the entire worksheet if only one column or table field is relevant. A narrow range improves clarity and avoids unrelated matches elsewhere.

Case, spaces, and wildcard characters

By default, “North” and “north” are treated as matching text. To distinguish them, set MatchCase:=True. This changes letter-case handling but does not make Find a general-purpose validator for every aspect of a string. Leading or trailing spaces remain part of the cell content, for example: a cell containing “North ” may not match the target “North” as a whole-cell search.

Data imported from websites or other systems may contain nonbreaking spaces or other characters that are hard to see. If a value looks identical but does not match, inspect the cell’s actual contents, trim or normalize the source data as appropriate, and then search again. Do not remove spaces blindly if they are meaningful, such as in identifiers or carefully formatted codes.

Excel Find treats the asterisk (*) and question mark (?) as wildcard characters, even when LookAt:=xlWhole is used. An asterisk can stand for a sequence of characters, and a question mark can stand for one character. To search for those characters literally, prefix them with a tilde (~) in the search text; a literal tilde itself is represented by two tildes. This matters when searching product codes or filenames that actually include wildcard symbols.

Common problems and how to diagnose them

The macro finds a partial-looking result: Confirm that LookAt:=xlWhole is present. If the result still surprises you, check whether the cell’s value includes spaces or wildcard characters.
The macro finds nothing, but the value appears to exist: Verify the worksheet name and range, and confirm whether the target is stored as text, a number, or a formula result. Check for hidden characters and choose the correct LookIn setting.
The result changes between runs: Find settings such as Look In, Look At, and Match Case can persist from earlier searches in Excel. Set every relevant argument in the VBA call instead of leaving settings implicit.
The wrong duplicate is returned first: Find returns the first result according to the specified search order and direction, not necessarily the first visible row you had in mind. Specify those arguments and define a range whose layout makes the order clear.
A search unexpectedly uses formatting: Set SearchFormat:=False unless formatting is intentionally part of the search. This avoids a previously configured format criterion affecting the outcome.

When diagnosing a mismatch, test the same target in a small, known range and display the found cell’s address and value. That separates a search-setting issue from a problem in the source data. For a computed cell, also check the formula bar and the displayed result; those are not necessarily the same searchable content.

When to use Find instead of another method

Range.Find is a good fit when you want to locate a matching cell, inspect its address, or continue through multiple matches. It searches an existing range and can use settings such as whole-cell matching and case sensitivity. It does not, by itself, tell you whether a cell satisfies additional business rules or return a neatly arranged list of matching records.

For a direct test of two values in VBA, the equality operator may be simpler: compare the cell’s value with the target and branch based on whether they are equal. That approach is useful when looping through rows and applying other conditions. For searching a large range for one result, Find is often more convenient than writing a cell-by-cell loop. For worksheet calculations that return a corresponding value, Excel lookup functions may be more appropriate than a VBA macro. Choose based on whether the task is to locate cells, test values, or produce a formula-driven result.

These approaches are not interchangeable in every detail. A VBA comparison may need deliberate handling of case and data types, while Find has its own wildcard and persistent-setting behavior. If exact matching is important for identifiers, test representative examples—including capitalization, spaces, numeric-looking text, and wildcard symbols—before relying on the result in a larger process.

Frequently Asked Questions

How do I make VBA Find match the whole cell?

Set the Find argument LookAt:=xlWhole. This prevents a search for “Cat” from matching a cell whose contents are “Catalog,” assuming the search text and cell content otherwise match.

Is VBA Find case-sensitive by default?

No. Set MatchCase:=True when uppercase and lowercase letters must be distinguished. Use False for the usual case-insensitive search.

What does Find return if there is no exact match?

It returns Nothing. Check If foundCell Is Nothing Then before using properties such as Address or Value.

Does xlWhole ignore spaces at the beginning or end?

No. Whole-cell matching does not automatically trim the cell or target. If stray spaces are present, clean or normalize the data first, while preserving any spaces that are meaningful.

Can Find return every matching cell?

One Find call returns one result. Use FindNext to continue through matches, and stop when the search returns to the address of its first result because Excel wraps around 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.