Use Excel Drop-Down Selections for Multiple Conditional Values in WPS Spreadsheets
Question details
The user is building a WPS Spreadsheets workbook with drop-down selections for Unit and Word and needs a formula that returns the correct numeric value from multiple condition combinations.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The worksheet has drop-down cells for Unit and Word, and the user wants to calculate different results based on the selected unit, the selected word, and their combined condition.
- Observed behavior
- The result depends on a two-condition lookup table. A single cell cannot contain both a word label such as Word 1 and a numeric value used in arithmetic, so the label and its number must be kept in separate cells.
Prepare a structured lookup table with Unit, Word, and Result columns, and make sure the Unit and Word input cells are available as separate drop-down cells. Keep the Word label and its numeric value in different cells so arithmetic formulas use numbers, not descriptive text.
Return the Correct Value with a Two-Condition XLOOKUP and a Lookup Table
Use this method when the result depends on two drop-down selections and all allowed combinations are stored in a structured lookup table.
A structured table keeps every allowed Unit and Word combination in one place. The XLOOKUP formula multiplies two logical arrays so only the row where both conditions are true returns 1.
In WPS Spreadsheets, select the range that will hold the lookup values, go to the Insert tab, and click Table. Rename the table to Table1 and add the columns Unit, Word, and Result.
Select the Unit input cell such as B4, go to Data > Validation, choose List, and set Source to the Unit column. Repeat for the Word label cell such as D8 using the Word column.
In an empty result cell such as F8, type =XLOOKUP(1,(Table1[Unit]=B4)*(Table1[Word]=D8),Table1[Result],"") and press Enter. The formula returns the Result from the row that matches both the selected Unit and Word.
Keep the Word label and its numeric value in separate cells. In the final cell, use only numeric cells and the returned lookup result. If D8 holds a numeric Word value, the final formula can follow the pattern =A8+((D8+C8)*B8)+F8, where F8 is the lookup result; if D8 is the Word label, replace D8 with the separate numeric Word cell.
Use INDEX and MATCH When XLOOKUP Is Not Available
Use this fallback if your WPS Spreadsheets version does not expose XLOOKUP or if you need to share the workbook with an older Excel-compatible environment.
Build a Conditional Drop-Down Lookup Workbook in WPS Spreadsheets
WPS Spreadsheets can directly create the data validation lists, structured lookup table, and two-condition XLOOKUP formula you need, while keeping the workbook compatible with Microsoft Excel. It is a free, lightweight alternative with a familiar ribbon, so you can open, edit, and save .xlsx files without reworking your formulas.
- 1. Open WPS Spreadsheets: Create a new workbook or open the existing .xlsx file that contains your drop-down logic.
- 2. Set up the lookup table: Enter the Unit, Word, and Result columns and format the range as a Table from the Insert tab.
- 3. Create drop-down lists: Select the Unit and Word input cells, then go to Data > Validation and choose List with the appropriate source range.
- 4. Enter the conditional lookup formula: Use XLOOKUP to match both conditions and reference the returned result in your final calculation.
- 5. Save as .xlsx: Save the file as .xlsx to keep the formulas compatible with Microsoft Excel and WPS Office.
Frequently Asked Questions
Can I use this XLOOKUP formula in WPS Spreadsheets the same way as in Excel?
Yes. Current versions of WPS Spreadsheets support XLOOKUP and structured table references. If your version does not show XLOOKUP, use the INDEX and MATCH equivalent instead.
Why does my Word drop-down cell not calculate correctly?
A cell cannot contain both text such as Word 1 and a numeric value at the same time. Keep the Word label in one cell, put its numeric value in a separate cell, and refer only to numeric cells in arithmetic.
How do I add a drop-down list for Unit and Word in WPS Spreadsheets?
Select the input cell, go to Data > Validation, choose List, and set the source to the relevant column or type the allowed values separated by commas.
What should I do if the XLOOKUP result is blank?
Check that the Unit and Word selections exactly match a row in the lookup table, including spaces and capitalization. You can also add a fallback value in the final XLOOKUP argument.
Can I use SWITCH instead of a lookup table for this problem?
SWITCH works for a single expression with a fixed list of cases. For multiple conditions and many combinations, a two-condition lookup table is easier to maintain and update.
