WPS Spreadsheet: Match Product Numbers and Return Costs with XLOOKUP
Question details
The user needs to retrieve product costs by matching sales part numbers with a product list.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- A sales table contains part numbers, while product numbers and costs are stored on a separate worksheet or in another workbook.
- Observed behavior
- The required cost must be returned for each matching part number, including repeated part numbers in the sales data.
Confirm that the sales part numbers and product-list part numbers use the same format, including any leading zeros. Check the product-list sheet name and identify the columns containing part numbers and costs before entering the formula.
Use XLOOKUP to return costs from a product worksheet
Use this solution when the product numbers and costs are stored on another sheet in the same WPS Spreadsheet file.
XLOOKUP searches the product-number range for the value in each sales row and returns the corresponding cost. Copying the formula down applies the same lookup to repeated part numbers automatically.
Open the sales worksheet and click the first blank cost cell beside the part number in A2.
Type =XLOOKUP(A2,Products!A:A,Products!B:B,"Not found") in the formula bar, replacing Products with the exact product-list sheet name if necessary.
Press Enter and verify that the returned value matches the cost for the part number in A2.
Select the completed formula cell and drag its fill handle down the sales table, or use Home > Fill > Down, to process every part number.
Reference a product list in another open workbook
Use this variation when the lookup table is stored in a separate workbook that is open in WPS Spreadsheet.
Use WPS Spreadsheet for XLOOKUP product cost matching
WPS Spreadsheet lets you match part numbers and return product costs with XLOOKUP across worksheets or open workbooks. Its familiar spreadsheet interface makes it easy to enter, verify, and fill lookup formulas.
- 1. Open your sales workbook: Launch WPS Office, choose Spreadsheet, and open the workbook containing the sales part numbers.
- 2. Prepare the product list: Open the worksheet or workbook containing product numbers and costs, and confirm the lookup and return columns.
- 3. Enter the lookup formula: Select the first cost cell and enter =XLOOKUP(A2,Products!A:A,Products!B:B,"Not found"), adjusting the sheet or workbook reference as needed.
- 4. Fill the results: Press Enter, verify the result, and drag the fill handle down the sales table to return costs for all rows.
Frequently Asked Questions
What XLOOKUP formula matches a sales part number to a product cost?
If the sales part number is in A2, product numbers are in Products column A, and costs are in Products column B, enter =XLOOKUP(A2,Products!A:A,Products!B:B,"Not found").
Can XLOOKUP search a different worksheet in WPS Spreadsheet?
Yes. Include the worksheet name before each range, such as Products!A:A and Products!B:B. If the sheet name contains spaces, enclose it in single quotation marks.
How do I use XLOOKUP with another workbook?
Open both workbooks, then select the lookup and return ranges from the product workbook while creating the formula. WPS Spreadsheet inserts the external workbook reference automatically; keep the source workbook open while setting up the formula.
What happens when a part number is missing?
The fourth XLOOKUP argument controls the missing-value result. In =XLOOKUP(A2,Products!A:A,Products!B:B,"Not found"), unmatched part numbers display Not found instead of an error.
Will repeated part numbers return the same product cost?
Yes. Copy the XLOOKUP formula down the sales table. Each row searches its own part number in the product list and returns the corresponding cost.
