I’m trying to pick a random row from my dataset shown below.
I’m wanting to pick out 2 rows from the list at random, with column C being the column that is randomized and column D to match with the value show in column C.
Like this:
Dotty Davies | Kay, Changing Ends 1×03
Lucas Royalty | Young Jonah, 9-1-1 5×17
currently I’m using this formula
=INDEX(C1:D10,RANDBETWEEN(1,ROWS(C1:C10)),1)
=INDEX(D1:E10,RANDBETWEEN(1,ROWS(D1:D10)),1)
Which is showing this:
Dotty Davies | Redbird, Batwheels 2×10
Lucas Royalty | Self, Sesame Street
Column C appears to be working, however Column D isn’t matching up with the original value in the row pulled from Column C
How do I do this correctly using Excel???
| C | D |
|---|---|
| Kylee Levien | Teen Autumn, Outer Range 2×06 |
| Crew Kingston Miskel | Redbird, Batwheels 2×10 |
| Lucas Royalty | Young Jonah, 9-1-1 5×17 |
| Kiefer O’Reilly | Alexi Zinman, Alert: Missing Persons Unit 2×08 |
| Oliver Savell | Young Alan Carr, Changing Ends 1×03 |
| Dotty Davies | Kay, Changing Ends 1×03 |
| Nariyah Ann SimpsonBoushee | Self, Sesame Street |
| Aiden Stoxx | Tayo Abiola, The Good Doctor 7×08 |
| Charlie Storey | Young Sedona Jones, Sullivan’s Crossing 2×02 |
| Hannah Bos | Hannah Devlin, Dead Boy Detectives 1×03 |
>Solution :
Try using the following formula:
=TAKE(SORTBY(C1:D10,RANDARRAY(ROWS(C1:D10))),2)
Shown demo in web version of Excel!
Or,
=CHOOSEROWS(SORT(C1:D10,RANDARRAY(ROWS(C1:D10)),1),SEQUENCE(2))
Using Structured References aka Tables:
=CHOOSEROWS(SORT(Table1,RANDARRAY(ROWS(Table1)),1),SEQUENCE(2))