'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Use VLOOKUP Between Worksheets and Workbooks in Excel Online
Question details
The user wants to pull data using VLOOKUP or XLOOKUP across different worksheets or entirely separate workbooks using Excel Online.
- Product
- Microsoft Excel Online
- Device & OS
- not provided
- Scenario
- Looking up and retrieving data from another sheet or file while working in the web browser version of Excel.
- Observed behavior
- Cross-sheet references work natively within the same file, but direct formula references between separate workbooks are not reliably supported in Excel for the web.
Ensure both your source and destination workbooks are saved in your OneDrive or SharePoint. If you need to link separate workbooks online, ensure you have access to Microsoft Power Automate.
Use VLOOKUP Between Worksheets in the Same Workbook
Use standard formula syntax to reference a different tab within the same Excel file, which is fully supported online.
Excel for the Web seamlessly supports cross-worksheet formulas. As long as your data resides in the same workbook, standard functions like VLOOKUP and XLOOKUP operate exactly as they do in the desktop version.
Navigate to Excel Online and open the workbook containing both your target sheet and the source data sheet.
Select the cell where you want the result to appear and type the beginning of your VLOOKUP formula, such as =VLOOKUP(A2,
Click on the tab of your second worksheet (e.g., Sheet2) and highlight the data range you want to search. The formula bar will automatically update to include the sheet reference, looking something like =VLOOKUP(A2, Sheet2!A:B, 2, FALSE).
Press Enter to complete the formula, then drag the fill handle down to apply the lookup to the rest of the column.
Use Power Automate for Separate Workbooks
Since Excel Online doesn't support direct cross-workbook formulas, use Power Automate to sync data into a single workbook first.
Seamlessly Link Multiple Workbooks with WPS Office
Skip complicated Power Automate workarounds. WPS Spreadsheet fully supports real-time VLOOKUP and XLOOKUP across separate workbooks without web-based restrictions. Enjoy a lightweight, fast, and feature-rich offline alternative.
- 1. Download and install: Download WPS Office for free from the official website and install it on your computer.
- 2. Open both workbooks: Launch WPS Spreadsheet and open both the destination workbook and the source workbook simultaneously.
- 3. Create the external link: Type =VLOOKUP( in your destination cell, switch to the window containing the source workbook, and highlight your array. Press Enter to instantly create a reliable cross-workbook link.
Frequently Asked Questions
Can I use XLOOKUP across different workbooks in Excel Online?
No, direct external links using XLOOKUP or VLOOKUP between separate workbooks are not supported in Excel Online. You must combine the data into one workbook or use a desktop spreadsheet application.
Why do my VLOOKUP external links show a #REF! error in Excel for the Web?
Excel for the Web cannot retrieve data from closed external workbooks dynamically. Opening the affected file in the desktop version of Excel will typically force the links to refresh and resolve the #REF! error.
Is there any way to merge Excel files online without using complex formulas?
Yes, you can manually copy and paste the required tables into a new worksheet within your primary file online. For automated merging, you would need to use Power Query in the desktop application.
