This is more complicated than it appears on the surface (especially when you
need to account for ties).

A top n list can be more than n. Based on the sample you posted the top 10
would include *every* entry so your top 10 list turns into a list of 20.

Try this....

Enter this formula in J3. This will return the count of items that fall
within the top 10.

=COUNTIF(G3:G72,"="&LARGE(G3:G72,10))

Enter this formula in K3. This will return the top 10 numbers in ascending
order.

=IF(ROWS(K$3:K3)<=J$3,LARGE(G$3:G$72,ROWS(K$3:K3)) ,"")

Enter this array formula** in L3. This will return the plant names.

=IF(K3="","",INDEX(A$3:A$72,SMALL(IF(G$3:G$72=K3,R OW(G$3:G$72)),COUNTIF(K$3:K3,K3))-ROW(G$3)+1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Select both K3 and L3 and copy down until you get blanks meaning all the
data has been extracted.

--
Biff
Microsoft Excel MVP


"Jeff Gross" wrote in message
...
I have two columns of data A3:A72 and G3:G72. The first column has plant
names and the second column has a number of accidents. Below is a sample
of
the data:

Plant 1-01 1
Plant 1-02 0
Plant 1-03 0
Plant 1-04 0
Plant 1-05 0
Plant 1-06 2
Plant 1-07 0
Plant 1-08 1
Plant 1-09 0
Plant 1-10 2
Plant 2-01 0
Plant 2-02 0
Plant 2-03 0
Plant 2-04 2
Plant 2-05 0
Plant 2-06 0
Plant 2-07 0
Plant 2-08 1
Plant 2-09 0
Plant 2-10 1

I would like to create a Top 10 list of the highest values in G3:G72 and
then return both the Plant Name and its associated number of accidents in
descending order on another tab.

Several of the plants will have the same number of accidents (e.g. 0 or 1
or
2) in which case I would need all of those plants.

I tried using the following formulas on the different tab but it doesn't
appear to work. The first formula is to give the plant name and is
started
in row A1 of the tab called "Sheet1" and the second is to give the number
of
accidents:

=INDEX('Current Year Data'!A$3:A$72,MATCH(LARGE('Current Year
Data'!G$3:G$72,ROW(A1)),'Current Year Data'!G$3:G$72,0))

=INDEX('Current Year Data'!G$3:G$72,MATCH(LARGE('Current Year
Data'!$G$3:$G$72,ROW(B1)),'Current Year Data'!G$3:G$72,0))

Any help wold be greatly appreciated.

Jeff