Use VLOOKUP and Linked Workbook Formulas in SharePoint Excel Files
Question details
The user wants to know whether Excel workbooks saved in SharePoint can use VLOOKUP formulas and links to data in other workbooks.
- Product
- Microsoft Excel workbooks stored in SharePoint
- Device & OS
- not provided
- Scenario
- Users need to store a workbook in SharePoint while retrieving data from another accessible workbook.
- Observed behavior
- VLOOKUP and other linked-workbook formulas can work when referenced files, permissions, and workbook locations remain available.
Confirm that every person who opens the workbook has permission to view both the main workbook and every referenced workbook in SharePoint. Keep linked files in stable document library locations before creating or updating links.
Store linked workbooks in accessible SharePoint locations
Use this approach when a workbook contains VLOOKUP or other formulas that refer to data in another workbook.
Excel workbooks stored in SharePoint can contain VLOOKUP formulas and external workbook references. The linked source file must remain available at its saved SharePoint location, and users must have access to it.
In Excel, select File > Save As and save the main workbook and the source workbook to the appropriate SharePoint document library.
Open the main workbook in the Excel desktop app, select the formula cell, and verify that the VLOOKUP lookup range or other formula reference points to the correct SharePoint workbook.
In SharePoint, select the source workbook, choose Manage access, and confirm that the people using the main workbook have at least viewing permission for the referenced file.
Keep the source workbook in the same SharePoint folder after links are created. If it is moved or renamed, open the main workbook and use Data > Edit Links to update the source when available.
Recalculate linked formulas in the Excel desktop app
Use this when values shown in SharePoint or Excel for the web do not immediately reflect changes in a linked workbook.
Create VLOOKUP and Linked Workbook Formulas with WPS Spreadsheet
WPS Spreadsheet lets you build VLOOKUP formulas, reference data across workbooks, and recalculate results before sharing files with collaborators. Use stable cloud-accessible locations and confirm that collaborators can open each linked source workbook.
- 1. Open the main workbook: Launch WPS Spreadsheet, select Open, and choose the workbook that will contain the VLOOKUP formula.
- 2. Open or identify the source workbook: Use File > Open to verify the source workbook and locate the worksheet, lookup column, and return column needed for the formula.
- 3. Enter the VLOOKUP formula: Select the destination cell, type =VLOOKUP(lookup_value,table_array,column_index_num,FALSE), and replace the arguments with the required cell and data range references.
- 4. Recalculate and save: Use the Calculate or Recalculate command if needed, then select File > Save to store the workbook with its latest formula results.
Frequently Asked Questions
Can I save an Excel workbook with VLOOKUP formulas to SharePoint?
Yes. Save the workbook normally to a SharePoint document library. VLOOKUP formulas remain in the workbook and can calculate as long as their referenced cells or source workbooks are available.
Why does a linked workbook formula show old values in SharePoint?
Excel for the web may not continuously refresh external workbook links. Open the workbook through SharePoint with Open > Open in Desktop App, then use Formulas > Calculation Options > Automatic and press F9 before saving.
Do users need permission to the workbook used in a VLOOKUP link?
Yes. Users need access to both the workbook they open and the referenced source workbook. In SharePoint, select the source file and use Manage access to confirm permissions.
What happens if I move a source workbook after linking it?
Moving or renaming the source file can break or interrupt the external reference. Keep linked files in stable SharePoint folders, or open the main workbook in Excel and use Data > Edit Links to update the link.
