How to Create an Excel Price List with Material Options in WPS Office
Question details
The user wants to create a price list worksheet in WPS Office Spreadsheets that automatically calculates a product price based on the selected material and options and shows the calculation clearly.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- Planning a product price list where each product has multiple material options and the worksheet must return the matching price and explain the calculation.
- Observed behavior
- The worksheet should return the correct price for the selected material option and show the calculation logic clearly.
Prepare a separate data table listing each material, option, and unit price before building the calculator. Check whether your WPS Office version supports XLOOKUP; if it does not, use VLOOKUP instead.
Create a material price table and use drop-down lists with XLOOKUP
Use this method when WPS Office Spreadsheets supports XLOOKUP and you want the most direct formula to return the selected material and option price.
This method keeps the raw material, option, and price data separate from the visible price list, so the formula remains easy to audit and update.
Create a new sheet named Materials. In columns A, B, and C, enter Material, Option, and Price; list every material and price combination the price list should support.
On the price list sheet, set up cells for Material and Option. Select the Material cell, go to the Data tab, click Data Validation, choose List, and set the source to the Material column range in the Materials sheet.
Select the Option cell, open Data Validation again, choose List, and point the source to the Options range that corresponds to the selected material, or use a helper column if the option list depends on the material.
In the Price cell enter =XLOOKUP(A2&"|"&B2, Materials!$A$2:$A$100&"|"&Materials!$B$2:$B$100, Materials!$C$2:$C$100) and press Enter to return the exact material-option price.
Add a Quantity cell and a Total cell with =Price_cell*Quantity_cell so the estimate or order total updates automatically.
Use VLOOKUP with a helper column when XLOOKUP is not available
Use this method if you are using an older WPS Office version or sharing the workbook with users who only have VLOOKUP available.
Create an Excel Price List with Material Options in WPS Spreadsheets
WPS Office Spreadsheets can directly build the price list: create a material price table, add drop-down lists, and use XLOOKUP or VLOOKUP to calculate prices automatically.
- 1. Download and install WPS Office: Go to wps.com, download WPS Office, install it, and open the price list workbook or create a new Spreadsheet.
- 2. Enter the material price data: Create a Materials sheet and list Material, Option, and Price columns with every combination you sell.
- 3. Add drop-down lists: On the price list sheet, select the Material cell, click Data > Data Validation, choose List, and select the material range.
- 4. Insert the lookup formula: In the Price cell, enter the XLOOKUP or VLOOKUP formula that matches the selected material and option.
- 5. Format and save the price list: Add totals and formatting, then save as .xlsx to maintain Excel compatibility.
Frequently Asked Questions
Can I use this price list in Microsoft Excel after creating it in WPS Office?
Yes. Save the worksheet as .xlsx, and XLOOKUP/VLOOKUP formulas and data validation drop-downs will work in Microsoft Excel.
What if my WPS Office version does not have XLOOKUP?
Use VLOOKUP with a helper column. Create a unique key such as material|option in the first column of the lookup range and refer to it from the price cell.
How do I make the Option drop-down change based on the selected material?
Create dependent drop-downs by using defined names or a helper column per material, then set the Option data validation source with an IF or INDIRECT formula to the selected material's option list.
Why is my VLOOKUP returning #N/A?
The lookup key is not found, usually because the Material and Option values do not exactly match the Materials table, or the material-option key column is not the first column in the VLOOKUP range.
