Excel VBA Find LookIn: What It Means and How to Use It

In Excel VBA, LookIn is an argument of the Range.Find method that tells Excel what kind of content to search: use xlValues to search calculated cell values, or xlFormulas to search formula text. Specify it explicitly in every search, because Excel remembers Find settings and may reuse settings from the Find dialog or an earlier VBA search.

What LookIn means in Excel VBA

LookIn does not name a separate VBA function. It is one of the named arguments in Range.Find, the method VBA uses to search a range. Its value determines whether Excel looks at cell values, formulas, notes, or comments. For ordinary worksheet searches, the main choice is between xlValues and xlFormulas.

The difference matters when a cell contains a formula. Suppose B2 contains =A2&" North" and displays Maple North. Searching for the text North with xlValues checks the calculated result, while searching with xlFormulas checks the formula expression. The latter can find text that is part of the formula even if that text is not present in the current displayed result.

If a cell contains a typed-in constant rather than a formula, both search modes can find its contents. For example, a cell containing the literal text Maple North is not a formula, so there is no formula expression to distinguish from its value. Choose the mode based on the kind of content you intend to search, not simply on whether the cell looks like text.

Choosing xlValues or xlFormulas

Use xlValues when the question is about what the cell currently evaluates to: a displayed status, a calculated label, or a formula result. Use xlFormulas when you need to inspect the formula itself, such as finding formulas that refer to a particular sheet or contain a specific text fragment. A search of formula text is not the same as evaluating the formula’s result.

xlValues: searches cell values, including the calculated results of formulas.
xlFormulas: searches formula expressions as well as constant cell contents.
xlNotes or the relevant comments option: searches note or comment content where supported by the Excel version.

Excel distinguishes legacy Notes from modern threaded comments. The available comment-related LookIn constants depend on the Excel version; newer Excel versions include xlCommentsThreaded for threaded comments. If code must run in older installations, avoid assuming that a newer constant exists there. For a routine search of cell contents, use xlValues or xlFormulas instead.

A reliable basic Find example

