Execute Xlookup in VBA

Question

I want to execute the Xlookup function from VBA without writing the formula in an Excel sheet.

This returns:

Runtime error 1004: Unable to get the Xlookup property of the worksheetfunction class

Sub searching()

Dim strSearched As String
Dim rngSearch As Range
Dim rngRenurned As Range

strSearched = "FSATA"
Set rngSearch = Sheets("asheet").Range("C:C")
Set rngRenurned = Sheets("asheet").Range("B:B")
MsgBox Application.WorksheetFunction.XLookup(strSearched, rngSearch, rngRenurned)

End Sub

Could it be a reference problem? I wanted to collect information from the Microsoft learn website but it seems Xlookup doesn't have a page.

Answer 1

Once you know that your Excel version supports XLOOKUP, you still can get the error 1004, if nothing can be found. Two simple things to do about it:

1. Provide a 4th argument as suggested by VBasic2008:

MsgBox Application.WorksheetFunctionXLookup(strSearched, rngSearch, rngRenurned, "Not found")

2. Ignore the error for that line. Note however that you will see no message box in case nothing is found:

On Error Resume Next
MsgBox Application.WorksheetFunction.XLookup(strSearched, rngSearch, rngRenurned)
On Error GoTo 0