excelrandomexcel-formularowmatching

excel - how to pick the whole row using randbetween

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 1x03

Lucas Royalty | Young Jonah, 9-1-1 5x17

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 2x10

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???

Dataset

C D
Kylee Levien Teen Autumn, Outer Range 2x06
Crew Kingston Miskel Redbird, Batwheels 2x10
Lucas Royalty Young Jonah, 9-1-1 5x17
Kiefer O'Reilly Alexi Zinman, Alert: Missing Persons Unit 2x08
Oliver Savell Young Alan Carr, Changing Ends 1x03
Dotty Davies Kay, Changing Ends 1x03
Nariyah Ann SimpsonBoushee Self, Sesame Street
Aiden Stoxx Tayo Abiola, The Good Doctor 7x08
Charlie Storey Young Sedona Jones, Sullivan's Crossing 2x02
Hannah Bos Hannah Devlin, Dead Boy Detectives 1x03
Solution

  • Try using the following formula:

    enter image description here

    =TAKE(SORTBY(C1:D10,RANDARRAY(ROWS(C1:D10))),2)

    Shown demo in web version of Excel!

    enter image description here

    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))