Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
How to take a random sample from a list in Excel and Google Sheets
Excel & Google Sheets
=INDEX(A2:A4,MATCH(SMALL(B2:B4,1),B2:B4,0))✓
Verified example
Computed by a real spreadsheet engine on the sample data below.
| Name | RandNum |
| Alice | 0.72 |
| Bob | 0.31 |
| Carol | 0.55 |
=INDEX(A2:A4,MATCH(SMALL(B2:B4,1),B2:B4,0))→Bob
Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
Step by step
- 1Enter your list in column A (e.g., names in A2:A20).
- 2In column B, enter =RAND() next to each name and copy it down — this assigns a random number to each row.
- 3In column C, enter =RANK(B2,$B$2:$B$20,1) to rank each random number from smallest to largest.
- 4Retrieve the top N sample items using =INDEX($A$2:$A$20,MATCH(ROW(A1),$C$2:$C$20,0)) copied down for as many rows as you need.
Tips
- Press F9 (or Cmd+Shift+F9 on Mac) to manually recalculate and generate a new random sample without editing any cells.
Need it for your exact data?
Describe your columns in plain English and get the precise formula for your sheet, with the right Excel or Sheets syntax.
Frequently asked
How do I get a random sample of 5 from a 50-item list in Excel 365?
Use =INDEX(A2:A51,SORTBY(SEQUENCE(50),RANDARRAY(50)),SEQUENCE(5)) — or more simply, add a RAND column, sort by it, and take the top 5.
Will RAND() give me duplicate ranks?
Duplicates are theoretically possible but astronomically rare. Use RANK.EQ with a tiebreaker if needed: =RANK(B2,$B$2:$B$20)+COUNTIF($B$2:B2,B2)-1.