INDEX-MATCH-SMALL-COUNTIFS returning duplicated results

Hello,

I created a formula based on many post's helping users to get the top X rows of a table depending on a value in a specific column af the row.

I have an Epics table containing an Epic element in each line. One of the columns correspond to the Cost Deviation, another one to the FixVersion. I want to get the Top 5 Epics Keys by Cost Deviation for a given FixVersion and display them in another 5 cells.

The array formula I've created is as follows :

In french:
{=SIERREUR(INDEX(JEpicsTable[[Key]:[Key]];EQUIV(PETITE.VALEUR(NB.SI.ENS(JEpicsTable[[Cost Deviation]:[Cost Deviation]];">"&JEpicsTable[[Cost Deviation]:[Cost Deviation]];JEpicsTable[[FixVersions]:[FixVersions]];"*"&C$4&"*");LIGNE(1:1));NB.SI.ENS(JEpicsTable[[Cost Deviation]:[Cost Deviation]];">"&JEpicsTable[[Cost Deviation]:[Cost Deviation]];JEpicsTable[[FixVersions]:[FixVersions]];"*"&C$4&"*");0));"")}

In french simplified:
{=SIERREUR(INDEX(JEpicsTable[Key];EQUIV(PETITE.VALEUR(NB.SI.ENS(JEpicsTable[Cost Deviation];">"&JEpicsTable[Cost Deviation];JEpicsTable[FixVersions];"*"&C$4&"*");LIGNE(1:1));NB.SI.ENS(JEpicsTable[Cost Deviation];">"&JEpicsTable[Cost Deviation];JEpicsTable[FixVersions];"*"&C$4&"*");0));"")}

In english simplified:
{=IFERROR(INDEX(JEpicsTable[Key],MATCH(SMALL(COUNTIFS(JEpicsTable[Cost Deviation],">"&JEpicsTable[Cost Deviation],JEpicsTable[FixVersions],"*"&C$4&"*"),ROW(1:1)),COUNTIFS(JEpicsTable[Cost Deviation],">"&JEpicsTable[Cost Deviation],JEpicsTable[FixVersions],"*"&C$4&"*"),0)),"")}

The value of the given FixVersion is in C$4.

I am supposed to obtain the Top 5 elements by incrementing the ROW(1:1) like this : ROW(1:1), ROW(2:2), ROW(3:3)... but actually I obtain some duplicated keys in my results like A, A, A, B, B, C, D, E, F, F, G...
All correct keys are there and in the correct order, but since there are some duplicated lines in the results, I can't obtain dynamically the Top 5 by just incrementing the row index from 1 to 5.

Is there any way to filter duplicates (Duplicated keys in the results not duplicated Cost Deviations in the considered data) ? or to prevent genereting them by adapting the formula ?
I'm new into array formulas and I'm not sure I completely understanding what Excel is internally doing whith the CountIfs. Evaluate formula didn't help me to get it.

Thanks in advance for your help,
Xavier

Update 2021/08/30: Excel example has been attached to the post. It shows how repeated results are shown in the list, as well as some bigger values than others that are ignored. Thanks in advance for your help.

Welcome to the forum.

There are instructions at the top of the page explaining how to attach your sample workbook.

Hi,

In your file, why is the second returned value of 2 (cell F3) marked as "OK"? That Key is associated with Fix Version "V2", not "V1", so I would have thought it should be ignored. Or am I missing something?

Regards

In F2:

=IF(ROWS(F$1:F1)>COUNTIFS(JEpicsTable[Fix Versions],"*V1*"),"",LOOKUP(1,0/FREQUENCY(0,1/(1+COUNTIFS(JEpicsTable[Cost Deviation],"<="&JEpicsTable[Cost Deviation])*(COUNTIFS(F$1:F1,JEpicsTable[Key])=0)*ISNUMBER(SEARCH("V1",JEpicsTable[Fix Versions])))),JEpicsTable[Key]))

and copied down.

Regards

You're Welcome. Glad to help . Thank You for the feedback

Use with each other ISNUMBER not only contains numbers + SEARCH and FIND Can distinguish between uppercase and lowercase

SHIFT+ENTER pls change to CTRL+SHIFT+ENTER
Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).