Xlookup returns "-" but not the return array's value

Definitions of variables:

Book1 (main file where we will store output values from xlookup): Cell "A3" = LookUpVal
Book 2 (source data): Col G = RetArr; Col A = SrcArr

Problem 1:

Without using address property with search and return arrays, in the arguments of Xlookup, it doesn't even work (Error: unable to get Xlookup property of worksheet function class).

Problem 2:

The return value in book 1 is empty i.e. "-" as supplied in xlookup argument.

What I have tried:


1. Without address and with address in Xlookup

2. Checked values in srcArr, LookupVal and retArr to be correct. This was check through srcArr.select as well as looping through debug.print cells value. So I know it is selecting the right ranges, and values.

3. Checked workbook values, and they are correct alongside sheet names.

Sub XlookMultipleWorkbooks()

Dim lookupVal As Range, srcArr As Range, retArr As Range
Dim Book1 As Workbook, Book2 As Workbook
Dim Book1Rows As Long, Book2Rows As Long

Application.ScreenUpdating = False

With Application.FileDialog(msoFileDialogFilePicker)
    .ButtonName = "Select One File"
    If .Show = -1 Then
        f = .SelectedItems(1)
    End If
End With

'Set some Workbook variables:

Set Book1 = ThisWorkbook 'CHANGE THIS
Set Book2 = Workbooks.Open(f)

'Count rows on both workbooks

Book1Rows = Book1.ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
Book2Rows = Book2.ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row

'Set range to set for lookup range (note this is only for column G now!)

Set lookupVal = Book1.Sheets("Test").Cells(3, 1)

With Book2.ActiveSheet
    Set srcArr = .Range(Cells(3, 1), Cells(Book2Rows, 1))
    Set retArr = .Range(Cells(1, 7), Cells(Book2Rows, 7))
End With

Book1.Sheets("Test").Cells(3, 2).Value = Application.WorksheetFunction.XLookup _
(lookupVal, srcArr.Address, retArr.Address, "-") 'This only returns "-" in Cells(3,2)? It should return 564 quantity.
End Sub

If I can past the hurdle of actually getting return values from the search array, then I will be on my way to loop through each lookup value and put it inside test worksheet.

Due to company policy any workbook upload is blocked but I want Xlookup to read from yellow columns essentially. I am testing this code on cols A and G only.

XlookUpToSave.png

ReturnArray.png