Data Import & Export

How to Use WPS Spreadsheet Lookup Formulas with External Data Sources

Huma Ashraf ChHuma Ashraf Ch Nov 13, 2026 871 views

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.

How to Use WPS Spreadsheet Lookup Formulas with External Data Sources
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 you start

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.

Solution 1Recommended

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.

1
Open both workbooks and select the result cell

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.

2
Start the XLOOKUP formula

Click the formula bar and type =XLOOKUP(, or click Formulas > Lookup & Reference > XLOOKUP if it is listed in the function gallery.

3
Select the lookup value and source columns

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].

4
Add a not-found message and confirm

Type "Not found" as the final argument, press Enter, and check that the matching item detail appears without opening the source table manually.

Use XLOOKUP to Pull Item Details from Another Workbook in WPS Spreadsheet
Flexible lookup result: XLOOKUP works across workbooks and does not require the source table to be sorted because it defaults to an exact match.
WPS Spreadsheet lookup formulas

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. 1. Open the workbooks: Open WPS Office Spreadsheet and load both the result workbook and the source workbook.
  2. 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. 3. Select external data ranges: Select the lookup value and then select the source table columns from the external workbook.
  4. 4. Confirm the formula: Add the exact-match or not-found argument and press Enter to return the matching item detail.
  5. 5. Save in Excel-compatible format: Save the workbook as .xlsx to keep formulas compatible with Microsoft Excel.
Return matching item details from another workbook with XLOOKUP or VLOOKUP without copying rowsSupports Microsoft Excel .xlsx workbooks and formula references for easy file exchangeFree, lightweight WPS Office interface with a familiar formula bar and function libraryWorks for cross-workbook lookup workflows while keeping your files Excel-compatible
microsoft office alternative - wps office

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.