Go To Source Cell of XLOOKUP Formula

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

Watch on YouTube & Subscribe to our Channel

Download the Excel File

You can download the example Excel file that contains the macros below.

XLOOKUP Go To Source Macro.xlsm (129.5 KB)

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?

Jump to the source of an XLOOKUP formula with a macro
Click to Enlarge

This was a question from Lucy, a member of our Elevate Excel Training Program.

Question from Lucy on Editing Lookup Formula Results 609

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.

XLOOKUP Formula Go to Source to Make Changes

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.

Macro selects the source cell that is the result of the XLOOKUP formula in Excel

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.

XLOOKUP Jump to Source Result Macro for Address List
Click to Enlarge

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.

rResult.Parent.Select

Then select the cell that is the result of the XLOOKUP.

rResult.Select

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.

XLOOKUP Go to Source Macro code

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:

  1. 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.
  2. 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.
  3. 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.

Insert a Shape

To link a macro to your shape, just right-click on the shape and select Assign Macro.

Assign a macro to a shape

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.

Conclusion

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.

    Sub VLookUp_Select()

    Dim findString As String
    Dim searchRange As Range
    Dim searchColumn As Long
    Dim findRow As Long
    Dim findRange As Range
    Dim arr

    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)
    findRange.Parent.Activate
    findRange.Select

    End Sub

  • 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.

  • 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 :))

    Sub GotoLookupSource()
    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
    Else
    i = 0
    arr(0) = Replace(arr(0), “=INDEX(“, “”)
    End If
    Application.Goto Reference:=Range(arr(i))
    Selection.Find(What:=WhatToFind).Select
    Exit Sub
    EH:
    MsgBox “Whoops.. something went wrong. Make sure you start in the right place and try again!”, vbCritical
    End Sub

    • 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!

  • Hi Jon
    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?

  • Search
    Generic filters
    Exact matches only

    JOIN US & LEARN EXCEL

    Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??"
    Plus weekly updates to help you learn Excel.

    Download the eBook

    Excel Shortcuts List

    keyboard shortcuts list banner

    Learn over 270 Excel keyboard & mouse shortcuts for Windows & Mac.

    Excel Shortcuts List

    Join Our Weekly Newsletter

    The Excel Pro Tips Newsletter is packed with tips & techniques to help you master Excel.

    Join Our Free Newsletter

    About Me

    Jon Acampora Profile

    Hello and welcome! My name is Jon Acampora and I'm here to help you learn Excel.

    This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Subscribe above to stay updated. More about me...

    MVP_Horizontal_BlueOnly

    Free Excel Training Webinar Modern Power Tools

    >