Forum Discussion

AdrienneVH's avatar
AdrienneVH
Copper Contributor
Oct 28, 2021

RANDBETWEEN and INDEX with a check on if result has been used before

I'm trying to create a book randomizer to choose a random book to read, but then to exclude it from the options if it's been read before. I have a list of Books in Literature!A:A and in Literature!B...
excel
Formulas and Functions
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Oct 29, 2021

AdrienneVH 

For such layout

formula could be

=LET(
  N,      ROWS(allBooks[ID]),
  k,      SEQUENCE(N),
  array,  RANDARRAY(N,1,1,N,0),
  random, SORTBY(k, array),
  bookID, @FILTER(random,  NOT(XLOOKUP( INDEX(random,k), allBooks[ID], allBooks[Read]) )),
  INDEX(allBooks[Book], bookID) )

Resources