How to pick a random item from a list
✓ Verified in LibreOffice 25.8.7.3 ✓ Verified in Google Sheets (2026-08-30)Random winner, random assignment, random sample of one.
The formula
| App | Formula | Notes |
|---|---|---|
| Excel (desktop) | =INDEX(A2:A100,RANDBETWEEN(1,COUNTA(A2:A100))) | Re-rolls on every recalculation — copy → Paste Values to freeze the winner. |
| Google Sheets | =INDEX(A2:A100,RANDBETWEEN(1,COUNTA(A2:A100))) | Identical. |
| LibreOffice Calc | =INDEX(A2:A100,RANDBETWEEN(1,COUNTA(A2:A100))) | Identical. |
How it works
RANDBETWEEN rolls a row number from 1 to the list's length (COUNTA keeps it sized to the data) and INDEX fetches that entry. (The verification asserts the deterministic property — whatever gets picked is genuinely from the list — since the pick itself changes every run.) It re-rolls on every edit, so freeze results with Paste Special → Values. For several picks WITHOUT repeats, modern versions can use =TAKE(SORTBY(A2:A100,RANDARRAY(COUNTA(A2:A100))),3) — shuffle, then take three.
Verified, not just documented
We ran =COUNTIF(A2:A4,INDEX(A2:A4,RANDBETWEEN(1,3)))>0 in LibreOffice 25.8.7.3 (headless, with forced recalculation) and it returned True — exactly the expected result. We then ran the same formulas in Google Sheets, executed 2026-08-30: a formula-only workbook goes into Google Drive, which converts it to a Sheet and recalculates every formula with Google’s own engine, and comes back out as .xlsx carrying the values Google computed. It returned True for the worked example, the same value LibreOffice produced. Both engines’ numbers on this page are executed results. The Excel formula follows Microsoft’s official documented syntax — we do not run desktop Excel.
Functions used
INDEX · RANDBETWEEN · COUNTA — see full Excel, Google Sheets & LibreOffice compatibility for each.
Related recipes
- How to auto-number rows with a formula
- How to do a case-sensitive lookup
- How to count non-blank (non-empty) cells
- How to count the number of unique values in a range
- How to look up the closest match (tax brackets, tiers, grades)