How to Replace Google Sheets IMPORTRANGE with INDEX and MATCH in WPS Spreadsheets
Question details
The user wants an Excel-compatible alternative to Google Sheets IMPORTRANGE that can retrieve data from another workbook and combine it with INDEX and MATCH or similar lookup functions.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- Migrating a Google Sheets workflow that uses IMPORTRANGE with INDEX and MATCH to an Excel-compatible spreadsheet.
- Observed behavior
- Excel and Excel-compatible spreadsheets do not recognize IMPORTRANGE, so the user needs a workbook reference or data import method that produces equivalent cross-workbook lookup results.
Save the source workbook in a folder you can easily open from WPS Spreadsheets and note the exact worksheet name and data range. If you will refresh the data later, use a stable file location and name to avoid broken links.
Use an external workbook reference with XLOOKUP or INDEX and MATCH
Use this when both workbooks are available and you want live lookup results that update when the source workbook changes.
WPS Spreadsheets uses the same external reference syntax as Excel, so you can point a lookup formula directly to another workbook. External references are ideal for dynamic dashboards and recurring reports where the source file is opened or linked.
Open the source workbook that contains the data and the destination workbook where the formula will be placed.
Click the destination cell and type =XLOOKUP(lookup_value, then click the source workbook and select the lookup column.
Type a comma, switch to the source workbook, select the return column, and add the final argument such as ,"Not found"). Press Enter. For backward compatibility, use =INDEX('[Source.xlsx]Sheet1'!$B$2:$B$100,MATCH(A2,'[Source.xlsx]Sheet1'!$A$2:$A$100,0)).
Paste a linked range from another workbook
Use this when you need a visible data block rather than a single lookup result, and you want the pasted cells to keep a link to the source.
Replace IMPORTRANGE with WPS Spreadsheets Workbook Lookups
WPS Spreadsheets lets you complete cross-workbook lookups without IMPORTRANGE by using external workbook references, XLOOKUP or INDEX and MATCH. You can open, edit, and save Excel-compatible .xlsx workbooks in a free, familiar spreadsheet environment.
- 1. Open the destination workbook in WPS Spreadsheets: Launch WPS Office, open the workbook where you want the IMPORTRANGE alternative result, and click the cell that will contain the lookup.
- 2. Reference the source workbook in a formula: Open the source workbook, then enter =XLOOKUP(A2,'[Source.xlsx]Sheet1'!$A$2:$A$100,'[Source.xlsx]Sheet1'!$B$2:$B$100,"Not found") in the destination cell.
- 3. Use INDEX and MATCH if needed: If your WPS version or template requires INDEX and MATCH, enter =INDEX('[Source.xlsx]Sheet1'!$B$2:$B$100,MATCH(A2,'[Source.xlsx]Sheet1'!$A$2:$A$100,0)).
- 4. Refresh and save as .xlsx: Keep both workbooks open for live results, then save the destination workbook in the default .xlsx format so it remains compatible with Microsoft Excel and WPS Spreadsheets.
Frequently Asked Questions
Does WPS Spreadsheets have a Google Sheets IMPORTRANGE function?
No. WPS Spreadsheets uses Excel-compatible workbook references instead. You can use formulas such as ='[Source.xlsx]Sheet1'!A1, XLOOKUP, or INDEX and MATCH to retrieve data from another workbook.
Can I pull data from a closed Excel workbook in WPS Spreadsheets?
Yes, an external reference such as ='[Source.xlsx]Sheet1'!A1 can return values from a closed workbook if the source file stays in the referenced path. For a one-time snapshot, open the source workbook and copy the range into the destination file.
Does WPS Spreadsheets support XLOOKUP?
Current WPS Spreadsheets versions support Excel-compatible lookup functions including XLOOKUP. If your version does not show XLOOKUP, use INDEX and MATCH with an external reference instead.
How do I link a whole data range instead of writing a formula?
Copy the source range in WPS Spreadsheets, switch to the destination workbook, right-click the starting cell, select Paste Special, and choose Paste Link.
How can I use Power Query to replace IMPORTRANGE in Excel?
In Microsoft Excel, go to Data > Get Data > From File > From Excel Workbook, select the source workbook, choose the sheet or table, and load it. In WPS Spreadsheets, use an external reference or Paste Link for a comparable cross-workbook result.
