excelexcel-formula

Retrieve article name and store of cheapest article for a given category in Excel

I previously asked how to conditional-format the smallest value in range of same category. So if I have a table with the unit price of various articles (products) of various categories of various stores, Excel automatically highlights the cheapest product across all stores and across the category (for example, to know the cheapest brand of cheese in all stores). My question was successfully answered by user @z.., by using conditional formatting with the custom formula =AND(C2=MIN(FILTER($C$2:$E$7,$A$2:$A$7=$A2)), C2 <> "") in the range =$C$2:$E$7 for the following Excel spreadsheet:

enter image description here

Now, I want to get the cell address of the corresponding cell, for further processing. Specifically, in a new table where I have a row for each category, I want to obtain in three different columns 1) the product name, 2) the store name, and 3) the unit price, of such cheapest product. This table would act as a summary, so that I can faster see which store sells the cheapest product. Like this:

enter image description here

So there are three different formulas I need. For obtaining the unit price, it's almost the same as the previous one used for conditional formatting; for cell D10 it's =MIN(FILTER($C$2:$E$7, $A$2:A7 = A10)), and for cell D11 it's =MIN(FILTER($C$2:$E$7, $A$2:$A$7 = A11)).

For the other two columns, I tried using the CELL, ROW and COLUMN functions, but can't get them to work. I also thought about two-way look-up using INDEX and MATCH functions, but I can't think of a way of defining a dynamic range. Any ideas?

Markdown of first table:

Category Article Store 1 Store 2 Store 3
Cat 1 Product 1 $30.00 $35.00 $27.00
Cat 1 Product 2 $29.00 $24.00 $25.00
Cat 1 Product 3 $32.00 $33.00 $35.00
Cat 2 Product A $4.00 $4.30 $4.50
Cat 2 Product B $5.00 $5.50 $4.50
Cat 2 Product C $3.50 $4.00 $3.75

Markdown of second table:

Category Article Store Unit price
Cat 1 =MIN(FILTER($C$2:$E$7, $A$2:A7 = A10))
Cat 2 =MIN(FILTER($C$2:$E$7, $A$2:$A$7 = A11))
Solution

  • So, try using the following formula, although the following won't take care about the ties, for that use the second method if required:

    enter image description here

    • Formula not involving any ties:

    =TEXTSPLIT(CONCAT(REPT(B$2:B$7&"-"&C$1:E$1,(A10=A$2:A$7)*(D10=C$2:E$7))),"-")

    • Formula includes ties:

    enter image description here

    Article:

    =TEXTJOIN(CHAR(10),1,REPT(B$2:B$7,(A10=$A$2:$A$7)*(D10=$C$2:$E$7)))

    Store:

    =TEXTJOIN(CHAR(10),1,REPT(C$1:E$1,(A10=$A$2:$A$7)*(D10=$C$2:$E$7)))