SOLVED

# help with combined random and lookup

Copper Contributor

# 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

2 Replies
best response confirmed by VI_Migration (Silver Contributor)
Solution

# Re: help with combined random and lookup

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.

# Re: help with combined random and lookup

@hans

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

Accepted Solutions
best response confirmed by VI_Migration (Silver Contributor)
Solution

# Re: help with combined random and lookup

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.