Hello!
I am trying to get the product names by store that are the "bottom 3" in the following table:
<tbody>
</tbody>
Using the following array formula:
=INDEX($A$2:$A$11,MATCH(SMALL(IF($B$2:$B$11=$H$4,IF($C$2:$C$11>0,$D$2:$D$11)),$F5)&$H$4,$D$2:$D$11&'Sample Mr. Excel'!$B$2:$B$11,0))
I get this results:
<tbody>
</tbody>
The problem is that I can't figure out how to make the formula not to show the same product name when value is duplicated.
Please, help!
I am trying to get the product names by store that are the "bottom 3" in the following table:
Product | Store | Budget | %Achieved |
Soda | A | 10.00 | 46% |
Juice | A | 20.00 | 0% |
Coffee | A | 25.00 | 0% |
Water | A | 30.00 | 0% |
Tea | A | 40.00 | 0% |
Soda | B | 5.00 | 10% |
Juice | B | 15.00 | 90% |
Coffee | B | 60.00 | 40% |
Water | B | 20.00 | 30% |
Tea | B | 10.00 | 0% |
<tbody>
</tbody>
Using the following array formula:
=INDEX($A$2:$A$11,MATCH(SMALL(IF($B$2:$B$11=$H$4,IF($C$2:$C$11>0,$D$2:$D$11)),$F5)&$H$4,$D$2:$D$11&'Sample Mr. Excel'!$B$2:$B$11,0))
I get this results:
Store | ||
Ranking | A | B |
1 | Juice | Tea |
2 | Juice | Soda |
3 | Juice | Water |
<tbody>
</tbody>
The problem is that I can't figure out how to make the formula not to show the same product name when value is duplicated.
Please, help!