Look Up a Part Number and Price from Another Sheet in WPS Spreadsheets
Question details
The user needs to retrieve a part number’s price from a price list stored on another worksheet and optionally validate part-number entries with a drop-down list.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- A price list is maintained on one worksheet, and the user wants to enter or select a part number on another worksheet to return the matching price automatically.
- Observed behavior
- Manual price lookup is slow and can introduce errors; the goal is to use an exact-match lookup formula that returns the correct price without manual searching.
Ensure the part numbers are in one column on the price list worksheet and the prices are in an adjacent column. Confirm that each part number is unique and contains no leading, trailing, or inconsistent spaces before creating the formula.
Use XLOOKUP to Return a Price from Another Worksheet
Best for WPS Office 2021+ and Excel 2021/Microsoft 365 users who want a clean, exact-match lookup with a built-in not-found value.
XLOOKUP searches a lookup array and returns the matching value from a return array. It avoids the column-index counting required by VLOOKUP and handles missing values with the if_not_found argument.
On the worksheet where the part number is entered, click the cell where the price should appear, for example E2.
Type =XLOOKUP(D2,PriceList!$A$2:$A$50,PriceList!$B$2:$B$50,"") and press Enter. D2 is the part number cell; PriceList is the worksheet containing the part numbers and prices.
Drag the fill handle from the bottom-right corner of the formula cell down through the rows that need the same lookup, or double-click the fill handle to auto-fill adjacent rows.
Use VLOOKUP with IFERROR for Older Spreadsheet Versions
Use this if your WPS Office or Excel version does not support XLOOKUP, or if you need a formula that works across older files.
Add a Drop-Down List of Valid Part Numbers
Use this to prevent typing errors by letting users choose a part number from a Data Validation list based on the price-list range.
Look Up Part Numbers and Prices Automatically in WPS Spreadsheets
Use XLOOKUP or VLOOKUP across worksheets in WPS Office Spreadsheets to return the matching price for a part number, and add a drop-down list to make part-number entry faster and more accurate.
- 1. Open your workbook in WPS Spreadsheets: Launch WPS Office, open the .xlsx workbook, and make sure the price list is on a separate worksheet such as PriceList.
- 2. Enable the lookup formula: Click the result cell on the input sheet and enter =XLOOKUP(D2,PriceList!$A$2:$A$50,PriceList!$B$2:$B$50,""), or use the VLOOKUP version for older versions.
- 3. Create a part-number drop-down: Select the part-number input cells, go to Data > Data Validation, choose List, set the source to =PriceList!$A$2:$A$50, and click OK.
- 4. Verify the results: Type or select a valid part number, press Enter, and confirm the price appears from the price list. Drag the formula down if you need multiple rows.
Frequently Asked Questions
Can I use XLOOKUP in WPS Office Spreadsheets?
Yes, recent WPS Office versions support XLOOKUP. If your version does not have it, use =IFERROR(VLOOKUP(D2,PriceList!$A$2:$B$50,2,FALSE),"") instead.
Why does my VLOOKUP return #N/A even though the part number exists?
This usually happens because of extra spaces, a mismatch between text and number values, or missing the FALSE exact-match argument. Check the source values, use TRIM to clean them, and set the last argument to FALSE.
How do I make the price update when I change the part number?
WPS Spreadsheets recalculates formulas automatically by default. When you type a new part number or select one from the drop-down, the formula result updates. If it does not, go to Formulas > Calculation Options and select Automatic.
Can the drop-down list source be on another worksheet?
Yes, WPS Spreadsheets supports a direct sheet reference such as =PriceList!$A$2:$A$50 in Data Validation. In some older Excel versions, you may need to create a named range first.
How do I avoid displaying #N/A when a part number is not found?
Wrap the lookup in IFERROR, for example =IFERROR(VLOOKUP(D2,PriceList!$A$2:$B$50,2,FALSE),""), or use XLOOKUP with an empty if_not_found argument.
