Get Top n Records based on Some Condition

[Pravinkarne.31]

Hello,

Required your help to get Top n Records basied on certain condition.

I have Unit_Number, Client_Number and I want top 3 Client_Numbers basied on Unit which I Enter in One fixed Cell.

Kindly refer attached file for more details.

Thanks in Advance!!!

Please help.

 

[Sathish KV]

Hi,

Use the below formula

Note: Its an array formula. You need to hit Ctrl+Shift+Enter after entering the formula

=INDEX($B$2:$B$51, SMALL(IF($F$2=$A$2:$A$51, ROW($A$2:$A$51)-ROW($A$2)+1), ROW(1:1)))

 

[Pravinkarne.31]

Great, Thank you so much Sathish for your quick help!!!

 

[GFC]

a slight variation using tables
Also an array formula (Ctrl+Shift+Enter)

=IFERROR(INDEX(Table1[Client_Number], SMALL(IF(Table1[Unit_Number]=$F$2, ROW(Table1[Unit_Number])-ROW(Table1[[#Headers],[Unit_Number]])), ROWS($H$1:H1))),"")