Excel VBA Lookup: Find Matching Data with Code

An Excel VBA lookup searches a range for a value and returns related information, such as a product name for a product code. For a straightforward exact-match macro, use VBA’s Range.Find method, specify LookAt:=xlWhole, and check whether the method found a cell before reading its value.

What an Excel VBA lookup does

A lookup connects a search key in one place with related data somewhere else. For example, a worksheet might list product codes in column A and product names in column B. A macro can search column A for a code the user enters and display the name from column B on the matching row.

VBA is useful when the lookup needs to be part of a repeatable action: a button can prompt for a code, fill in several fields, or run a lookup as part of a larger process. A macro can also apply rules that are awkward to express in a single worksheet formula. For a basic lookup that should update automatically as cells change, a worksheet formula is often simpler.

The example below assumes a worksheet named Products, with product codes in A2:A100 and names in B2:B100. It finds the first exact match and displays the corresponding name. Change the sheet name and ranges to fit your workbook.

Build an exact-match lookup with Range.Find

On Windows, open the Visual Basic Editor with Alt+F11, choose Insert > Module, and paste the procedure into the module. The Developer tab’s Visual Basic command opens the same editor. If the Developer tab is hidden, enable it through File > Options > Customize Ribbon. On Mac, use Developer > Visual Basic; the editor shortcut can vary by keyboard, commonly Option+F11 or Fn+Option+F11.

