Title: Generate Random List based on Criteria from Separate Column in Excel
Tags: excel;random;excel-formula
Question: I have two columns : one that has paragraph numbers listed and another is its Frequency - how often it's surveyed (i.e. Annually, Monthly or N/A).
I want to search the Frequency column to randomly select any row that is not N/A and return the number listed in the previous column for that random selection.
I know how to randomly select and return the paragraph number just based on a search of that column:
=INDEX(Master[PWS-4],RANDBETWEEN(1,ROWS(Master[PWS-4])),1)
But this won't search based upon the criteria in the Frequency column, of course.  I then tried adding an IF function:
=IF(Master[Frequency]<&gt;"N/A",INDEX(Master[PWS-4],RANDBETWEEN(1,ROWS(Master[PWS-4])),1),"")
I feel like adding the IF function first isn't the right step (and it certainly isn't getting me the result I want) but I'm unsure how to successfully return the result from one column based upon the random selection from another based on criteria.  Any help would be greatly appreciated!

Here is the accepted answer: Here is an example:
Formula in D2:
=INDEX(A1:A10,AGGREGATE(15,3,(B2:B10<&gt;"N/A")*ROW(B2:B10),RANDBETWEEN(COUNTIF(B2:B10,"N/A")+1,COUNTA(B2:B10))))
Apply ranges to your situation.
Comment for this answer: @Ashley_L, The reason why this works is that I created an array of rownumbers that contain other values than `N/A`. The array returned looks like `{2,3,0,5,6,0,8,9,0}`. Then we take `SMALL` to return nth smalles value. For that parameter we take `RANDBETWEEN` the number of values that are `N/A` using `COUNTIF()` + 1 for lowbound, and the max number possible through `COUNTA`. The +1 is there because without `SMALL` would possibly return the 3th smallest value, being 0. We only want the 4th smallest till max of 9. I hope this made sense. If it has answered your question, feel free to accept it!
Comment for this answer: Fantastic!  Can you explain to me why you took a COUNTIF+1 and why RANDBETWEEN(4,9) works?
Comment for this answer: Does the function overall operate like this?  Somehow the RANDBETWEEN part of the function returns a value that doesn't have N/A associated with it and in return it pushes that value to the AGGREGATE function to find that Nth SMALLest (random) value of the array.  If so, does the AGGREGATE function ignore 0 values?


{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 1, 'CommentCount': 0, 'ContentLicense': 'CC BY-SA 4.0', 'CreationDate': '2019:00:07/17/19', 'FavoriteCount': None, 'PostTypeId': 1, 'Tags': '<excel><random><excel-formula>', 'Title': 'Generate Random List based on Criteria from Separate Column in Excel', 'ViewCount': 1530, 'id': 57080426, 'user_AboutMe': '', 'user_AccountId': 1696686.0, 'user_DisplayName': 'Ashley_L', 'user_Reputation': 9.0, 'user_Views': 4.0, 'user_id': 1557933.0}}