Function Problems

How to Return a SharePoint Hyperlink with Excel XLOOKUP or VLOOKUP

Huma Ashraf Ch Oct 9, 2026 869 views

Question details

The user wants an Excel lookup formula to automatically return a SharePoint hyperlink associated with a selected event number from a different worksheet.

How to Return a SharePoint Hyperlink with Excel XLOOKUP or VLOOKUP

Product: Microsoft Excel
Device & OS: not provided
Scenario: Pulling data into a summary report using lookup formulas, expecting the original hyperlink from the data sheet to be clickable on the report page.
Observed behavior: XLOOKUP and VLOOKUP only return the text value of the cell, failing to retrieve the underlying hyperlink formatting or SharePoint URL address.

Before you start

Verify whether your source data contains the actual URL string or if the hyperlink is simply embedded in the cell formatting.

Solution 1Recommended

Redesign Source Data and Use the HYPERLINK Function

Since lookup formulas only return text values, storing the actual SharePoint URL as plain text in a separate column allows you to combine it with the HYPERLINK function.

Standard lookup functions like VLOOKUP and XLOOKUP extract cell values, not the underlying metadata or formatting. To make the link clickable on your summary page, the actual URL must be retrieved and processed by the HYPERLINK function.

Add a URL Column

In your source data worksheet, insert a new column next to the event numbers.

Paste URL Text

Extract or copy the actual SharePoint URL text (e.g., 'https://sharepoint...') and paste it into this new column for each corresponding event.

Write the Lookup Formula

On your summary report page, use XLOOKUP or VLOOKUP to retrieve the raw URL text. For example: =XLOOKUP(F4, DataSheet!A:A, DataSheet!B:B).

Wrap in HYPERLINK

Modify the formula by wrapping it inside the HYPERLINK function to make the result clickable. Example: =HYPERLINK(XLOOKUP(F4, DataSheet!A:A, DataSheet!B:B), "Open SharePoint Folder").

Redesign Source Data and Use the HYPERLINK Function

Best Practice: Storing URLs as raw text in a dedicated column is the most stable and reliable method for maintaining clickable links across different sheets without relying on scripts.

Solution 2

Use a VBA Macro to Extract the Hyperlink

If you cannot alter the source data structure, a custom VBA function (UDF) can extract the underlying hyperlink address from the referenced cell.

Free Microsoft Office alternative

Try WPS Office for Your Data Management

If you frequently work with complex data lookups, hyperlinks, and cross-sheet references, WPS Office provides a highly compatible, fast, and feature-rich alternative to Microsoft Office.

1. Download WPS Office: Visit the official WPS website and download the free installation package.
2. Install the Software: Run the installer and follow the on-screen instructions to set up WPS Office on your computer.
3. Open Your Workbooks: Launch WPS Spreadsheet and open your existing .xlsx files to seamlessly continue your lookup tasks.

Highly compatible with Microsoft Excel formats (.xlsx, .xls and .xlsm)Fully supports advanced lookup functions including XLOOKUP and VLOOKUPLightweight architecture ensures fast processing of large datasetsFamiliar user interface for a seamless, zero-learning-curve migration

microsoft office alternative - wps office

Frequently Asked Questions

Why doesn't VLOOKUP copy the hyperlink from the source cell?

VLOOKUP and XLOOKUP are designed to extract only the text or numerical data (the value) stored in a cell. They do not retrieve the cell's formatting, metadata, or embedded hyperlinks.

Can I use the HYPERLINK function with VLOOKUP?

Yes, you can wrap your VLOOKUP formula inside a HYPERLINK function. However, this only works if the VLOOKUP formula retrieves a valid URL text string from the source cell, rather than just a display name.

Is there a native Excel function to extract a URL from a hyperlinked cell?

No, Excel currently lacks a built-in formula function to extract the underlying URL address from a cell that is formatted as a hyperlink. You must use VBA or manually separate the URL into its own column.

Excel & Data Problems