How to Return a Random Lookup Value in WPS Spreadsheets from an Excel Drop-Down Selection
Question details
The user needs to select a category in a drop-down list and return a random matching value from a related data range on another worksheet.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user has a working sheet with a drop-down category cell and a master data range on another worksheet. They want to generate a random lookup result for the selected category.
- Observed behavior
- The formula should display a matching value from the related data range and update to another random matching value when the workbook recalculates.
Before you start, check that your WPS Spreadsheets version supports the FILTER and LET functions, and confirm that the drop-down list source references the same category values as the Master Data sheet. Use the alternative AGGREGATE formula if you are on an older build.
Return a Random Matching Value with LET, FILTER, INDEX, and RANDBETWEEN
Use this dynamic formula when your WPS Spreadsheets build supports FILTER and LET; it filters the master data by the drop-down category and randomly picks one matching value.
This approach places the entire filtered list into a named variable with LET and then uses RANDBETWEEN to choose a row number from that list. Because RANDBETWEEN is volatile, the result recalculates whenever WPS Spreadsheets recalculates.
Open the workbook in WPS Spreadsheets and click cell B2 on the Working File sheet, or the cell where you want the random lookup result to appear.
In the formula bar, paste the following formula and adjust the sheet name and ranges to match your data: =IFERROR(LET(Data,FILTER('Master Data'!$B$2:$B$20,'Master Data'!$A$2:$A$20=A2),Num,ROWS(Data),INDEX(Data,RANDBETWEEN(1,Num))),"")
Press F9 to recalculate the workbook. Each recalculation selects another random matching value for the category chosen in A2.
Use AGGREGATE and COUNTIF as a Compatibility Formula
Use this formula if your version of WPS Spreadsheets does not support the FILTER or LET functions, or if you need a non-dynamic-array alternative.
Create Random Drop-Down Lookups in WPS Spreadsheets
Use WPS Spreadsheets to build the drop-down selection and the random lookup formula in one free Office-compatible workbook. Your existing .xlsx files open and calculate with familiar functions such as INDEX, LET, FILTER, and RANDBETWEEN.
- 1. Download and install WPS Office: Download WPS Office Free from the official WPS website, install it, and open the workbook that contains your drop-down list and master data.
- 2. Create or verify the drop-down list: In WPS Spreadsheets, select the category cell, go to Data > Data Validation, choose List, and set the source to the category values from the Master Data sheet.
- 3. Enter the random lookup formula: In the result cell, enter the recommended formula that uses FILTER, INDEX, and RANDBETWEEN, or paste the AGGREGATE compatibility formula if needed.
- 4. Recalculate and use the result: Press F9 to refresh the random matching value. If you need a static result, copy the cell and paste it as values.
Frequently Asked Questions
Why does my random lookup value change when I edit other cells?
RANDBETWEEN is a volatile function, so WPS Spreadsheets recalculates it every time the workbook recalculates. To keep a value, select the result cell and use Copy > Paste as Values.
Can I reference a master data table on another worksheet?
Yes. In the formula, include the sheet name in single quotes whenever it has spaces, for example 'Master Data'!$A$2:$A$20, and adjust the range to match your data.
Does WPS Spreadsheets support the FILTER and LET functions?
Recent WPS Spreadsheets builds support FILTER and LET. If your version does not, update WPS Office or use the AGGREGATE and COUNTIF compatibility formula in this article.
How do I create the drop-down list for the category?
Select the input cell, click Data > Data Validation, choose List, and set the source to the category values on your Master Data sheet so the formula can match the selected category.
Can I return a random value without duplicate results for multiple rows?
The basic formula treats each row independently, so duplicates can occur. To avoid duplicates, add helper columns or use a more advanced sorting and allocation approach.
