I have a Google Sheet that has the following tabs:
Shops|| This contains a few drop-down menus!Data_ShopInventory|| This contains columns A-I and is effectively the 'database'!Data_Shops|| This contains some randomly generated stuff based on the other tabs and then the Shops tab pulls from it
I have the following formula:
={"Items";SORTN(FILTER('!Data_ShopInventory'!$A$2:$A, SEARCH(Shops!$C$3, '!Data_ShopInventory'!$I$2:$I)), 5, 0, ARRAYFORMULA(RANDBETWEEN(TRANSPOSE(SPLIT(REPT("1,",COUNTA(FILTER('!Data_ShopInventory'!$A$2:$A, search(Shops!$C$3, '!Data_ShopInventory'!$I$2:$I)))), ",", 0, 1)), 10000)), 1)}
This works as such: Based on the drop-down menu selected on the Shops tab (cell C3), it will then randomly pull 5 items from the !Data_ShopInventory (Col A) tab.
In the !Data_ShopInventory tab, I have a column (Town Size) that contains text as follows:
- Large
- Medium
- Medium,Large
- Small,Medium
- Small
- Small,Medium,Large
I need to make my vlookup that returns the random item also take this into account.
On the Shops tab, there is another drop-down menu (cell C4) that has the options of Small, Medium or Large.
So if I select 'Large', I want the vlookup to only return random items where C3 matches (like it currently does in the above formula) and where the corresponding cell in Col G contains the selection from the drop-down in C4.
Tried a bunch and I just can't figure it out... It's 1:30am and I am tired. Anyone able to help?
Here is a link to the sheet:
update:
={"Items";SORTN(FILTER('!Data_ShopInventory'!$A$2:$A, SEARCH(Shops!$C$3, '!Data_ShopInventory'!$I$2:$I),REGEXMATCH('!Data_ShopInventory'!G2:G, Shops!C4)), 30, 0, ARRAYFORMULA(RANDBETWEEN(TRANSPOSE(SPLIT(REPT("1,",COUNTA(FILTER('!Data_ShopInventory'!$A$2:$A, search(Shops!$C$3, '!Data_ShopInventory'!$I$2:$I),REGEXMATCH('!Data_ShopInventory'!G2:G, Shops!C4)))), ",", 0, 1)), 10000)), 1)}
CodePudding user response：
if I select 'Large', I want the vlookup to only return random items where C3 matches (like it currently does in the above formula) and where the corresponding cell in Col G contains the selection from the drop-down in C4.
So if I'm understanding correctly you want to return five random items from !Data_ShopInventory where ShopType matches Shops!C3 and TownSizeAvailable matches Shops!C4. If that's what you are after, this formula will do the trick.
={"Items";SortN(Query('!Data_ShopInventory'!A2:I,"select A where I='"&Shops!C3&"' and G like '%"&Shops!C4&"%'",0),5,,RandArray(SumProduct(('!Data_ShopInventory'!I2:I=Shops!C3)*RegexMatch('!Data_ShopInventory'!G2:G,Shops!C4))),0)}