How to Use WPS Spreadsheet Lookup Formulas with External Data Sources
Question details
The user wants to know whether spreadsheet lookup formulas can pull item details from external workbooks, SharePoint lists, Dataverse, and other connected sources, and how VLOOKUP and XLOOKUP compare for this workflow.

- Product
- WPS Office Spreadsheet
- Device & OS
- not provided
- Scenario
- The user is evaluating lookup formula options in spreadsheet workbooks and wants to retrieve item details from workbooks, SharePoint lists, Dataverse, or other connected data sources.
- Observed behavior
- The goal is to return matching information from an external table or connected data source without manually copying rows into the formula workbook.
Before entering a lookup formula, open both the result workbook and the source workbook in WPS Office Spreadsheet. If you plan to work with SharePoint or Dataverse data, export or connect the table first so the data is accessible as a referenced range.
Use XLOOKUP to Pull Item Details from Another Workbook in WPS Spreadsheet
Use this method when you are on a current WPS Office version and need a flexible lookup that can search columns to the left or right and show a clean not-found message.
XLOOKUP is available in current WPS Spreadsheet builds and is the recommended replacement for older lookup workflows because it separates the lookup array and return array, reducing column-index mistakes.
In WPS Office Spreadsheet, open the workbook where you want the result and the workbook that contains the source table. Click the cell where the returned item detail should appear.
Click the formula bar and type =XLOOKUP(, or click Formulas > Lookup & Reference > XLOOKUP if it is listed in the function gallery.
Select the lookup value cell such as A2, then switch to the source workbook and select the lookup column, such as Source[Item Code]. Next select the return column, such as Source[Item Details].
Type "Not found" as the final argument, press Enter, and check that the matching item detail appears without opening the source table manually.

Use VLOOKUP When You Need Compatibility with Older Spreadsheet Versions
Use this method if the file may be opened in older spreadsheet applications or if your team still uses VLOOKUP-based templates.
Use WPS Spreadsheet to Retrieve Item Details from External Workbooks
WPS Office Spreadsheet can retrieve item details from another workbook with XLOOKUP or VLOOKUP, so you do not need to copy rows manually. It reads and saves .xlsx workbooks, making it easy to keep working with Excel-compatible lookup formulas.
- 1. Open the workbooks: Open WPS Office Spreadsheet and load both the result workbook and the source workbook.
- 2. Insert the lookup function: Click the result cell, then go to Formulas > Lookup & Reference and choose XLOOKUP or VLOOKUP from the function list.
- 3. Select external data ranges: Select the lookup value and then select the source table columns from the external workbook.
- 4. Confirm the formula: Add the exact-match or not-found argument and press Enter to return the matching item detail.
- 5. Save in Excel-compatible format: Save the workbook as .xlsx to keep formulas compatible with Microsoft Excel.

Frequently Asked Questions
Does WPS Spreadsheet support XLOOKUP like Microsoft 365 Excel?
Yes, current WPS Office versions support XLOOKUP. You can enter =XLOOKUP(lookup_value,lookup_array,return_array,"Not found") in the formula bar and select ranges from the same or another open workbook.
Can VLOOKUP pull data from a different workbook in WPS Spreadsheet?
Yes. Open both workbooks, click the result cell, type =VLOOKUP(, select the lookup value, switch to the source workbook and select the table range, then specify the column index and FALSE for an exact match.
Do SharePoint lists or Dataverse tables work like normal workbook ranges for lookup formulas?
They are centralized Microsoft data sources rather than ordinary workbook ranges. You typically connect or export them through Microsoft 365 or Power Platform, and WPS Spreadsheet can work with the exported .xlsx data.
Which is more flexible: VLOOKUP or XLOOKUP?
XLOOKUP is generally more flexible because it can return columns to the left, separates the lookup and return arrays, handles exact or approximate matching, and lets you set a clean not-found message without IFERROR.
What should I check if my external lookup returns #N/A?
Check that the external workbook is open or the path is valid, that lookup values have the same data type and format, and that the correct column index or return array is selected in the formula.




