Forum Discussion
Solved
help with combined random and lookup
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
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.
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.
