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

Occasional Visitor

# 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: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

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

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