Search code examples
google-sheetsgoogle-sheets-formulasequencevlookuparray-formulas

Google Sheet Arrayformula Lookup/Filter Multi Criteria

I have a list of approximately 7,000 products in which I am trying to create an arrayformula that will lookup each product's recycling fee and deposit amount based on several criteria provided within another table. I was able to create the desired formula which is posted below, however I have been unable to find a solution that works in an arrayformula so that each row(product)is updated.

https://docs.google.com/spreadsheets/d/1YgBG6ANGHXNUGqemxS9Gklon4bMtD1GCGqlQRKCPvUU/edit#gid=0

[Example Picture] https://drive.google.com/file/d/1sSX6dakHR9fbuNByQmEyDOoUOOQl39Ot/view?usp=sharing

Detailed Information In the Price table(lookup table), you will find a list of the types of deposits which are based on the container type and size. As the deposit rates are based on the container type and size, you will find that the container type column has duplicates. For example there is a row for Glass bottles that are smaller than 1 litre and another row for glass bottles that are greater than 1 litre. Each row contains the container type(ColA), minimum size(ColB) and maximum size (ColC) and has one column providing the value for products that are non-alcoholic (ColD) and another column for those that contain alcohol(ColE).

In the Product List each product(row) has the Container Type (ColB), whether it contains alcohol(ColC) and the size (Named Range Value in ColD). In order to determine the correct value I need to query the product's container type(ColA) in the Price Table(ColA) which will return multiple matches as the different matches will be for different product sizes. I then need to filter that list to ensure that my product size(ColD) fits within the tables min size(>=ColB) and max size(<=ColC). Then depending on whether the product contains alcohol(ColC) I can determine whether I need the value in Price Table ColD or ColE.

While multi-criteria vlookups can commonly be solved by using VLOOKUP(A1:A&B1:B,A1:A&B1:B,2,FALSE), this approach does not work for my problem as one of the criteria fields is based on the maximum size of the product and another field is based on the minimum size.

The formula which works;

FILTER('Price Table'!A:G,D2*1>='Price Table'!B:B*1,D2*1<='Price Table'!C:C*10,B2='Price Table'!A:A)
,IF(C2="ALCOHOL",5,4),FALSE)```
Solution

  • try:

    =ARRAYFORMULA(IFNA(VLOOKUP(IFNA(VLOOKUP(B3:B, 
 {UNIQUE(FILTER(Q2:Q, Q2:Q<>"")), 
 SEQUENCE(COUNTUNIQUE(Q2:Q), 1, 1, 100)}, 2, 0))&D3:D*1, {IFNA(VLOOKUP(Q2:Q, 
 {UNIQUE(FILTER(Q2:Q, Q2:Q<>"")), 
 SEQUENCE(COUNTUNIQUE(Q2:Q), 1, 1, 100)}, 2, 0))&R2:R*1, T2:W}, 
 IF(C3:C="ALCOHOL", {3,5}, {2,4}), 1)))

    enter image description here