Bottom Line: Learn how to use a macro to jump to and select the source cell of the results of an XLOOKUP formula.
Skill Level: Advanced
Watch the Tutorial
Download the Excel File
You can download the example Excel file that contains the macros below.
Jump to the Source of a Lookup Formula
Have you ever wanted to edit the value that is the result of a lookup formula like XLOOKUP?
This was a question from Lucy, a member of our Elevate Excel Training Program.
Unfortunately, we can't edit the value directly in the cell that contains the formula. And finding that cell can be tedious and time consuming if you are visually scanning the data or using the Find window.
However, we can use a simple macro to quickly jump to the cell that contains the underlying data.
The blue button in the image above runs the macro to jump to the source of the selected XLOOKUP formula.
For example, let's say I want to jump to the source of the very first email in the list above ([email protected]). To do so, I just select that cell and click the macro button. The macro takes me to the source worksheet and selects the cell that contains the email address.
Checkout the video above to see this in action.
Here's another example. This time the data is part of a form. The light blue cells are all populated using XLOOKUP formulas. These cells change whenever a new name is selected in the light orange cell above them.
Maybe someone's phone number changes and we want to update it. We can simply select the phone number and click the button to go right to the source data and make our change.
How the Macro Works
This is a relatively simple macro because the XLOOKUP function returns a range. This happens in the background. We only see the value being returned by the formula in the cell. However, we can use VBA to get the range that the XLOOKUP returns.
The following line of code uses the Evaluate method to calculate the formula in the active cell and set the result to a range variable.
Dim rResult as Range Set rResult = Evaluate(ActiveCell.Formula)
We can then select the sheet that the result is on by using the range's parent property.
Then select the cell that is the result of the XLOOKUP.
The fact that the new XLOOKUP function returns a range is pretty cool, and useful! Unfortunately, older functions like VLOOKUP only return a value. So we cannot use this same macro for VLOOKUP. More on that below.
Here is a screenshot of the entire macro, and you can grab the code by downloading the workbook in the Downloads section above.
I also included an enhanced version of the macro that works when the lookup_value argument in your XLOOKUP contains a structured reference notation like, [@Column Name]. The code injects the table name before the reference, which is needed for the Evaluate method to work properly.
You can use that macro even if your formula doesn't use structured references. It should work in all scenarios.
Ways to Run the Macro
There are several ways to store and run the macro:
- Store the macro in a macro enabled (xlsm) workbook and run it with a macro button. The file can then be sent to other users and they will be able to use the macro.
- Store the macro in your personal macro workbook and run it from a custom ribbon button. The macro can then be run on any file you have open on your computer.
- Store the macro in an add-in with a customized ribbon. You can then deploy the add-in to other users to install and they will be able to run the macro on any workbook they have open.
Here is more info on #1 and #2.
Create a Macro Button
I added a macro button to the sheet that contains the XLOOKUP formulas.
To create a macro button, all you need to do is add a shape to your worksheet and then link it to the macro. Adding the shape is done from the Insert tab.
To link a macro to your shape, just right-click on the shape and select Assign Macro.
That will open up a list of available macros you can assign.
I've included the macro in the Excel file at the top of this post. You can simply copy the macro and insert it into your own workbook.
Add the Macro to Your Personal Macro Workbook & Custom Ribbon
If you work with XLOOKUP frequently, you might want to consider adding this macro to your Personal Macro Workbook. After you do, you can set up a custom button for it in the ribbon. That way you can go to your XLOOKUP source data no matter what worksheet you're using.
I explain the Personal Macro Workbook, how to create one, where it's stored, and how to use it in this tutorial: How to Create a Personal Macro Workbook (Video Series).
And this post details how you can add the macro button to your ribbon: How to Add Macro Buttons to the Excel Ribbon or Quick Access Toolbar.
As I mentioned above, this macro currently only works for XLOOKUP. It doesn't work for VLOOKUP or any other lookup formula.
If a VLOOKUP macro is something you'd like for me to create and explain in a future video, I'd love to hear that feedback in the comments. If you are feeling ambitious and want to create such a macro on your own, share it with everyone in the comments as well.
Hope this was helpful for you! See you next time.