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

%3CLINGO-SUB%20id%3D%22lingo-sub-2895999%22%20slang%3D%22en-US%22%3ERANDBETWEEN%20and%20INDEX%20with%20a%20check%20on%20if%20result%20has%20been%20used%20before%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2895999%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%20class%3D%22%22%3EI'm%20trying%20to%20create%20a%20book%20randomizer%20to%20choose%20a%20random%20book%20to%20read%2C%20but%20then%20to%20exclude%20it%20from%20the%20options%20if%20it's%20been%20read%20before.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%22%3EI%20have%20a%20list%20of%20Books%20in%20%3CEM%3ELiterature!A%3AA%3C%2FEM%3E%20and%20in%20%3CEM%3ELiterature!B%3AB%3C%2FEM%3E%26nbsp%3B%3DCOUNTIF(%3CSPAN%3E'2021'!D%3AD%3C%2FSPAN%3E%2C%3CSPAN%3EA4%3C%2FSPAN%3E)%20to%20check%20if%20it's%20been%20read%20already%20this%20year%20from%20%3CEM%3E'2021'!D%3AD%3C%2FEM%3E.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%22%3EI%20would%20like%20to%20modify%20this%20formula%20below%20to%20exclude%20books%20that%20have%20a%20value%20of%201%20in%20the%20Checking%20column.%26nbsp%3B%3CBR%20%2F%3E%3DINDEX(%3CSPAN%3ELiterature!A1%3AA100%3C%2FSPAN%3E%2CRANDBETWEEN(%3CSPAN%20class%3D%22%22%3E1%3C%2FSPAN%3E%2CCOUNTA(%3CSPAN%3ELiterature!A1%3AA100%3C%2FSPAN%3E)))%3CBR%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%22%3EThank%20you%20for%20your%20help!%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2895999%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2898445%22%20slang%3D%22en-US%22%3ERe%3A%20RANDBETWEEN%20and%20INDEX%20with%20a%20check%20on%20if%20result%20has%20been%20used%20before%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2898445%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1198817%22%20target%3D%22_blank%22%3E%40AdrienneVH%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Euse%20randbetween%20using%20a%20table%20of%20values%3A%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fexceljet.net%2Fformula%2Frandom-value-from-list-or-table%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3EExcel%20formula%3A%20Random%20value%20from%20list%20or%20table%20%7C%20Exceljet%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Euse%20an%20if%20formula%20to%20make%20the%20value%20appear%20or%20disappear%20from%20the%20table%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

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