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

	A	B	C	D	E	F	G
1	Color	Class	Value			Blue
2	Green	B	-90		1	-464	R
3	Yellow	C	-50		2	-27	I
4	Yellow	D	-49		3	-20	L
5	Yellow	E	-41		4	-16	M
6	Orange	F	-38		5	-16	P
7	Green	G	-35
8	Green	H	-30
9	Blue	I	-27
10	Green	J	-25
11	Yellow	K	-21
12	Blue	L	-20
13	Blue	M	-16
14	Yellow	N	-19
15	Yellow	O	-19
16	Blue	P	-16
17	Blue	Q	-15
18	Blue	R	-464

<tbody>
</tbody>

Sheet6​

Array Formulas Cell Formula F2 {=SMALL(IF($A$2:$A$18=$F$1,$C$2:$C$18),E2)} G2 {=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)))} <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>

 

[thabhfy]

That worked perfectly, thank you for the quick reply