XLOOKUP with HYPERLINK in Excel: Jump to Cell or URL

Published on

XLOOKUP is a modern lookup function in Excel, and when combined with HYPERLINK, it can serve two useful purposes:

  • Jump to a cell where the XLOOKUP result is located.
  • Return a clickable hyperlink that directs to an external website or file.

In this tutorial, you’ll learn how to use XLOOKUP with HYPERLINK in both scenarios with easy-to-follow examples.

In the following example, we have columns for Item, Item Code, and Product Description. Our goal is to look up an item code, return the corresponding item, and jump to the cell containing that item. This allows for quick verification and modification of the product description if needed.

Example: Jump to a cell using XLOOKUP and HYPERLINK in Excel
  1. Enter the lookup value in any cell, for example, BS321 in E2.
  2. Use the following formula in F2 to return the corresponding item name:
    =XLOOKUP(E2, B2:B6, A2:A6)
    This formula searches for E2 in B2:B6 and returns the corresponding product from A2:A6.
  3. Wrap it with the CELL function to get the cell address:
    =CELL("address", XLOOKUP(E2, B2:B6, A2:A6))
  4. Add the “#” sign to create an internal link:
    ="#" & CELL("address", XLOOKUP(E2, B2:B6, A2:A6))
  5. Wrap it with HYPERLINK to generate a clickable link:
    =HYPERLINK("#" & CELL("address", XLOOKUP(E2, B2:B6, A2:A6)))
  6. Optionally, replace the cell address with the product name as the link text:
    =HYPERLINK("#" & CELL("address", XLOOKUP(E2, B2:B6, A2:A6)), XLOOKUP(E2, B2:B6, A2:A6))

This method allows you to jump to the XLOOKUP result cell using HYPERLINK in Excel.

Optimized Formula Using LET

To avoid repeating the XLOOKUP calculation, use the LET function:

=LET(xl, XLOOKUP(E2, B2:B6, A2:A6), HYPERLINK("#"&CELL("address", xl), xl))

When using this formula:

  • Replace E2 with the search key.
  • Replace B2:B6 with your lookup range.
  • Replace A2:A6 with your result range.

If the XLOOKUP result range contains a hyperlink, XLOOKUP loses the link and returns plain text. (As a side note, this is not the case with XLOOKUP in Google Sheets.)

To retain the link, the best approach is to maintain a separate column with the URLs of the external sources.

Sample Table:

Clickable link to a website or file using XLOOKUP and HYPERLINK in Excel
  1. Use the following formula to look up the topic and return the text (without a hyperlink):
    =XLOOKUP(E2, A2:A4, B2:B4)
  2. Instead of the result text, return the URL from the third column:
    =XLOOKUP(E2, A2:A4, C2:C4)
  3. Wrap it with HYPERLINK to create a clickable link:=HYPERLINK(XLOOKUP(E2, A2:A4, C2:C4))

When you click this link, it will open the corresponding webpage in your browser.

Looking Up and Opening Files Stored on Your Computer

If you want to link to a file stored on your computer, specify the file path in the result range instead of a URL. For example:

C:\Users\HP\OneDrive\Pictures\Screenshots\Screenshot 2024-11-24 114433.png

The formula remains the same:

=HYPERLINK(XLOOKUP(E2, A2:A4, C2:C4))

Clicking the link will open the specified file.

Resources

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Merge Duplicate Rows and Keep Latest Values in Excel

Here’s a dynamic array formula approach to merge duplicate rows and keep the latest...

Cycle Highlights in Google Sheets – Rotate Highlights Daily

Want to cycle highlights in Google Sheets every day? Whether you're rotating a meal...

Filter Rows Containing Multiple Selected Values in Google Sheets

This tutorial explains how to filter rows in a column containing multiple selected drop-down...

Two-Way Lookup with XLOOKUP in Google Sheets

When you need to look up one search key vertically and another horizontally, you...

More like this

Merge Duplicate Rows and Keep Latest Values in Excel

Here’s a dynamic array formula approach to merge duplicate rows and keep the latest...

XLOOKUP with Match Mode 3 in Excel (Regex Match)

XLOOKUP becomes more powerful in Excel with the new match mode 3, which enables...

Hierarchical Number Sorting in Excel with Modern Functions

A hierarchical numbering system in Excel allows you to organize data into a clear,...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.