I have a list of hundreds of values organized as follows:

enter image description here

I'd like to have a cell generate a random Item for which there is no corresponding Order value.

  • 1
    1. Nah, I want, for example, Cell C1 to show a random Item that has no Order value. The blanks will get filled in manually as Items are addressed, but the list is massive and picking an Item at human-random is seeing some near the bottom largely ignored. 2. The Order field relates to the order in which the Item was addressed, so in the example, Item 18 was worked first, Item 9 2nd, etc. There's no real limit to how many will be on the list, but it will probably be fewer than a thousand. The name are all fuzzed here, but each Item has a specific name on the real sheet. – Nick Aug 20 '20 at 19:01
  • Please add a brief description of your search/research efforts as is suggested in How to Ask. – Rubén Aug 21 '20 at 0:42

See Test sheet

Formula in C1 returns random value from list of items with no order number


FILTER() part filters items with no order number.

COUNTA() - counts number of items in filtered list.

RANDBETWEEN() - generates random value from 1 to number of items in filtered list.

INDEX() - returns that random item.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.