Excel VBA Range Find Exact Match with xlWhole Explained

To find a cell whose entire contents match a value in Excel VBA, call Range.Find with LookAt:=xlWhole, and check whether the returned range is Nothing. Set the other important search options explicitly as well, because Excel remembers Find settings and those saved settings can otherwise affect a macro.

What “exact match” means in Range.Find

In VBA, LookAt:=xlWhole tells Excel to match the whole cell entry rather than a portion of it. If the search text is “North,” a whole-cell search can match a cell containing only “North,” but not “North region.” By contrast, LookAt:=xlPart allows a match inside a longer entry.

“Whole cell” does not automatically mean every possible kind of exactness. The search can still be case-insensitive, and Excel treats asterisks and question marks as wildcard characters unless you escape them. A cell’s underlying value, formula, number format, and spaces can also matter. Decide whether you need whole-cell matching alone or also need to control case, literal wildcard characters, and the kind of cell content searched.

The method belongs to Excel’s Range object, so the range you call it on sets the search area. For instance, searching Worksheets("Sheet1").Range("A2:A100") confines the search to those cells rather than the whole worksheet.

A reliable VBA example

This macro searches A2:A100 on Sheet1 for the whole-cell value “North,” ignoring case. It searches evaluated values rather than formula text, searches by rows, and disables saved format criteria. Change the sheet name, range, and search text to suit your workbook.

Option Explicit

