Here's a non-array formulas play to extract a full descending sort by amt
into a new sheet (caters for the likely possibility of ties in the amounts)

A sample construct is available at:
http://cjoint.com/?cgiVSpaRSW
ExtractDescendingSortedList_via135_wks.xls

Assume source table in sheet: X, cols A & B, data from row2 down

In a new sheet: Ranking,
With the same col headers in A1:B1, viz.: Name, Amt

Put in A2:
=IF(ISERROR(LARGE($C:$C,ROW(A1))),"",
INDEX(X!A:A,MATCH(LARGE($C:$C,ROW(A1)),$C:$C,0)))
Copy A2 to B2

Put in C2: =IF(X!B2="","",X!B2-ROW()/10^10)
(Leave C1 empty)

[Col C is the arbitrary tiebreaker col]

Select A2:C2, fill down to cover
the max expected extent of the source data in X

The above returns a full descending sort of the source table in X by the Amt
col. Names with tied amts, if any, will appear in the same relative order
that they appear in the list in X. Just pick off the "top 20" as required
from the list (In the event of ties, or even multiple ties, you may need to
pick more than just the top 20 names for the top 20 highest amts !)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"via135" wrote in
message ...

hi all!

i am having names in a1:a1000 and amounts in b1:b1000.
how can i get the top20 highest amounts and its corresponding names
in c1:d20?

help pl?

-via135


--
via135
------------------------------------------------------------------------
via135's Profile:
http://www.excelforum.com/member.php...o&userid=26725
View this thread: http://www.excelforum.com/showthread...hreadid=508829