Search code examples
sortingif-statementgoogle-sheetsgoogle-sheets-formulavlookup

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

Solution

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