CODEWITHSUNDEEP
sortingif-statementgoogle-sheetsgoogle-sheets-formulavlookup
SL8t7
SL8t7

Reputation: 647

Vlookup AND check if cell contains other critera

I have a Google Sheet that has the following tabs:

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:

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: Google Sheet Copy

Upvotes: 2

Views: 109

Answers (2)

player0
player0

Reputation: 1

delete G1:J1 and use in G1:

={"Standard Price", "Discounted Price", "Inflated Price", "Limited Stock"; 
 ARRAYFORMULA(IF(LEN(F2:F), IFNA(VLOOKUP(F2:F, 
 FILTER('!Data_ShopInventory'!A:H, REGEXMATCH('!Data_ShopInventory'!G:G, Shops!C4)), {3,4,5,8}, )), ))}

enter image description here

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)}

Upvotes: 2

z..
z..

Reputation: 13156

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)}

Upvotes: 0

Related Questions