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:
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:
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)) |