Search code examples
google-sheetsgoogle-sheets-formula

How to return merged column header based on match in column A and minimum value in row that


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


Solution

  • 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},)))))))
    
    • Clear the column E Grocery List_tab of any existing formula(s) prior to applying the formula in Cell_B2 so as for the formula to expand freely

    enter image description here