Excel VBA Find Exact Match: Find Whole-Cell Values

In Excel VBA, set the LookAt argument of Range.Find to xlWhole to match an entire cell rather than a substring. For example, search a range with LookAt:=xlWhole and LookIn:=xlValues; add MatchCase:=True if uppercase and lowercase letters must be treated as different.

What “exact match” means in VBA

Excel’s Find method searches a specified range and returns a Range object for a matching cell, or Nothing if it finds no match. The key setting for a whole-cell match is LookAt:=xlWhole. If you use xlPart instead, Excel can return a cell that contains the search term as only part of its contents. For instance, searching for AB-104 with xlPart could also find AB-104-OLD; xlWhole asks for the complete cell value to match.

“Exact” can mean more than one thing, so it helps to decide what you need. Whole-cell matching controls whether extra characters before or after the term are allowed. Case sensitivity is a separate choice, controlled by MatchCase. The search scope is another: a search of column A does not check column B. Finally, a cell’s underlying value may differ from what its number format displays. Setting xlWhole does not automatically resolve all of those distinctions.

A basic VBA macro for a whole-cell match

This example searches product codes in cells A2:A500 on a worksheet named Inventory. It uses explicit arguments so the search behavior is clear and is not left to remembered Find-dialog settings. Change the worksheet name, range, and search text to fit your workbook.

Sub FindExactProductCode()
    Dim searchRange As Range
    Dim foundCell As Range
    Dim searchText As String

    searchText = "AB-104"
    Set searchRange = Worksheets("Inventory").Range("A2:A500")

    ' Start at the end so the first cell is checked first.
    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

With this setup, a cell containing AB-104 is eligible for a match, while AB-104-OLD is not. Because MatchCase is False, the search does not distinguish between uppercase and lowercase text. If the code’s letter case matters, change that argument to True. The macro reports the address rather than selecting or editing the cell, so you can adapt the result handling without changing the search itself.

Before running any macro, save a separate copy of the workbook. VBA changes cannot be reversed with Ctrl+Z, even if you later add code that edits cells. Save a macro-enabled workbook as an .xlsm file if you need to keep the macro. On Windows, open the Visual Basic Editor with Alt+F11 or use Developer > Visual Basic. On Mac, use Developer > Visual Basic; the keyboard shortcut can vary with the keyboard and function-key settings.

Why the Find arguments matter

Excel’s Find dialog and VBA share search settings. Some arguments can carry over from a previous search, including one someone ran manually in the Find dialog. If a macro omits an argument, it may therefore behave differently depending on the workbook or the user’s last search. Supplying the important arguments explicitly makes the macro more predictable.

What is the text, number, or other value VBA should search for.
LookAt:=xlWhole requires a whole-cell match; xlPart allows a partial match.
LookIn:=xlValues searches cell values, while xlFormulas searches formula contents.
SearchOrder:=xlByRows searches across rows; xlByColumns searches down columns.
SearchDirection:=xlNext moves forward through the search order; xlPrevious moves backward.
MatchCase controls whether text case matters, and SearchFormat controls whether formatting criteria are used.

The sample sets SearchFormat:=False because its purpose is to find a value, not cells with particular formatting. A format-based search can produce confusing results if formatting criteria were previously set. Explicitly disabling format matching avoids that hidden dependency for this kind of value search.

Search values or search formulas?

Choose LookIn based on what the user means by “the cell contents.” With xlValues, the search considers the cell’s value, including the result displayed by a formula. For example, if a formula evaluates to AB-104, a value search can find that result. With xlFormulas, the search examines the formula text itself. That is useful when looking for a particular formula or a reference inside formulas, but it is not usually the right choice for matching the result a reader sees.

Formatting is not the same as the stored value. A numeric cell holding 123 could be displayed with leading zeroes because of its number format. Searching for the displayed string is not necessarily the same as searching for the underlying number. Similarly, dates are stored as numeric values and shown using date formats. If the distinction is important, inspect the cell’s value and number format in your code rather than assuming that Find compares the formatted screen appearance.

There can also be a difference between a number and text that looks like a number. Find is a convenient search tool, but if your requirement is explicitly “match a text value, and never a number,” a loop that checks the cell’s value type before comparing is clearer. Decide whether your rule concerns what the cell stores, what it displays, or the formula that produces its result before choosing the search method.

Case sensitivity and wildcard characters

Find is case-insensitive by default when MatchCase:=False. Thus, searching for north can find NORTH. Set MatchCase:=True when capitalization is meaningful, such as for identifiers that distinguish ab12 from AB12. Case sensitivity does not change the whole-cell rule: use xlWhole as well if extra characters must not be accepted.

