Index Match Small with duplicate values

Gigi007

New Member
Joined
Apr 23, 2018
Messages
2
Hello!

I am trying to get the product names by store that are the "bottom 3" in the following table:

Product Store Budget %Achieved
SodaA10.0046%
JuiceA20.000%
CoffeeA25.000%
WaterA30.000%
TeaA40.000%
SodaB5.0010%
JuiceB15.0090%
CoffeeB60.0040%
WaterB20.0030%
TeaB10.000%

<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
RankingAB
1JuiceTea
2JuiceSoda
3JuiceWater

<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!
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Welcome to the board!

Maybe:

ABCDEFGHI
1ProductStoreBudget%Achieved
2SodaA1046%
3JuiceA200%Store
4CoffeeA250%RankingAB
5WaterA300%1JuiceTea
6TeaA400%2CoffeeSoda
7SodaB510%3WaterWater
8JuiceB1590%
9CoffeeB6040%
10WaterB2030%
11TeaB100%

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet7

Array Formulas
CellFormula
H5{=IFERROR(INDEX($A$2:$A$11,MATCH(AGGREGATE(15,6,IF($B$2:$B$11=H$4,1,NA())*IF($C$2:$C$11>0,1,NA())*IF(COUNTIF(H$4:H4,$A$2:$A$11)=0,1,NA())*$D$2:$D$11,1),IF($B$2:$B$11=H$4,1,NA())*IF($C$2:$C$11>0,1,NA())*IF(COUNTIF(H$4:H4,$A$2:$A$11)=0,1,NA())*$D$2:$D$11,0)),"")}

<thead>
</thead><tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

AGGREGATE was added in Excel 2010, so you'll need that version or newer for this to work. If you don't, I can probably adapt it.
 
Upvote 0
For store A you have 4 items that are in the bottom 3.
In the example below the formula in B16 counts the number of items in the bottom 3 by store. Drag this formula across.
The formula in B17 will list the items. Drag this formula down and across as needed.

These formulas are array formulas and must be entered with CTRL-SHIFT-ENTER.
Excel Workbook
ABCD
1ProductStoreBudget%Achieved
2SodaA1046%
3JuiceA200%
4CoffeeA250%
5WaterA300%
6TeaA400%
7SodaB510%
8JuiceB1590%
9CoffeeB6040%
10WaterB2030%
11TeaB100%
12
13
14
15StoreAB
16# Items in bottom 343
171JuiceTea
182CoffeeSoda
193WaterWater
204Tea
215
Sheet
 
Upvote 0
Looks like my formula in B17 above got cut off.

Code:
IF($A17>B$16,"",INDEX($A$2:$A$11,MATCH(SMALL(IF($B$2:$B$11=B$15,IF($C$2:$C$11> 0,RANK($D$2:$D$11,$D$2:$D$11,1)+ROW($D$2:$D$11)/1000)),$A17),IF($B$2:$B$11=B$15,IF($C$2:$C$11> 0,RANK($D$2:$D$11,$D$2:$D$11,1)+ROW($D$2:$D$11)/1000)),0)))
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,972
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top