Formula to get Lowest performing Students

[Lasantha]

Dear Team,

Kindly review the attached template. Data sheet contains students yearly performance.

Could you please give me a formula to get only Lowest performing students to Summery sheet.

Thanks for your time.

Lasantha.

 

[GraH - Guido]

Lasantha, do you have the filter function onboard?

then set-up is like = Filter(rangeAB, columnC="Lowest")

 

[Lasantha]

Lasantha, do you have the filter function onboard?

then set-up is like = Filter(rangeAB, columnC="Lowest")

Dear GraH - Guido,

I am using Excel 2013. I don't have that function.

Could you please give me a different formula.

Thank you.

 

[GraH - Guido]

one alternative using INDEX and AGGREGATE.

 

[bosco_yip]

Here is 2 options in non-array formula solution.

Put the formula in B3, copied right to C3 and all copied down >>

1] Using Index + Aggregate function

=IFERROR(INDEX(Data!B:B,AGGREGATE(15,6,ROW(Data!$D$2:$D$66)/(Data!$D$2:$D$66="Lowest Performing"),ROW($A1))),"")

2] Using Index + Mode.Mult function

=IFERROR(INDEX(Data!B:B,INDEX(MODE.MULT((Data!$D$2:$D$66<>"Lowest Performing")*{1,2}%+ROW($2:$66)),ROW($A1))),"")

Regards

 

[GraH - Guido]

Mode.mult?
*{1,2}%?

Needing a coffee first

 

[Excel Wizard]

Please try at B3 to get unique Student Number with Lowest performing

with Ctrl+Shift+enter
=IFERROR(INDEX(Data!B$2:B$66,MATCH("L",LEFT(Data!$D$2:$D$66,ISNA(MATCH(Data!$B$2:$B$66,$B$2:$B2,))),)),"")

or normal enter
=IFERROR(INDEX(Data!B$2:B$66,MATCH("L",INDEX(LEFT(Data!$D$2:$D$66,ISNA(MATCH(Data!$B$2:$B$66,$B$2:$B2,))),),)),"")

 

[Lasantha]

one alternative using INDEX and AGGREGATE.

Thank you

 

[Lasantha]

Here is 2 options in non-array formula solution.

Put the formula in B3, copied right to C3 and all copied down >>

1] Using Index + Aggregate function

=IFERROR(INDEX(Data!B:B,AGGREGATE(15,6,ROW(Data!$D$2:$D$66)/(Data!$D$2:$D$66="Lowest Performing"),ROW($A1))),"")

2] Using Index + Mode.Mult function

=IFERROR(INDEX(Data!B:B,INDEX(MODE.MULT((Data!$D$2:$D$66<>"Lowest Performing")*{1,2}%+ROW($2:$66)),ROW($A1))),"")

Regards

Thank you

 

[Lasantha]

Please try at B3 to get unique Student Number with Lowest performing

with Ctrl+Shift+enter
=IFERROR(INDEX(Data!B$2:B$66,MATCH("L",LEFT(Data!$D$2:$D$66,ISNA(MATCH(Data!$B$2:$B$66,$B$2:$B2,))),)),"")

or normal enter
=IFERROR(INDEX(Data!B$2:B$66,MATCH("L",INDEX(LEFT(Data!$D$2:$D$66,ISNA(MATCH(Data!$B$2:$B$66,$B$2:$B2,))),),)),"")

Thank you.