top 5 customers using formula

[saurabhlotankar]

Hello,

i have huge data of income of employees.
In column "A" i have names of employees,
in column "B" i have income of last month and
in column "c" i have income of current month.
In column "D" i have calculated difference between two month's income(C1-B1).
Now based on values in column D i want to find names of top 5 gainers and top decliners (i.e. i want names of top 5 people who are having highest value in column "D")
I don't want to use pivot and sort, I want these names by using any formula.

Thanks in advance



thanks its working perfectly.

But now i have added one more column in the beginning as country names. Now i want to make a dashboard so tht i will create a drop down with country names and country i will select top gainers should come with ur given formula.

Please assist

[Bernie Deitrick]

Top 5:
=INDEX(A:A,MATCH(LARGE(D:D,ROW(A1)),D:D,FALSE))
Bottom 5:
=INDEX(A:A,MATCH(SMALL(D:D,ROW(A1)),D:D,FALSE))

Each dragged down for five rows.

If you can have exact ties in column D, then you need to use array formulas (entered using Ctrl-Shift-Enter) like this to break the ties:

=INDEX(A:A,MATCH(SMALL($D$1:$D$2000+ROW($D$1:$D$2000)/1000000,ROW(A1)),$D$1:$D$2000+ROW($D$1:$D$2000)/1000000,FALSE))