Vlookup AND check if cell contains other critera
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: Google Sheet Copy
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}, )), ))}
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)}