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.
This seems to work for VLookup – including when referencing tables or plain ranges, and also when the source range is on a different sheet.
Nowhere near 100% tested for all use cases though! And has no error handling.
FYI one way of getting this to run, in addition to the ideas mentioned, is to set it to the double-click event on the required range.
Dim findString As String
Dim searchRange As Range
Dim searchColumn As Long
Dim findRow As Long
Dim findRange As Range
findString = Evaluate(ActiveCell.Formula)
arr = Split(ActiveCell.Formula, “,”)
searchColumn = Replace(arr(2), “)”, vbNullString) ‘Needed in case VLookup does not include optional final argument
Set searchRange = Range(arr(1)).Columns(searchColumn)
findRow = Application.Match(findString, searchRange, 0)
Set findRange = searchRange.Cells(findRow, 1)
Awesome! Thanks for posting Duncan! 🙂
This macro doesn’t seem to work if your lookup info is not in the same workbook…is there a fix for that?
Great point! It can be modified to work when the source is in another workbook. I’ll add it to the to-do list. 🙂
And this is the reason that I still use old and good Index-Match and disabled “Allow editing directly in cells” instead of new and advanced Xlookup.
Sorry, I’m not sure I understand your reasoning there?
Hi Jon.. I just posted this on the YT video and thought I would drop it here too. Works for tables and ranges and with VLOOKUP, XLOOKUP and INDEX/MATCH. Many thanks to you and your VBA course which helped me build the skills to be able to create this kind of solution. Let me know what you think! Cheers :))
Dim WhatToFind As String
Dim WhereToFindIt As String
Dim arr As Variant
Dim i As Integer
On Error GoTo EH
WhatToFind = ActiveCell.Value
WhereToFindIt = Replace(ActiveCell.Formula, “)”, “”)
arr = Split(WhereToFindIt, “,”)
If InStr(arr(0), “VLOOKUP”) > 0 Then
i = 1
ElseIf InStr(arr(0), “XLOOKUP”) > 0 Then
i = 2
i = 0
arr(0) = Replace(arr(0), “=INDEX(“, “”)
MsgBox “Whoops.. something went wrong. Make sure you start in the right place and try again!”, vbCritical
Wayne, I’m testing your macro, and it works well, but only in the active workbook. I’d like to expand it to work with a lookup to a different workbook. In my initial test, it seemed to select the correct value in my different workbook, but also triggered the error trapper. Any ideas?
Hi Jomili.. thanks for your comment. I’m not sure what problem you are having. It works for me if the data is in a separate workbook. Try this: 1. Open a clean copy of Jon’s workbook and copy my code into a module; note that you may have to correct the quotation marks (i.e. “) in a variety of places, as a copy/paste from a web/blog site may paste in quote marks not recognized by the VBE. Make sure that in the Split function, the replacement character is “,” (no spaces); 2. Assign my macro to the macro button on Jon’s Prospects worksheet; 3. Right click on Jon’s Customer Data worksheet and select Move or Copy and move the worksheet to a new book; 4. Click back to Jon’s workbook and make sure the cell pointer is in the correct starting cell in column B of Jon’s Prospects table on one of the email addresses; 5. Click the button.. it should jump you to the book where you moved the Customer Data worksheet in just the same way as if it were in the same workbook. Obviously, the second book needs to be open. If this is not happening for you.. not sure what to tell you.. it works for me. Maybe email it to Jon or if you want to send me a link to download your example, I could try and figure it out for you. I hope this helps. Good luck!
Also, if you are trying to get this to work from the VBE window using F5 or F8 to initiate the macro, it will work, except the new book/sheet will not be activated. The pointer will be on that workbook/worksheet and it will be the active cell, but for some reason switching to the new book does not happen when running from the VBE window. Probably has to do with returning control back to the VBA at the end of the macro which does not update the window view. When you run it from the button, you will jump to the new book / sheet. Hope this helps. Good luck!
Great little tool, thanks for sharing and will come in very handy.
I have lots of older spreadsheets that use Vlookup’s in their formulas so would be really handy to have a Vlookup version if at all possible?
Is there a formula in VBA or a way that Macros that can operate like XLookup? I am trying to compile a large amount of data in a table whose primary key is the client name. I am trying to create dependent drop down list of products which is based on the client that is selected. I have used Index Match, but realized it couldn’t hold as many arguments as I thought and XLookup is more specific than I thought, working only for one client at a time. I was wondering if VBA or Macros has a function like XLookup and would be able to pull the product list data and output it into a list? Any advice would be appreciated!
The macro in downloadable file above, doesn’t work in structured tables… and no other download available as mentioned –
“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.”
Hi. I love your videos. I just want to know how to use xlookup when you try to lookup the name of the supervisor of an employee when the supervisor changes over time, like weekly changes.