Position of Nth Smallest/Largest Value with Duplicates

[thabhfy]

I have a table with 3 columns. Column A is the color, column B is the class, and column C is the value. Classes are unique, and one color can have multiple values. The values can be the same.

I'm trying to find the lowest 5 values in the range and the corresponding class for an specific color. This is working fine with small, match, and index formulas:
Lowest value:

=SMALL(IF($A$4:$A$23=$F$1,$C$4:$C$23),1)

Corresponding class:

=INDEX($B$4:$B$23,MATCH(SMALL(IF($A$4:$A$23=$F$1,$C$4:$C$23),1),$C$4:$C$23,0))

I'm changing the k in small to be 1/2/3/4 or 5. Assuming color Blue is the criteria, For 1, it returns -464 as the value and R as the class... and so on.

My problem is when the value is the same. For 4, it returns -16 for value and M as the class. For 5 the same thing is returned, and for 6 it returns-15 for the value and Q for the class. I'd like the 5th lowest value to be -16, class P.

Table Example:

Color Class Value
Green B -90
Yellow C -50
Yellow D -49
Yellow E -41
Orange F -38
Green G -35
Green H -30
Blue I -27
Green J -25
Yellow K -21
Blue L -20
Blue M -16
Yellow N -19
Yellow O -19
Blue P -16
Blue Q -15
Blue R -464

 

[Weazel]

Assuming I'm understanding correctly then maybe something like...

Unknown[TABLE="class: grid, width: 400"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]Color[/TD]
[TD]Class[/TD]
[TD]Value[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD]Blue[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]Green[/TD]
[TD]B[/TD]
[TD="align: right"]-90[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]-464[/TD]
[TD]R[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]Yellow[/TD]
[TD]C[/TD]
[TD="align: right"]-50[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]-27[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]Yellow[/TD]
[TD]D[/TD]
[TD="align: right"]-49[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]-20[/TD]
[TD]L[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]Yellow[/TD]
[TD]E[/TD]
[TD="align: right"]-41[/TD]
[TD="align: right"][/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]-16[/TD]
[TD]M[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD]Orange[/TD]
[TD]F[/TD]
[TD="align: right"]-38[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]-16[/TD]
[TD]P[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD]Green[/TD]
[TD]G[/TD]
[TD="align: right"]-35[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD]Green[/TD]
[TD]H[/TD]
[TD="align: right"]-30[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD]Blue[/TD]
[TD]I[/TD]
[TD="align: right"]-27[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD]Green[/TD]
[TD]J[/TD]
[TD="align: right"]-25[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD]Yellow[/TD]
[TD]K[/TD]
[TD="align: right"]-21[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD]Blue[/TD]
[TD]L[/TD]
[TD="align: right"]-20[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD]Blue[/TD]
[TD]M[/TD]
[TD="align: right"]-16[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]14[/TD]
[TD]Yellow[/TD]
[TD]N[/TD]
[TD="align: right"]-19[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]15[/TD]
[TD]Yellow[/TD]
[TD]O[/TD]
[TD="align: right"]-19[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]16[/TD]
[TD]Blue[/TD]
[TD]P[/TD]
[TD="align: right"]-16[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]17[/TD]
[TD]Blue[/TD]
[TD]Q[/TD]
[TD="align: right"]-15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]18[/TD]
[TD]Blue[/TD]
[TD]R[/TD]
[TD="align: right"]-464[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]

Sheet6​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]F2[/TH]
[TD="align: left"]{=SMALL(IF($A$2:$A$18=$F$1,$C$2:$C$18),E2)}[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]G2[/TH]
[TD="align: left"]{=INDEX($B$2:$B$18,SMALL(IF($C$2:$C$18=F2,ROW($C$2:$C$18)-ROW($C$2)+1),COUNTIF($F$2:F2,F2)))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

 

[thabhfy]

That worked perfectly, thank you for the quick reply