Excel Find also recognizes wildcard characters in the search text. An asterisk (*) can represent any number of characters, and a question mark (?) can represent one character. That behavior can surprise someone searching for a literal code such as SKU*: xlWhole alone does not turn the asterisk into an ordinary character. In Find searches, a tilde escapes wildcard characters. Escape any tilde already in the search text first, then escape asterisks and question marks:

searchText = Replace(searchText, "~", "~~")
searchText = Replace(searchText, "*", "~*")
searchText = Replace(searchText, "?", "~?")

Use those lines before calling Find when the search text may contain literal wildcard characters. The first replacement matters because a tilde is itself Excel’s escape character. Without the escaping, a search can match a pattern rather than the literal string the user entered.

Searching a range and handling multiple matches

Find returns one matching cell, not a collection of every match. The range you pass determines where it looks, so use a clearly bounded range when possible. Searching all of column A may be convenient, but a range such as A2:A500 can make the intended data area easier to understand and avoid searching headers or unrelated cells.

To collect every whole-cell match, call Find once and then use FindNext to continue. Store the address of the first result and stop when the search wraps back to it. Excel’s search loops around to the beginning of the range, so a loop without a stopping condition can revisit the same cells indefinitely. Check for Nothing before reading a result’s address, and avoid changing the search range while collecting matches.

For a single match, the basic macro is simpler and safer. For multiple matches, consider what the macro should do with each result: write its address to a report, copy values to another sheet, or apply a change. If the macro modifies cells, be especially careful about the workbook copy and test on a small range first. A change to a matched cell can also affect later searches or the logic that processes the results.

Common reasons an expected match is missed

If the macro returns Nothing unexpectedly, check both the search settings and the actual cell data. A value may look identical on screen but contain a trailing space, a nonbreaking space copied from a web page, or a different character such as a hyphen variant. Whole-cell matching correctly rejects extra characters, even when they are difficult to see.

Confirm the worksheet and range are the ones containing the target. A workbook with multiple sheets named similarly can lead to a search in the wrong place.
Check for leading or trailing spaces. If trimming is part of your business rule, normalize values deliberately rather than silently changing the meaning of every match.
Check whether the target is text, a number, a date, or a formula result, then choose the appropriate LookIn setting.
Review MatchCase and wildcard escaping, especially for codes containing *, ?, or ~.
Set SearchFormat:=False for a content-only search and supply the other Find arguments explicitly.

Do not switch from xlWhole to xlPart just to make a missed result appear unless partial matches are genuinely acceptable. That change can return a different record with a shared prefix or suffix. Instead, inspect the underlying data and define how spaces, capitalization, data types, and punctuation should be handled.

When to use a loop instead of Find

Range.Find is a good fit when you need Excel’s built-in search behavior and want one matching cell quickly. A loop is more suitable when “exact” includes custom rules that Find does not express directly—for example, comparing case sensitively while also requiring a text value, ignoring specified spaces, or logging every comparison for review.

In a loop, test each cell’s value and apply the exact comparison rule you chose. For case-sensitive text comparison, VBA’s StrComp with vbBinaryCompare is one option, provided you first ensure the values are text and handle empty cells appropriately. A loop also makes it easier to normalize values before comparing, but normalization should be intentional: removing spaces or punctuation can cause two distinct identifiers to become indistinguishable.

Worksheet lookup functions can be useful for formula-driven tasks, but they are not a direct substitute for every VBA Find use. For example, a formula may return a matching value or position, while VBA Find returns a cell reference that your macro can inspect or act on. Choose based on the desired result, and keep the whole-cell, case, type, and wildcard requirements explicit whichever approach you use.

Frequently Asked Questions

How do I make VBA Find match the entire cell?
Use LookAt:=xlWhole in the Range.Find call. Set the other arguments explicitly too, especially LookIn, MatchCase, and SearchFormat.

Is VBA Find case-sensitive by default?
Not when MatchCase:=False, which is the usual default behavior. Set MatchCase:=True when uppercase and lowercase letters must differ.

Why does Find return Nothing when the cell looks identical?
The stored contents may include extra spaces or different characters, or the search may be checking formula text instead of values. Also verify the worksheet, search range, case setting, and wildcard characters.

Does xlWhole make an asterisk a literal character?
No. Excel Find treats * and ? as wildcards. Escape them with a tilde in the search text, and double any literal tilde before escaping the wildcard characters.

How can I find every exact match?
Use Find for the first result and FindNext to continue. Remember the first result’s address and stop when the search returns to that address, because Find wraps around the range.

Can I undo a macro that changes cells?
No. Excel’s Ctrl+Z undo history does not reverse VBA macro changes. Save a separate workbook copy before running a macro that edits data, and test changes on a limited range first.

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.