How to Use a Drop-Down List to Populate Nutrition Values in WPS Office Spreadsheets
Question details
The user needs to create a meal-selection drop-down that automatically returns calories, protein, carbohydrates, and other nutrition values in adjacent columns using XLOOKUP.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is setting up a meal-planning or nutrition-tracking spreadsheet and wants to select a meal from a drop-down and have the related nutrition data fill automatically.
- Observed behavior
- The selected meal should return the corresponding nutrition values in adjacent cells without manually copying or looking up each value.
Organize the meal and nutrition data in a structured Excel Table, and confirm that your WPS Office version supports XLOOKUP and dynamic array spill results.
Create a Data Validation Drop-Down and Use XLOOKUP to Return Adjacent Nutrition Values
Use this method when you want one meal selection to automatically fill calories, protein, carbohydrates, and fat in neighboring cells.
This approach uses an Excel Table for structured references and XLOOKUP's multi-column return array. When dynamic arrays are enabled, the formula spills across adjacent columns automatically.
Select the range containing the meal names and nutrition values, click Insert > Table, confirm the range, and rename the table to Meals in the Table Design tab.
Select the cell where the meal will be chosen, such as A2, click Data > Data Validation, choose List under Allow, and enter =Meals[Meal] as the source.
In the first adjacent cell, such as B2, enter =XLOOKUP(A2,Meals[Meal],Meals[[Calories]:[Fat]],"Not found") and press Enter.
Check that the formula returns all nutrition columns in adjacent cells and that no other data is blocking the spill range.
Build a Meal Nutrition Drop-Down in WPS Office Spreadsheets
Use WPS Office Spreadsheets to create the same data validation and XLOOKUP workflow for meal nutrition tracking, with an Excel-compatible interface and full .xlsx support.
- 1. Install WPS Office: Download WPS Office from the official site, install it, and open WPS Spreadsheets.
- 2. Prepare the nutrition table: Enter meal names and nutrition columns, select the range, and click Insert > Table.
- 3. Add the drop-down and formula: Click Data > Data Validation to create the meal list, then enter the XLOOKUP formula in the adjacent cell.
- 4. Save in Excel format: Click Menu > Save As and choose .xlsx to keep the workbook compatible with Microsoft Excel.
Frequently Asked Questions
Does XLOOKUP work in WPS Office Spreadsheets?
Yes, current WPS Office versions include XLOOKUP. If your version does not support it, use INDEX and MATCH to return the matching nutrition row or column.
Why does my XLOOKUP result show #SPILL?
Make sure the cells to the right of the formula are empty and that the return_array includes the full nutrition range, because XLOOKUP cannot spill into occupied cells.
Can I return only calories instead of all nutrition values?
Yes, replace the multi-column return array with a single column such as Meals[Calories]: =XLOOKUP(A2,Meals[Meal],Meals[Calories],"Not found").
How do I add a new meal to the drop-down list?
Add the new meal and its nutrition data as a new row in the Excel Table. Because the validation source uses Meals[Meal], the list expands automatically.