Sub FindExactNorth()
    Dim ws As Worksheet
    Dim searchRange As Range
    Dim foundCell As Range
    Dim searchText As String

    Set ws = ThisWorkbook.Worksheets("Sheet1")
    Set searchRange = ws.Range("A2:A100")
    searchText = "North"

    ' Escape Find wildcards so they are treated as literal characters.
    searchText = Replace(searchText, "~", "~~")
    searchText = Replace(searchText, "*", "~*")
    searchText = Replace(searchText, "?", "~?")

    Set foundCell = searchRange.Find( _
        What:=searchText, _
        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 "Match found in " & foundCell.Address(False, False)
    End If
End Sub

Before running any macro, save a copy of the workbook. VBA changes cannot be undone with Ctrl+Z, so a backup is the simplest way to protect the original data. The example only reports the matching address; it does not change any cells.

Why every Find argument matters

Excel’s Find dialog and VBA’s Find method share search settings. Some options can persist from an earlier search, including choices made manually in the Find dialog. A macro that supplies only What and LookAt may therefore behave differently on another computer or after a user changes a setting. Supplying the relevant arguments makes the macro’s intent clear and repeatable.

What is the text or value to look for.
LookAt:=xlWhole requires the whole cell entry to match; xlPart searches within entries.
LookIn:=xlValues searches evaluated values. Use xlFormulas when the formula text itself is what you need to find.
SearchOrder:=xlByRows searches across rows; xlByColumns searches down columns first.
SearchDirection:=xlNext sets the direction, while After determines where the search starts.
MatchCase:=False ignores capitalization; set it to True when capitalization must match.
SearchFormat:=False prevents a saved cell-format criterion from restricting the result.

These named arguments are part of the Find call, not extra steps in the macro. Naming them also makes the code easier to review than relying on positional arguments, whose order is easy to misread.

Literal asterisks, question marks, and tildes

Excel Find interprets * as a wildcard for any sequence of characters and ? as a wildcard for one character. Consequently, LookAt:=xlWhole by itself does not guarantee that a search for A* means the two literal characters “A” and “*”. It can match other whole-cell values beginning with A as well.

To search for literal wildcard characters, prefix them with a tilde in the Find criterion: ~* searches for an asterisk and ~? for a question mark. A literal tilde itself is represented by ~~. The example escapes tildes first, then asterisks and question marks; that order prevents the newly added escape characters from being escaped again.

If the search text is always a fixed word without wildcard characters, those replacement lines are not essential. Keeping them is useful when the search text may come from a user or a cell and might contain punctuation that Excel interprets specially.

Case, spaces, numbers, and formulas

Whole-cell matching and case matching are separate controls. With MatchCase:=False, a search for “North” can match “north.” Set MatchCase:=True when capitalization is meaningful, such as when codes distinguish “ab12” from “AB12.” If case should be ignored but the entire entry must match, retain xlWhole and leave MatchCase false.

Spaces are characters too. A cell containing “North ” with a trailing space may not be an exact match for “North,” even if the difference is hard to see on screen. Leading spaces and nonbreaking spaces can cause similar surprises. If the source data is inconsistent, inspect the cell contents or clean the data first; changing the search to a partial match can create false positives rather than solve the underlying issue.

Numbers and dates deserve a separate check. A date displayed as “Nov 2” is stored as a numeric date value, and a number displayed with currency or decimal formatting may have a different visible presentation from its stored value. Likewise, text “00123” is not necessarily interchangeable with numeric 123. Choose a search value of the appropriate type and test against representative cells. Use xlValues for a calculated result, or xlFormulas if the formula expression itself is the target. The displayed formatting is not a substitute for identifying what is actually stored in the cell.

Search start, wraparound, and multiple matches

Find does not necessarily begin at the top-left cell of the range. Its After argument specifies the cell after which to begin, and Excel searches from there in the chosen direction. In the example, After is set to the last cell of the range, so the next search position is at the start of the range. The cell supplied to After must be a single cell within the search range.

A single call returns one matching cell, not every match. To collect multiple matches, store the address of the first result, use FindNext to continue, and stop when the search returns to that first address. A loop should also handle the case where the first Find returns Nothing; otherwise, trying to read an address from a missing result causes an error. Excel’s search wraps around at the end of the range, so the first-address check is important to avoid an endless loop.

For a one-off search, Find is usually concise and efficient. For more complicated rules—such as comparing normalized text, applying several conditions, or handling values of different types—a loop through the cells can be easier to control. In that approach, write the comparison rule explicitly and decide how to handle blank cells and errors rather than assuming they behave like ordinary text.

Using the Find dialog to check the same idea

You can verify the whole-cell concept without running VBA. In Excel for Microsoft 365 on Windows, press Ctrl+F, choose Options in the Find dialog if the extra settings are hidden, and select Match entire cell contents. The ribbon route is Home > Find & Select > Find. On Mac, use Command+F to open Find; the dialog also provides search options. The exact dialog layout can vary by platform, but the whole-cell option expresses the same idea as LookAt:=xlWhole.

This manual check is useful when a macro unexpectedly finds a result or misses one. Confirm the search range, the whole-cell option, case sensitivity, and whether the target is a formula or a value. Then make the VBA arguments match the settings you intended rather than depending on whatever was last used in the dialog.

Common mistakes and practical fixes

Using xlPart accidentally: change it to xlWhole when longer entries must not qualify.
Assuming a match was found: test If foundCell Is Nothing Then before referring to its address, value, or row.
Leaving out optional settings: specify the search behavior you rely on, especially LookIn, MatchCase, and SearchFormat.
Searching the wrong scope: qualify the range with a worksheet, as in ws.Range("A2:A100"), instead of relying on whichever sheet happens to be active.
Expecting case sensitivity by default: explicitly set MatchCase:=True if capitalization must be identical.
Searching for visible text that is not the stored value: check whether the cell contains a formula, number, date, or text with extra spaces, and select the appropriate search approach.

Frequently Asked Questions

Does xlWhole make the search case-sensitive?
No. It controls whether the whole cell contents must match. Set MatchCase:=True separately if uppercase and lowercase letters must be distinguished.

What is the difference between xlWhole and xlPart?
xlWhole looks for a match across the entire cell entry. xlPart can return a cell when the search text appears anywhere within a longer entry.

Why does Find return Nothing when I can see the value?
Check for leading or trailing spaces, a different stored data type, formula-versus-value search settings, case sensitivity, a restricted range, or a saved format criterion. Explicit Find arguments and a closer look at the cell often identify the mismatch.

Can Range.Find return every exact match?
One call returns one result. Use FindNext in a loop to visit additional results, recording the first result’s address and stopping when the search wraps back to it.

Does LookAt:=xlWhole treat an asterisk literally?
Not by itself. Excel Find treats * and ? as wildcards. Escape them with a tilde—~* and ~?—when those characters are part of the literal search text.

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.