I have a table listing prices broken down by unit cost and retailer.
Item | $ | Size | $/oz or Unit | $ | Size | $/oz or Unit | $ | Size | $/oz or Unit |
---|---|---|---|---|---|---|---|---|---|
all purpose flour | $8.00 | 10 | $0.80 | $15.00 | 9 | $1.67 | $8.00 | 5 | $1.60 |
apples | $7.00 | 3 | $2.33 | $11.00 | 5 | $2.20 | $10.00 | 9 | $1.11 |
applesauce | $5.00 | 9 | $0.56 | $13.00 | 5 | $2.60 | $6.00 | 3 | $2.00 |
bacon | $4.00 | 3 | $1.33 | $5.00 | 2 | $2.50 | $5.00 | 7 | $0.71 |
bagels | $15.00 | 3 | $5.00 | $12.00 | 6 | $2.00 | $3.00 | 6 | $0.50 |
bell pepper | $7.00 | 3 | $2.33 | $13.00 | 2 | $6.50 | $5.00 | 4 | $1.25 |
Retailer is a merged cell spanning $, Size, and $/oz or Unit. $/oz or unit is an arrayformula dividing the previous 2 columns.
I am trying to get the minimum cost per unit and return the retailer, size, and cost on a second sheet.
I've tried using query's and index match functions, but I cannot wrap my head around how to query against multiple criteria.
This is a copy of the sheet to clarify. https://docs.google.com/spreadsheets/d/1mRYX7r-ExODhs57GOpctGXLxSmNf5UEJwWw0KJmb6eU/edit?usp=sharing
You may try:
=let(common_,Stores!A3:A, group_,Stores!B3:S, header_,Stores!B1:S1, size_,3,
Σ,reduce(,sequence(columns(group_)/size_,1,1,size_),lambda(a,c,vstack(a,{common_,let(Λ,choosecols(header_,c),wrapcols(Λ,rows(group_),Λ)),choosecols(group_,sequence(size_,1,c))}))),
〱,sort(filter(Σ,index(Σ,,1)<>""),5,1), map(A2:A,lambda(丨,if(丨="",,index(ifna(vlookup(丨,〱,{2,3,4,5},)))))))
Grocery List_tab
of any existing formula(s) prior to applying the formula in Cell_B2
so as for the formula to expand freely