Sub FindProductName()
    ' Search product codes in column A and return the name from column B.
    Dim ws As Worksheet
    Dim searchRange As Range
    Dim foundCell As Range
    Dim lookupValue As String

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

    lookupValue = VBA.InputBox("Enter a product code:", "Product lookup")
    If lookupValue = vbNullString Then Exit Sub

    Set foundCell = searchRange.Find( _
        What:=lookupValue, _
        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 product code was found.", vbInformation
    Else
        MsgBox "Product name: " & CStr(foundCell.Offset(0, 1).Value), vbInformation
    End If
End Sub

Save a copy of the workbook before running a macro. VBA actions cannot be undone with Ctrl+Z. To keep VBA code in an Excel workbook, save it as an Excel Macro-Enabled Workbook (.xlsm), not as a standard .xlsx file. Run the procedure from the Visual Basic Editor with F5, or assign it to a button if people will use the lookup regularly.

How Find works—and why the settings matter

Find searches a range and returns a Range object for the matching cell. If there is no match, it returns Nothing, which is why the example tests If foundCell Is Nothing before accessing the result. The Offset(0, 1) expression means “same row, one column to the right.” With codes in column A and names in B, that returns the name.

Several search options deserve attention:

LookAt:=xlWhole requires the entire cell content to match. Without an explicit whole-cell setting, a search can match part of a longer value.
LookIn:=xlValues searches the values shown in cells, including calculated results. Use xlFormulas when the formula text itself is what you need to search.
MatchCase:=False makes the example’s search case-insensitive. Set it to True if uppercase and lowercase letters must be treated as different.
The code supplies the search options rather than relying on Excel’s remembered Find dialog settings. This matters because some Find settings persist between searches and can affect VBA searches if omitted.

This procedure returns the first match encountered in the search range. If codes should be unique, that is normally appropriate. If duplicate codes are possible, decide what the macro should do: report duplicates, return the first result, or collect every matching row. Returning the first result without considering duplicates can conceal inconsistent source data.

Choose between VBA and a worksheet formula

Use VBA when the lookup is part of a button-driven workflow, needs to populate several cells, or must perform additional actions after finding a record. Use a formula when users should see the result in a cell and have it recalculate as the source data or lookup key changes. Formulas are also easier to inspect without opening the VBA Editor.

For a formula alternative, assume lookup values are in A2:A100, return values are in B2:B100, and the search code is in E2. In Microsoft 365 for Windows, this exact-match formula returns the related value or the text “Not found”:

=XLOOKUP(E2,A2:A100,B2:B100,"Not found",0)

If E2 contains P-104 and A5 contains P-104 while B5 contains Desk Lamp, the formula returns Desk Lamp. XLOOKUP is available in Microsoft 365 and Excel 2021 or later. If a workbook must support older Excel releases, consider VLOOKUP or INDEX with MATCH instead; VLOOKUP requires the lookup column to be the first column in its table range. A macro does not make a lookup inherently more accurate: the important choices are the match rule, the source data, and how missing or repeated keys are handled.

Common lookup problems and fixes

No match despite an apparent match: Check for leading or trailing spaces, different data types, or characters that look alike but differ. A number stored as text may not behave like a numeric value. Inspect the underlying cells and standardize the source values if necessary.
The wrong result appears: Confirm the return column is the one intended. In the sample, Offset(0, 1) means one column to the right of the found cell; it would return a different field if the layout changes.
A partial match is returned: Keep LookAt:=xlWhole for an exact lookup. For instance, an exact search for AB-12 should not treat AB-123 as a match.
The macro reports a missing item after rows are added: The sample searches only A2:A100. Expand that range or calculate the last used row before searching when the list grows. A fixed range is easy to understand, but it must be maintained.
The macro cannot find the worksheet: The sheet name must match Products exactly. Also note that ThisWorkbook refers to the workbook containing the macro, which can differ from the workbook currently active on screen.

If codes may contain leading zeros, store and handle them consistently as text. Excel can remove leading zeros when it interprets an entry as a number, so a code such as 0017 may become 17 before the lookup runs. Formatting a cell to display leading zeros is not always the same as storing the zeros as part of the text value.

Using a lookup with larger or changing lists

A short fixed range is suitable for a small, stable example. For a growing list, a better design is to make the data an Excel table and have the macro locate its data range or use the table’s named columns. Tables expand as records are added, reducing the chance that a new row falls outside the search. This also makes the workbook’s structure clearer than relying on a large arbitrary range.

For very large datasets, avoid repeatedly searching the same column inside a loop if one search or a more structured approach will do. If a macro must process thousands of lookup keys, it may be faster to read data into arrays or build a dictionary of keys and results, then reuse that in-memory index. That approach adds complexity: the code must account for duplicate keys, key types, and how the dictionary is created. For a single user-entered value, Find is generally easier to read and maintain.

It is also worth separating the lookup from the action taken after it. First establish that a key exists; then read or write the related fields. This makes missing-key behavior explicit and reduces the risk of a macro writing to the wrong row. If the macro changes worksheet values, describe those changes clearly to users and test it on a copy before using it on important data.

Macro security and platform notes

VBA macros can change workbook contents and interact with files, so only enable macros in workbooks you trust. Do not lower Excel’s macro security setting simply to make an unknown file run. For a workbook shared with colleagues, explain what the macro does and use an approved, trusted distribution method.

Excel for Mac supports VBA, but Windows-specific components such as some ActiveX controls and COM automation are not available in the same way on Mac. The lookup procedure shown here uses ordinary worksheet objects and Range.Find, rather than a Windows-only automation component. Excel for the web can open macro-enabled workbooks, but it cannot create, edit, or run VBA macros; use the desktop Excel application to run this code.

Frequently Asked Questions

Can VBA return a value from a lookup into a cell?
Yes. After checking that foundCell is not Nothing, assign the value from the matching row to a destination cell. Choose the destination carefully so the macro does not overwrite source data.

How do I make an Excel VBA lookup exact?
Set LookAt:=xlWhole in the Find call. Also set the other relevant options explicitly, particularly LookIn and MatchCase, so the result does not depend on remembered Find dialog settings.

What happens if there are duplicate lookup values?
The sample returns the first matching cell it encounters. If duplicates are possible, decide whether the first result is acceptable or write additional logic to identify and handle every match.

Can I use VBA lookup code in Excel for the web?
No. Excel for the web does not run VBA macros. Open the workbook in the desktop version of Excel to run the macro; a worksheet formula may be a better option when browser-based use is required.

Why does Find return Nothing when the value is visible?
Check whether the search range includes that cell, whether the stored value differs from what is displayed, and whether spaces or text-versus-number differences are involved. Also verify that the macro is searching the intended worksheet and workbook.

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.