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

Rubens Sayegh

New Member
Joined
Sep 4, 2021
Messages
2
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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 11.000...0.222
Line 22.000...0.345
Line 33.000...0.658
Line 44.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.
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
2,644
Office Version
  1. 365
Platform
  1. Windows
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
 
Last edited:

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
2,644
Office Version
  1. 365
Platform
  1. Windows
In case you need it:-
VBA Code:
Dim addrBZIT As String
addrBZIT = Application.WorksheetFunction.XLookup(Cells(LIN, COL).Value, Range("C:C"), Range("G:G"), , 1).Address
 

Rubens Sayegh

New Member
Joined
Sep 4, 2021
Messages
2
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Oh! So simple. Nothing like asking to who really knows! Thanks a lot!
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
2,644
Office Version
  1. 365
Platform
  1. Windows
No problem. Glad I could help and thanks for the feedback.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,844
Messages
5,766,748
Members
425,378
Latest member
kapoor2892

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top