How to Combine Two Excel Tables by Article Number in WPS Spreadsheets
Question details
The user needs to add system information to a sales table by matching article numbers between two tables.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- One table contains article numbers and total units sold, while another contains article numbers and system information. Some articles appear in only one of the tables.
- Observed behavior
- System information needs to be added to the first table, but the article numbers do not align row by row.
Open both tables in WPS Spreadsheets and confirm that the article numbers use the same format, such as both text or both numbers, with no extra spaces. If needed, format each article number column consistently before applying the lookup formula.
Use XLOOKUP to Match Article Numbers and Return System Information
Use this solution when your version of WPS Spreadsheets supports XLOOKUP and you want a flexible lookup with a custom result for missing article numbers.
XLOOKUP searches for an article number in the system table and returns the system information from the same row. It does not require the return column to be to the right of the lookup column, which makes it more flexible than VLOOKUP.
In the first table, click the first cell in the System Information column where the result should appear.
Type =XLOOKUP([@[Article Number]],SystemTable[Article Number],SystemTable[System Information],"Not found") and press Enter.
Drag the fill handle down or double-click it to copy the formula through the System Information column.
Check that rows with no matching article number display "Not found" instead of an error.
Use VLOOKUP for Older Spreadsheet Versions
Use this solution when you need compatibility with older WPS Spreadsheets or Excel versions that do not support XLOOKUP.
Use INDEX and MATCH When the Lookup Column Is Not First
Use this solution when the article number is not in the first column of the system table or when you need a more flexible formula than VLOOKUP.
Combine Excel Tables by Article Number in WPS Spreadsheets
Use XLOOKUP, VLOOKUP, or INDEX MATCH in WPS Spreadsheets to match article numbers and pull system information from one table into another without manual copying.
- 1. Download WPS Office: Download and install WPS Office from the official WPS website, then open WPS Spreadsheets.
- 2. Open the workbook with both tables: Open the workbook that contains the article number sales table and the system information table.
- 3. Enter the lookup formula: Select the first System Information cell in the first table and enter the XLOOKUP formula using the structured table references.
- 4. Apply and verify results: Copy the formula down the column and confirm that matching system information appears and missing article numbers display "Not found".
Frequently Asked Questions
What formula can I use in WPS Spreadsheets to combine two tables by article number?
You can use =XLOOKUP([@[Article Number]],SystemTable[Article Number],SystemTable[System Information],"Not found") if XLOOKUP is supported. In older versions, use VLOOKUP with exact match or INDEX and MATCH.
Why does my VLOOKUP formula return #N/A even when the article number exists?
This usually happens because one article number is stored as text and the other as a number, or because there are extra spaces. In VLOOKUP, the lookup value must also be in the first column of the selected range.
How do I prevent #N/A errors when an article number is missing from one table?
Wrap the lookup formula in IFERROR, or use the XLOOKUP fourth argument to return a custom value such as "Not found" when no match exists.
Can WPS Spreadsheets open Excel files that contain XLOOKUP formulas?
Yes, WPS Spreadsheets opens .xlsx and .xls workbooks and supports XLOOKUP in versions that include it. For maximum compatibility with older versions, use VLOOKUP or INDEX and MATCH.
Does WPS Spreadsheets support Excel table structured references like SystemTable[Article Number]?
Yes. You can create a table by selecting the range and choosing Insert > Table in WPS Spreadsheets, then use structured references such as SystemTable[Article Number] in formulas.
