Vlookup AND check if cell contains other critera

user_4128307 0
Viewed 49

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

vlookup google-sheets-formula google-sheets if-statement sorting
2 Answers

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

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

user_178873010
Related