Yes. In Excel for Microsoft 365 and supported newer versions, VBA can call XLOOKUP with Application.WorksheetFunction.XLookup. Pass it a value to find, a lookup range, and a return range; for example, a macro can find a product price and write it into another cell.
What XLOOKUP in VBA Does
XLOOKUP is an Excel worksheet function, while VBA is Excel’s programming language for automating tasks. Calling XLOOKUP from a macro combines the two: VBA can choose the search value and ranges, then use the function’s result in a larger process. That can be useful when a workbook needs to look up many records, fill a report, or apply the same lookup each time someone runs a macro.
VBA does not make XLOOKUP available in older Excel releases. The function is supported in Microsoft 365 and Excel 2021 or later versions that include it. Excel 2019 and earlier do not support the worksheet function. A macro that calls it will therefore fail in an unsupported version, even if the VBA code itself is otherwise valid. Excel for the web can calculate XLOOKUP formulas, but it cannot run VBA macros.
There are two different ways to use the function in a workbook. You can put an XLOOKUP formula in a worksheet cell and let Excel recalculate it, or call XLOOKUP from VBA and use the returned value in code. The second option does not automatically create a worksheet formula: the macro can write a value to a cell, or it can write a formula if that is what the code is designed to do.
A Basic VBA XLOOKUP Example
Suppose the Products sheet has product IDs in A2:A100 and prices in C2:C100. The Orders sheet has the ID to search for in A2, and you want the matching price in B2. The following macro does that, returning the text “Not found” if the ID is absent.
Sub FindProductPrice()
Dim wsOrders As Worksheet
Dim wsProducts As Worksheet
Dim lookupValue As Variant
Dim result As Variant
Set wsOrders = ThisWorkbook.Worksheets("Orders")
Set wsProducts = ThisWorkbook.Worksheets("Products")
lookupValue = wsOrders.Range("A2").Value
result = Application.WorksheetFunction.XLookup( _
lookupValue, _
wsProducts.Range("A2:A100"), _
wsProducts.Range("C2:C100"), _
"Not found", _
0)
wsOrders.Range("B2").Value = result
End Sub
The first argument is the lookup value. The second is the range to search; the third is the range from which to return a result. The fourth argument supplies a custom result when there is no match. The fifth argument, 0, requests an exact match. The lookup and return ranges should correspond row by row and have compatible dimensions.
To run it on Windows, open the Visual Basic Editor with Alt+F11, insert a standard module using Insert > Module, and paste the code there. Return to Excel and press Alt+F8 to choose FindProductPrice and run it. On a Mac, Option+F11 opens the editor on many keyboards, sometimes with the Fn key as well; the exact shortcut can depend on the keyboard and macOS settings. The VBA editor is not available in Excel for the web.
Using an XLOOKUP Formula Instead
If the goal is simply to show a changing result in a cell, a worksheet formula is often simpler than a macro. With a product ID in Orders!A2, IDs in Products!A2:A100, and prices in Products!C2:C100, enter this formula in Orders!B2:
=XLOOKUP(A2,Products!A2:A100,Products!C2:C100,"Not found",0)
When the ID in A2 matches a value in the product list, the example returns the corresponding price from column C. If the ID is absent, it returns “Not found.” Unlike a macro that writes a result as a value, the formula recalculates when the lookup value or source data changes. This is generally preferable for a live worksheet result; VBA is more helpful when lookup is one step in a broader automated task.
A formula can also be easier to inspect and maintain because the lookup logic is visible in the cell. A macro may be appropriate when users need a button-driven action, when results must be copied as fixed values, or when the lookup is part of a sequence of data-cleaning or reporting steps. Avoid adding VBA solely to reproduce a formula that already works well in the sheet.
Exact Matches, Missing Values, and Search Options
XLOOKUP uses exact matching by default. In the macro example, the explicit match-mode argument 0 makes that choice clear. Other match modes support wildcard matching or nearest-match behavior, but they should be selected deliberately. For example, approximate matching is useful for tiered ranges only when the source data is arranged and interpreted correctly; it is not a general replacement for an exact ID search.
The if_not_found argument is particularly useful in VBA. Without it, a missing match produces an #N/A result, which can complicate later steps. Supplying “Not found” gives the macro a predictable text result it can test or display. Choose a fallback that cannot be mistaken for valid source data. If later code expects a number, for example, a text fallback may need to be checked before performing arithmetic.
XLOOKUP can search from the first item to the last by default, or search in reverse when given a reverse search mode. Reverse search can return the most recent matching record when records are ordered by date or sequence. It does not itself sort the data or verify that the last row represents the newest record, so confirm the source order before relying on that interpretation. For many VBA tasks, the standard first-to-last search is the clearest choice.
Handling Errors and Unexpected Results
A missing match is not the only possible problem. A misspelled worksheet name, a deleted range, an invalid workbook reference, or an error value in the source data can also prevent a macro from completing. The if_not_found argument deals with a normal unsuccessful search; it does not repair broken references or guarantee that every source value is valid.
For a macro used repeatedly, add error handling so a runtime problem produces a useful message instead of stopping without explanation. Here is a compact version of the earlier example with a handler. It reports an execution error, while an ordinary missing ID still returns “Not found.”
Sub FindProductPriceWithErrorHandling()
Dim wsOrders As Worksheet
Dim wsProducts As Worksheet
Dim lookupValue As Variant
Dim result As Variant
On Error GoTo LookupError
Set wsOrders = ThisWorkbook.Worksheets("Orders")
Set wsProducts = ThisWorkbook.Worksheets("Products")
lookupValue = wsOrders.Range("A2").Value
result = Application.WorksheetFunction.XLookup( _
lookupValue, _
wsProducts.Range("A2:A100"), _
wsProducts.Range("C2:C100"), _
"Not found", _
0)
wsOrders.Range("B2").Value = result
Exit Sub
LookupError:
MsgBox "XLOOKUP could not run: " & Err.Description, vbExclamation
End Sub
Error handling should not be used to conceal a workbook problem. A message that includes the error description helps identify whether the issue is a missing sheet, a bad reference, or another failure. If the macro processes many rows, it is also worth deciding how it should behave when one row fails: stop and report the row, mark that row, or continue while recording errors.
Returning Multiple Results and Choosing Ranges
XLOOKUP can return more than one column when its return array contains multiple columns. In a worksheet formula, the results can spill into neighboring cells when those cells are empty and the Excel version supports dynamic arrays. VBA code needs a little more care: a result assigned to a Variant may be an array rather than a single value, and writing an array to a range requires a matching destination area. The single-cell example avoids that extra complexity.
For a macro that searches many rows, avoid assuming that the first match is always the intended record. If an ID appears more than once, ordinary XLOOKUP returns the first match in its search direction. Decide whether duplicates are valid, whether the latest record is required, or whether the data should be checked for duplicates before lookup. A lookup function returns a result; it does not validate the business rules behind the data.
Fixed ranges such as A2:A100 are easy to understand but must be expanded when the list grows. An Excel table can provide structured, expanding source data, though references to table columns in VBA have their own syntax. Whichever approach you choose, keep lookup and return ranges aligned, and avoid including headers in one range but not the other.
Compatibility, Saving, and Macro Safety
Save a workbook containing VBA as an Excel Macro-Enabled Workbook (.xlsm) if you want to retain its macro code. A standard .xlsx file does not preserve VBA projects when saved in that format. If you share the workbook, confirm that recipients have a compatible desktop version of Excel and understand that they may need to allow the macro to run under their organization’s security policy.
Only enable macros from sources you trust. A macro can change workbook contents and perform other actions, so inspect unfamiliar code rather than enabling it just to access a file. When adding or testing a macro, save a separate copy first: macro changes cannot be undone with Ctrl+Z. This precaution matters even for a small lookup macro, because a changed range or destination could overwrite data.
Version compatibility is also important when a workbook moves between computers. A workbook can contain the VBA code even on a computer without XLOOKUP, but calling that function there will fail. If the workbook must work with older Excel versions, use a compatible alternative lookup approach or provide separate code for those versions rather than assuming the function exists.
Frequently Asked Questions
Can I use XLOOKUP in VBA?
Yes, in Excel versions that support the function. A common approach is Application.WorksheetFunction.XLookup, passing the lookup value and the lookup and return ranges. Older Excel releases that lack XLOOKUP cannot run that call successfully.
Does VBA XLOOKUP return a formula or a value?
The call returns a result to VBA. In the examples above, the macro assigns that result to a cell as a value. If you want a live formula in the cell, put an XLOOKUP formula there or write a formula from VBA instead.
How do I stop VBA XLOOKUP from returning #N/A?
Pass a fallback value as XLOOKUP’s fourth argument, such as “Not found.” That handles a search with no match. It does not prevent errors caused by invalid references, incompatible Excel versions, or other problems.
Can I use XLOOKUP in Excel for the web?
Excel for the web supports worksheet XLOOKUP formulas, but it does not run VBA macros. To run a macro that calls XLOOKUP, use a compatible desktop version of Excel.
Why does my VBA code say XLookup is not a method or member?
Check that the installed Excel version supports XLOOKUP and that the code is running in Excel, not another spreadsheet program. A version without the function may not recognize the method. Also check spelling and confirm that the call uses the Excel WorksheetFunction object.
Related
- Conditional Formatting Greater Than: Excel Rules Explained
- Scatter Plot Data Sheet: How to Set Up and Read One
- How to Merge Cells in Zoho Sheet Without Losing Data
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA Delete Whole Row: Safe Methods and ExamplesTo delete an entire worksheet row with VBA, use Rows(row_number).Delete, such as Rows(5).Delete for row 5. In a…
- VBA Excel Send Email in New Outlook: Options That WorkNot directly: Excel VBA’s traditional Outlook automation uses the Outlook COM object model, which New Outlook for Windows…
- Excel VBA Get Array Dimensions: Bounds, Rank, and SizeIn Excel VBA, use LBound(array, dimension) and UBound(array, dimension) to get an array’s lower and upper bounds; its…
- Excel UserForm: Change Font Size at Design Time or in VBATo change an Excel VBA UserForm’s font size, select the form or a control in the Visual Basic…
- TinyTask Macro Recorder: How It Works and When to Use ItTinyTask is a lightweight Windows macro recorder that captures mouse movements, clicks, and keyboard actions, then replays them…
- Excel VBA UserForm Progress Bar: Build One That WorksTo add an Excel VBA UserForm progress bar, create a UserForm with a Frame and a colored Label…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.