Share via

Can I make a random selection using two qualifiers?

Anonymous
2019-06-04T18:49:27+00:00

I have been looking for a way to make a random selection using two qualifiers.. does any have any ideas? 

Below is an example, I have $875 to spend on a new piece of furniture, I already own a bed and lamp but want excel to randomly select an item to purchase that I both need, and can afford. 

I have figured out a way to do 1 qualifier by using index, match, aggregate 14, with a random number,  but I can not for the life of me figure out 2 qualifiers

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    Comments have been turned off. Learn more

  2. Anonymous
    2019-06-04T21:40:04+00:00

    Hi,

    Say, your "Items" table is in cells A4:D10. Try this formula with Ctrl+Shift+Enter:

    =INDEX(INDEX($A$5:$A$10,SMALL(IF($D$5:$D$10="Y",IF($C$5:$C$10="Y",ROW($A$5:$A$10)-ROW($A$4),""),""),ROW(INDIRECT("A1:A"&COUNTA($A$5:$A$10))))),RANDBETWEEN(1,SUMPRODUCT(--($C$5:$C$10="Y")*($D$5:$D$10="Y"))))

    This works in Office 365, but I can't test if this runs in Excel 2016.

    Was this answer helpful?

    0 comments