Array Formula Index Match to Select Cell Based on Second, Third, and Fourth Minimum Value

Question

I have a cell that currently uses an array formula to return the name associated with the minimum hours worked for all my employees. However, what I am trying to do now is write an array formula that lists the three next employees with lowest hours. I have written a formula similar to this in the past, but can't seem to get the two formulas to appropriately match up.

My current return minimum employee formula in G5:

={INDEX(A:A,MATCH(MIN(IF(B:B=G3,IF(C:C>=$G$2,D:D)))&G3,D:D&B:B,0))}

Here is an example of my data:

...and now I'm attempting to incorporate in into the following array formula that would return a list of qualifying results as I dragged it down a column:

={(IF(ROWS(G$7:G7)<=F$8,INDEX($A$2:$A$8,SMALL(IF(Employees!$B$2:$B$8=$G$3,ROW($A$2:$A$8)-ROW($A$2)+1),ROWS(G$7:G7))),""))}

Currently, this array formula is only set up to match on position title and not the other qualifiers that I need from my minimum employee formula. How can I mesh the two formulas correctly? Thank you for any and all help and please, let me know if you need any clarification.

The ideal array result would show Boris and two blanks in consecutive rows in the Next 3 Employees chart.

Answer 1

Set your page up like this:

With the ranking in column F.

Then it is a quick modification of the last formula. Instead of MIN we use Small. The k part of the small equation is the ranking number:

=INDEX(A:A,MATCH(SMALL(IF(B:B=$G$3,IF(C:C>=$G$2,D:D)),F5)&$G$3,D:D&B:B,0))

This goes in G5. Is confirmed with ctrl-shift-enter. Then copied down for rows.

If do not want the errors to show then wrap it in IFERROR:

=IFERROR(INDEX(A:A,MATCH(SMALL(IF(B:B=$G$3,IF(C:C>=$G$2,D:D)),F5)&$G$3,D:D&B:B,0)),"NO MATCHES")