This macro searches the values in A2:A100 on a worksheet named Data and reports the first exact match for the text Pending. It sets the other important Find options as well, so the result does not depend on choices left over from Excel’s Find dialog.

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

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

    Set foundCell = searchRange.Find( _
        What:="Pending", _
        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 matching value was found."
    Else
        MsgBox "Found at " & foundCell.Address(False, False)
    End If
End Sub

Before running any macro, save a copy of the workbook. VBA actions generally cannot be reversed with Ctrl+Z, even if a later version of the macro changes cells rather than merely searching them. This example only searches and displays a message, but saving a copy is a good precaution for macros generally.

The range and worksheet names in the example are assumptions, not special Excel requirements. Change them to match the workbook. ThisWorkbook means the workbook containing the macro; use a different workbook reference if the data is in another open file. The check for Nothing is essential: Find returns no range object when it cannot find a match.

What the other Find arguments control

LookIn is only one part of a search. The other arguments determine what counts as a match, where searching starts, and how the search proceeds. Setting them deliberately makes a macro more predictable and easier to maintain.

What is the text, number, or other content to find.
LookAt:=xlWhole requires the entire cell content to match; LookAt:=xlPart finds the target anywhere within it.
SearchOrder:=xlByRows searches across rows before moving down; xlByColumns searches down columns before moving across.
SearchDirection:=xlNext searches forward, while xlPrevious searches backward.
MatchCase:=True makes a text search case-sensitive; False does not.
SearchFormat:=False prevents a saved formatting criterion from affecting the search.

After identifies the single cell after which the search begins. Excel searches after that cell and eventually wraps around to the beginning of the range. It must be a cell in the range being searched. The example sets it to the last cell so the first cell in the range is considered promptly; choosing a different starting cell changes the order in which matches are encountered, not the set of cells in the range.

Finding every match with FindNext

Find returns only one match. To process every match, save the address of the first result, then call FindNext until the search returns to that address. Recording the starting address prevents an endless loop because Excel wraps back to the beginning after reaching the end of the range.

Sub ListPendingValues()
    Dim ws As Worksheet
    Dim searchRange As Range
    Dim foundCell As Range
    Dim firstAddress As String

    Set ws = ThisWorkbook.Worksheets("Data")
    Set searchRange = ws.Range("A2:A100")

    Set foundCell = searchRange.Find( _
        What:="Pending", _
        After:=searchRange.Cells(searchRange.Cells.Count), _
        LookIn:=xlValues, _
        LookAt:=xlWhole, _
        SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, _
        MatchCase:=False, _
        SearchFormat:=False)

    If foundCell Is Nothing Then Exit Sub

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

In this example, matching addresses are written to the Immediate window in the VBA editor, which you can open with Ctrl+G on Windows. This is a useful way to inspect results without modifying the worksheet. To perform an action on each result, put that action inside the loop and keep the wraparound check. If the macro edits cells, remember that Excel’s normal undo history will not restore those changes.

Why Find sometimes returns an unexpected result

A frequent cause of confusing results is omitted arguments. Excel retains several Find settings between calls and shares them with the Find dialog. A person may have last searched formulas, partial matches, or a particular format in the user interface; a macro that leaves those arguments unspecified can behave differently than expected. Supplying LookIn, LookAt, search order, direction, case, and format options makes the intent clear.

Other details can also affect a match:

Whole versus partial match: xlWhole will not match Pending review when searching for Pending; xlPart can.
Wildcards: Excel Find supports * for any sequence of characters and ? for one character. A tilde escapes a wildcard when you need to search for the character itself.
Formatting: A search can be constrained by formatting if a saved Find format is active. Setting SearchFormat:=False avoids carrying that criterion into the macro.
Numbers and dates: Excel stores dates as serial numbers and may display values using formats. A displayed date or rounded number is not necessarily the underlying value being searched.
Errors and blanks: Decide what the range and search target should include. A blank cell is not the same as a cell containing a formula that returns an empty string.

When searching formulas, remember that a formula can contain a word without returning that word, and it can return a word without containing that word as a literal. For example, a formula may look up a status from another cell. If the goal is to identify currently displayed statuses, search values; if the goal is to locate formula definitions, search formulas.

Using Find from Excel on Windows and Mac

The VBA method works in desktop Excel for Microsoft 365 on Windows and is also available in desktop Excel for Mac, although the editor access and some platform details differ. On Windows, open the VBA editor with Alt+F11 or use Developer > Visual Basic. On Mac, Option+F11 may open the editor; some keyboards require Fn as well. The Developer tab can be enabled in Excel’s ribbon settings if it is not visible.

For a manual search, Ctrl+F opens Find on Windows, while Command+F is the usual Mac shortcut. The dialog’s settings are one reason VBA code should specify its own Find arguments: the dialog and VBA share remembered search choices. Excel for the web does not run VBA macros, so use desktop Excel to execute this code. A workbook containing macros should be saved in a macro-enabled format, such as .xlsm, if the VBA code needs to be retained.

When to use Find instead of another method

Range.Find is convenient when you need to locate one or more matching cells, inspect their addresses, or take an action on each match. It avoids checking every cell yourself and supports options such as whole-cell matches, case sensitivity, search direction, and formula-versus-value searching.

For a fixed, small range, a loop through cells can be easier to understand when the matching rule is more complex than a text search. For worksheet calculations, functions such as XLOOKUP or MATCH may be a better fit when you want a result in a cell rather than VBA control over the matching cell. XLOOKUP is available in Microsoft 365 and Excel 2021 or later; older Excel versions may require alternatives. These approaches answer different needs: a worksheet function returns a calculation, while VBA Find returns a cell reference that your macro can inspect or act upon.

Frequently Asked Questions

Should I use xlValues or xlFormulas?

Use xlValues to search a cell’s current value, including a formula’s calculated result. Use xlFormulas to search formula expressions and constants. Choose based on whether you want to find what a cell evaluates to or what its formula contains.

What does LookIn:=xlValues mean?

It tells Range.Find to search cell values rather than formula text. A formula cell is searched according to its calculated result, which is useful when locating statuses, labels, or other outputs currently shown by formulas.

Why does VBA Find return Nothing?

Find returns Nothing if it finds no match in the specified range under the selected settings. Check that the range, search term, LookIn, and LookAt match your goal, and test the result with If foundCell Is Nothing Then before using it.

Does LookIn search what is visibly displayed in a cell?

Not necessarily in the sense of matching formatted screen text character for character. Values and formulas refer to cell content, while number formats can change how numbers and dates appear. If a search depends on a particular display format, verify the behavior against representative cells rather than assuming the displayed characters are the stored value.

Why does Find behave differently after I use Ctrl+F?

Excel remembers Find settings, and VBA’s Find method shares several of those settings with the dialog. Include all relevant arguments in each call, especially LookIn, LookAt, and SearchFormat, so prior manual searches do not silently change the macro’s behavior.

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.