WPS Spreadsheet: Return a Price for a Selected Item
Question details
The user needs a spreadsheet formula that returns the correct price when an item or wood section is selected.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- A product or wood section is selected in one cell, and its matching price must be retrieved from a two-column reference table.
- Observed behavior
- The desired price should update automatically whenever the selected item changes.
Prepare a reference table with a unique item column and a corresponding price column, then confirm the selected item cell and the result cell. Use clear table headers such as WoodSection and Price.
Use XLOOKUP to return the matching price
Use this method when your WPS Spreadsheet version supports XLOOKUP and your reference data is organized in separate lookup and return columns.
XLOOKUP searches the selected item in the reference column and returns the corresponding value from the price column. The empty-text argument keeps the result blank when no match is found.
In WPS Spreadsheet, enter the item names in one column and their prices in the next column. Select the range, choose Insert > Table, and name the table WoodPricing if table names are available.
Enter or choose the wood section in cell D1, or use the cell that contains the selected product.
Select cell E1 and enter =XLOOKUP(D1,WoodPricing[WoodSection],WoodPricing[Price],"") if the table uses the WoodSection and Price column names.
Change the value in D1 and confirm that E1 automatically displays the matching price.
Use VLOOKUP for a compatible two-column table
Use this fallback when XLOOKUP is unavailable or when the lookup item is in the first column of a simple reference range.
Use INDEX and MATCH for flexible lookup positions
Use this option when the lookup and price columns are not arranged for VLOOKUP or when you want separate ranges for the search and return values.
Automatically Look Up Prices in WPS Spreadsheet
WPS Spreadsheet lets you build a reference table and use XLOOKUP, VLOOKUP, or INDEX and MATCH to return a price automatically when a selected item changes.
- 1. Open WPS Spreadsheet: Launch WPS Office and open a new or existing spreadsheet.
- 2. Build the reference table: Enter item names and prices in adjacent columns, then select the range and choose Insert > Table.
- 3. Enter the selected item: Place the product or wood section selection in a dedicated cell such as D1.
- 4. Add the lookup formula: In the result cell, use =XLOOKUP(D1,WoodPricing[WoodSection],WoodPricing[Price],"") or a compatible VLOOKUP formula.
- 5. Test the result: Change the selection and confirm that the returned price updates automatically.
Frequently Asked Questions
Which formula should I use to return a price from a selected item?
Use =XLOOKUP(D1,WoodPricing[WoodSection],WoodPricing[Price],"") when XLOOKUP is supported. Otherwise, use =VLOOKUP(D1,WoodPricing,2,FALSE) for a two-column table.
Why does VLOOKUP return an incorrect price?
Check that the selected item is in the first column of the lookup range and that the last argument is FALSE for an exact match. Also check for extra spaces or spelling differences.
How can I keep the result blank when no item is selected?
Use XLOOKUP with an empty fallback value, such as =XLOOKUP(D1,WoodPricing[WoodSection],WoodPricing[Price],""). For VLOOKUP, wrap the formula in IFERROR, such as =IFERROR(VLOOKUP(D1,A2:B20,2,FALSE),"").
Will the price update when I change the selected item?
Yes. If the lookup formula refers to the selection cell, such as D1, WPS Spreadsheet recalculates the result automatically whenever D1 changes.
