stuck with Xlookup in vba

Question

hello.

I want to execute the Xlookup function from vba without bothering writing the formula in an excel sheet. I think have checked every page google could offer me about the problem but I couldn't find satisfying results.

This for exemple return me: "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 informations about the Microsoft learn website but it seem Xlookup dont have any page on it.

XLOOKUP is not available in Excel 2016 and Excel 2019, however, you may come across a situation of using a workbook in Excel 2016 or Excel 2019 with the XLOOKUP function in it created by someone else using a newer version of Excel.. Check [this](https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929) — Foxfire And Burns And Burns, Mar 01 '23 at 13:46
[XLOOKUP](https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929), only the first hit in Google search. There is a 4th argument whose parameter you can set if no match is found e.g. `.XLookup(strSearched, rngSearch, rngRenurned, "Not found")`. — VBasic2008, Mar 01 '23 at 13:46

score 1 · Accepted Answer · answered Mar 01 '23 at 13:54

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:

Provide a 4th argument as suggested by VBasic2008:

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

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

score 0 · Answer 2 · answered Mar 01 '23 at 14:10

If you don't have XLookUp then an alternative is INDEX-MATCH. The difference in speed is discussed here

Dim v: v = Application.Match(strSearched, rngSearch.Value, 0)
If Not IsError(v) Then
    MsgBox Application.Index(rngRenurned, v)
Else
    MsgBox "'" & strSearched & "' not found", vbExclamation
End If

stuck with Xlookup in vba

2 Answers2