Lookup & Reference
Pick a Value From a Fixed Set of Options (INDEX + MOD / RANDBETWEEN)
Select an allowed tier, shift size, or discrete test parameter (such as {10, 25, 50, 100}) from a fixed lookup list using INDEX(Options, selector)—either deterministically via MOD(ID - 1, COUNTA(Options)) + 1 or randomly via RANDBETWEEN(1, COUNTA(Options)).
Sample or cycle ONLY through valid discrete pack sizes {10, 25, 50, 100}!
When generating QA test datasets, simulation scenarios, or rotational shift assignments, standard RANDBETWEEN(10, 100) returns invalid numbers like 37 or 83 when your factory ONLY ships discrete carton sizes {10, 25, 50, 100}! The clean pattern separates the 1-to-K integer selector from the allowed-values list via INDEX(Allowed_Range, Selector_1_to_K)!
You get two interchangeable modes with the exact same INDEX wrapper: for volatile Monte Carlo draws that change on recalculation, pass RANDBETWEEN(1, COUNTA($A$2:$A$5)) as the row index; for reproducible, non-volatile QA test tables that NEVER jump around when someone edits a cell, pass deterministic modulo index MOD(Ticket_ID - 1, COUNTA($A$2:$A$5)) + 1!
| Ticket / Test Row ID | Selector MOD(ID-1, 4) + 1 | Allowed Pack Options {10,25,50,100} | Assigned Value |
|---|---|---|---|
| Row ID = 1 | Index 1 | Option #1 = 10 | 10 units |
| Row ID = 2 | Index 2 | Option #2 = 25 | 25 units |
| Row ID = 3 | Index 3 | Option #3 = 50 | 50 units |
| Row ID = 5 (Wraps!) | Index 1 | Option #1 = 10 | 10 units |
Reproducible Cyclic Option Selector From Fixed Allowed Range
Maps any positive integer ID (D2) onto 1..COUNTA(A2:A5) via MOD and retrieves the corresponding allowed value.
=IF(OR(D2 < 1, COUNTA(A2:A5) = 0), "Invalid Selector", INDEX(A2:A5, MOD(D2 - 1, COUNTA(A2:A5)) + 1))Direct K-th Option Lookup With Explicit Bounds Guard
Retrieves the exact K-th option (D3) from A2:A5 (the exact inner call used by INDEX(A2:A5, RANDBETWEEN(1, COUNTA(A2:A5)))).
=IF(OR(D3 < 1, D3 > COUNTA(A2:A5)), "Index Out of Range", INDEX(A2:A5, D3))Try the example
Use the original values or change them to test your understanding.