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.

Please Login or Register  to view this content.

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

That was problem 1. It gives xlookup unable to get property of worksheet function class.

It is 365 yes.

I test here on my two workbooks, ensured these weren't csv files, and also did it on another copy of the same workbooks. The debuggers till throws that error that it can't use xlookup property???