Create an Excel for the Web Hyperlink to a Matching Cell in an External Workbook
Question details
Create a formula that finds a matching value in an external workbook and opens the corresponding cell through an Excel for the web SharePoint link.
- Product
- Microsoft Excel for the web with SharePoint-hosted workbooks
- Device & OS
- not provided
- Scenario
- A workbook needs a clickable link that uses a lookup value to locate the first matching row in another workbook.
- Observed behavior
- The hyperlink must use the correct SharePoint workbook URL and cell-navigation parameter to open the target worksheet and matching cell.
Confirm that you can open the source workbook in Excel for the web and copy its SharePoint URL. Verify the exact source workbook filename, worksheet name, and the column that contains the lookup values.
Use XMATCH and HYPERLINK to open the matching external workbook cell
Use this method when the source workbook is accessible through SharePoint and you want the link to calculate the first matching row automatically.
XMATCH returns the position of the first matching value, and HYPERLINK combines that row number with an Excel for the web workbook URL. Replace the sample URL, workbook name, sheet name, and lookup ranges with your organization’s values.
In the workbook containing the link, identify the lookup value, such as cell B4. In the source workbook, use the column containing the matching values, for example '[SOURCEFILENAME.xlsx]Sheet2'!$A:$A.
Select the result cell and enter: =LET(r,XMATCH(B4,'[SOURCEFILENAME.xlsx]Sheet2'!$A:$A),HYPERLINK("https://COMPANY.sharepoint.com/personal/USERNAME/Documents/SOURCEFILENAME.xlsx?activeCell=Sheet2!A"&r,r))
Replace COMPANY, USERNAME, SOURCEFILENAME.xlsx, Sheet2, and A with the actual SharePoint URL components, source workbook name, worksheet name, and target column letter.
Click the formula result in Excel for the web and confirm that the browser opens the source workbook at the expected cell, such as Sheet2!A25.
Calculate the match from a synchronized source-data copy
Use this fallback when Excel cannot evaluate external workbook references or the SharePoint-hosted workbook is unavailable to the formula.
Build Dynamic Workbook Links with WPS Spreadsheet
WPS Spreadsheet lets you use lookup and hyperlink formulas to calculate a matching row and create a clickable link to the related workbook location. It provides a familiar spreadsheet workflow for managing linked Excel data and formulas.
- 1. Open the workbook in WPS Spreadsheet: Launch WPS Office, select Spreadsheet, and open the .xlsx workbook containing the lookup value.
- 2. Select the formula output cell: Click the cell where the dynamic link should appear, such as C4.
- 3. Enter the lookup and hyperlink formula: Type a LET, XMATCH, and HYPERLINK formula using your source range and workbook URL, then press Enter.
- 4. Test the link target: Click the displayed hyperlink and verify that it opens the intended workbook location in the available browser or workbook application.
Frequently Asked Questions
How do I make an Excel hyperlink open a specific cell in Excel for the web?
Add the target cell to the workbook URL with the activeCell parameter, such as ?activeCell=Sheet2!A25. In a formula, concatenate the calculated row number after the column letter.
Why does my external workbook hyperlink not open the expected cell?
Check the SharePoint workbook URL, worksheet name, cell reference, and URL encoding. Some Microsoft 365 environments use different navigation behavior, so test the completed URL directly in Excel for the web.
Can XMATCH look up values in another Excel workbook?
XMATCH can use an external workbook range when the environment supports external references and the source workbook is accessible. If the reference cannot be evaluated, use a synchronized local copy of the source lookup data.
What happens when more than one source row matches the lookup value?
XMATCH returns the first matching position by default. To link to a different match, adjust the lookup logic or add criteria that makes the source value unique.
