How to Match and Import Excel Data Between Two Workbooks in WPS Office
Question details
The user needs to bring selected columns from a large Excel source workbook into a smaller subset workbook when row order differs, using IncidentID as the matching key.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- Two workbooks contain related records; the destination subset workbook does not list rows in the same order as the source workbook, so data must be matched by IncidentID instead of position.
- Observed behavior
- Manual row-by-row copying is not practical because rows are not aligned; selected columns need to be imported dynamically by IncidentID.
Verify that IncidentID values are unique in the source workbook and match the subset workbook exactly, and open both workbooks in WPS Office so the formula can reference the source workbook.
Import Selected Columns with XLOOKUP Using IncidentID
Use this recommended solution when IncidentID values are unique and consistent, and you need a flexible lookup that does not depend on row order.
XLOOKUP searches for the IncidentID in the source workbook and returns the value from the column you specify. It is more flexible than VLOOKUP because the return column can be anywhere in the source range, not only to the right of the key column.
Launch WPS Office Spreadsheets and open both the large source workbook and the smaller subset workbook.
In the subset workbook, select the cell next to the first IncidentID where the imported value should appear.
Type =XLOOKUP(A2,[Book1]Sheet1!$A$2:$A$3500,[Book1]Sheet1!$D$2:$D$3500) and press Enter. Replace the sheet name, lookup range, and return range with your actual workbook, sheet, and target column.
Drag the fill handle down the column, then repeat the formula for each additional source column by changing the return range.
Use INDEX and MATCH as a Compatibility Alternative
Use this alternative if XLOOKUP is not available in your WPS Office version or you need a lookup formula that also works in older spreadsheet versions.
Import Matching Data Between Workbooks in WPS Office Spreadsheets
Use WPS Office Spreadsheets to match rows by IncidentID and pull only the selected columns you need from a large Excel workbook. Enter an XLOOKUP or INDEX MATCH formula, drag to fill, and save your subset workbook as a standard .xlsx file.
- 1. Open both workbooks in WPS Office: Launch WPS Office Spreadsheets and open the large source workbook and the smaller destination workbook.
- 2. Prepare the destination cell: In the destination workbook, click the first cell where the imported value should appear, directly next to the matching IncidentID.
- 3. Enter the matching formula: Type =XLOOKUP(A2,[Book1]Sheet1!$A$2:$A$3500,[Book1]Sheet1!$D$2:$D$3500) and press Enter, adjusting the source sheet, lookup range, and return range as needed.
- 4. Fill and repeat: Drag the fill handle down to import all records, then repeat with the correct return range for each additional source column.
- 5. Save or export: Save the subset workbook. If needed, choose Menu > Save As and select Excel Workbook (*.xlsx) for Microsoft Excel compatibility.
Frequently Asked Questions
Does the row order need to match between the two workbooks?
No. XLOOKUP and INDEX MATCH locate the IncidentID value anywhere in the source range, so the rows do not need to be in the same order as long as each IncidentID is unique.
Why does my XLOOKUP formula return #N/A?
This usually means an IncidentID in the subset workbook is missing or has extra spaces in the source workbook. Check that both keys are formatted the same way and that the lookup range covers the source IncidentID column.
Can I import multiple columns using this method?
Yes. After entering the first formula, drag it down, then create or copy a formula for each additional column and change the return range to that column in the source workbook.
Will this work when the source workbook is an Excel .xlsx file?
Yes. WPS Office Spreadsheets opens and saves .xlsx files and supports XLOOKUP, INDEX, and MATCH formulas, so you can work with Microsoft Excel workbooks directly.
Do I need to keep the source workbook open while using the formula?
You only need it open while creating and editing the formula. After the formula is entered, WPS Office can often use stored values, but keep the source accessible if you need to refresh or audit results.
