From a value found with XLOOKUP, retrieve the cell's address

[Rubens Sayegh]

Greetings!
I am using VBA in Excel and with the following function...

Dim BZIT as Double
BZIT = Application.WorksheetFunction.XLookup(Cells(LIN, COL).Value, Range("C:C"), Range("G:G"), , 1)

I get the value in cell LIN,COL, find it (or the next one to it) in column C, then jump in the same line, to column G and get the cell's value.
See this example:

Assuming that the cell in position (LIN, COL) has a value of 1.765, then...

	Column C	...	Column G
Line 1	1.000	...	0.222
Line 2	2.000	...	0.345
Line 3	3.000	...	0.658
Line 4	4.000	...	0.999

So, XLOOKUP picks 1.765 and try to match it in column C.
As there's no match, the next number (2.000, in line 2) is choosen.
In the same line, "jumping" to column G, the function returns in BZIT the value 0.345 - Presto! -

But, what I really need is the cell's address (where the 0.345 is).
I've tried using ActiveCell.Address(False, False) but this only returned the address of the cell where the cursor was positioned, and not the address of the result's cell...

Any help would be greatly appreciated.
Thanks in advance.

 

[Alex Blakenburg]

I can't test it at the moment but based on my understanding of how xlookup works, if you put a string data type variable on the left of your equals sign and append ".Address" without quotes to the end of your Xlookup, you should get the address.
PS: now tested and confirmed

 

[Alex Blakenburg]

In case you need it:-

Dim addrBZIT As String
addrBZIT = Application.WorksheetFunction.XLookup(Cells(LIN, COL).Value, Range("C:C"), Range("G:G"), , 1).Address

 

[Rubens Sayegh]

Oh! So simple. Nothing like asking to who really knows! Thanks a lot!

 

[Alex Blakenburg]

No problem. Glad I could help and thanks for the feedback.