I have a Database sheet with a list of products and each product's details including but not limited to the product size, cost, department and more. On a separate sheet named PriceTiers, I have a list of price levels and each price level's requirement/settings such as minimum product size, maximum product size, minimum cost, maximum cost, department and more. My objective is to have an arrayformula on my Database sheet that uses the product details to look up the correct price level on the PriceTiers sheet similar to a vlookup with multiple criteria.
I am stumped as the more common approach of Vlookup(A1:A&H1:H&W1:W…. does not work because criteria includes multiple greater than and less statements such as larger than 1 litre and less than 10 litres. My attempts to use a filter in an arrayformula result in mismatched rows and query doesn’t seem to work because of the cell references that would have to repeat down all rows.
The following DGET formula does work but it does not work in an arrayformula which is my objective so that it doesn’t have to be repeatedly copied down 5,000 rows. Any help is appreciated. I have created a sample workbook here.
=IF(ROW(A1:A)=1,"Price Line Lookup",IFERROR(IF(A1="",,
IF(I1>50,
DGET(PriceTiers!A:AC,"Price Line Code",{"Subcategory Code", "SU-Min","SU-Max","Cost_Min","Cost_Max";H1, "<="&X1,">="&X1,"<="&AT1,">="&AT1}),
DGET(PriceTiers!A:AC,"Price Line Code",{"Subcategory Code","Size_Min","Size_Max","SU-Min","SU-Max","Cost_Min","Cost_Max";H1,"<="&W1,">="&W1,"<="&X1,">="&X1,"<="&AT1,">="&AT1})))))
Converting the drag-down formula to array-style:
=map(A:A,H:H,I:I,W:W,X:X,AT:AT,lambda(a,h,i,w,x,at,
IF(ROW(a)=1,"Price Line Lookup",IFERROR(IF(a="",,
IF(i>50,
DGET(PriceTiers!A:AC,"Price Line Code",{"Subcategory Code", "SU-Min","SU-Max","Cost_Min","Cost_Max";h, "<="&x,">="&x,"<="&at,">="&at}),
DGET(PriceTiers!A:AC,"Price Line Code",{"Subcategory Code","Size_Min","Size_Max","SU-Min","SU-Max","Cost_Min","Cost_Max";h,"<="&w,">="&w,"<="&x,">="&x,"<="&at,">="&at})))))))