WPS Spreadsheet: Look Up Product Prices by SKU with XLOOKUP
Question details
The user needs to match a product SKU against a price list and return the corresponding price on another worksheet.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A product SKU is entered on one worksheet, while the SKU and price data are stored on a separate Price List worksheet.
- Observed behavior
- The goal is for WPS Spreadsheet to find the matching SKU and display its price automatically, leaving the result blank when no match exists.
Confirm that the SKU column on the Price List sheet contains the lookup values and that the price column is beside it. Check that the SKU formats match, such as text versus numbers, before entering the formula.
Use XLOOKUP to return a product price by SKU
Use this method in current WPS Spreadsheet versions when the SKU and price ranges can be selected independently.
XLOOKUP searches the SKU range and returns the corresponding value from the price range. The final empty-string argument keeps the result blank when the SKU is not found.
Open the worksheet where the requested SKU is listed, and select the result cell next to the SKU, such as E2 when the SKU is in D2.
In the formula bar or selected cell E2, enter =XLOOKUP(D2,'Price List'!$A$2:$A$100,'Price List'!$B$2:$B$100,"") and press Enter.
Verify that E2 displays the price from the matching row on the Price List sheet. If D2 does not match a SKU, the cell remains blank.
Select E2 and drag its fill handle down, or copy and paste the formula into the remaining result cells for additional products.
Use VLOOKUP for older spreadsheet compatibility
Use this alternative when XLOOKUP is unavailable or when the SKU and price columns are arranged as the first two columns of one table.
Find SKU Prices Quickly in WPS Spreadsheet
WPS Spreadsheet can match SKUs across worksheets with XLOOKUP or VLOOKUP, return the correct product price, and fill the formula down for an entire product list.
- 1. Open your workbook: Launch WPS Office, open the spreadsheet containing the SKU list and the Price List worksheet.
- 2. Select the result cell: Click the cell beside the first SKU where the matching price should appear.
- 3. Add a lookup formula: Enter an XLOOKUP formula using the SKU cell, the SKU range on Price List, and the corresponding price range.
- 4. Fill the formula down: Drag the fill handle or copy the formula to apply the lookup to all product rows.
Frequently Asked Questions
How do I look up a price from another worksheet in WPS Spreadsheet?
Select the destination cell and enter =XLOOKUP(D2,'Price List'!$A$2:$A$100,'Price List'!$B$2:$B$100,""). Replace D2 and the ranges with the locations in your workbook.
What should I use if XLOOKUP is not available?
Use =IFERROR(VLOOKUP(D2,'Price List'!$A$2:$B$100,2,FALSE),""). The SKU must be in the first column of the selected table range, and FALSE ensures an exact match.
Why does the SKU lookup return a blank result?
Check that the SKU in the destination cell exactly matches a value on the Price List sheet. Differences in spaces, text-versus-number formatting, or an incorrect sheet name or range can prevent a match.
How can I apply the price lookup to every product?
Enter the formula in the first result cell, select it, and drag the fill handle down. Absolute references such as $A$2:$A$100 keep the Price List ranges fixed as the formula copies.
