help with combined random and lookup

Copper Contributor

Hi everyone, 


Its my first time posting and please let me know if you need any further information

I have scouted everywhere but cant seem to find the answer I am looking for anywhere. 



Document has 2 sheets. 1 sheet is master data and second sheet is working file. 


In working file A2 I want to change the category from a drop dropdown list. 



B2 should lookup the category in sheet 1 and provide a random within the selected category.




Eg. If I select DESSERT50 from dropdown list, B2 should return a random cell from column B, next to DESSERT50 column A. 


Not sure if this makes sense...  


I could really do with any help on this


Thank you

2 Replies
best response confirmed by VI_Migration (Silver Contributor)


Let's say the data are on the Master Data sheet in A2:B20.

Enter the following formula in B2 on the Working File sheet:


=IFERROR(LET(Data, FILTER('Master Data'!$B$2:$B$20, 'Master Data'!$A$2:$A$20=A2), Num, COUNTA(Data), INDEX(Data, RANDBETWEEN(1, Num))), "")


This can be filled down if required. Sample workbook attached.


Thank you a billion times for this. It works like a charm.