help with combined random and lookup

[RubiconXDS]

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. 

Brief,

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 · Accepted solution]

@RubiconXDS 

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.

[#3]

@hans

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