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

excel
Formulas and Functions

1 Reply

  • 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