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

Copper Contributor

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:B =COUNTIF('2021'!D:D,A4) to check if it's been read already this year from '2021'!D:D.

 

I would like to modify this formula below to exclude books that have a value of 1 in the Checking column. 
=INDEX(Literature!A1:A100,RANDBETWEEN(1,COUNTA(Literature!A1:A100)))

 

Thank you for your help!

1 Reply

@AdrienneVH 

For such layout

image.png

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