Try this.
=INDEX(A:A,MATCH(LARGE(K:K,ROW(A1)),K:K))
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"computers hate me" wrote in
message ...

because right now there are only 700 but we will add more eventually and
it
could add up to about 2000. So i wanted to set the formula so that if we
add
more it will still work
"Lars-Åke Aspelin" wrote:

Also, this formula relies on that the range with alamrs is less than
1000 rows.
You said that you have 700 alarms, so why do you enter A$2:A$2000 ??


On Tue, 05 Aug 2008 14:20:01 GMT, Lars-Åke Aspelin
wrote:

Did you enter the formula as an array formula?
If you don't do that, you will get NUM# error.

Lars-Åke

On Tue, 5 Aug 2008 07:03:02 -0700, computers hate me
wrote:

ok it gives me a NUM# Error
I dont understand the formula so i dont know how to subtitute my values
into
it
this is the formula i used

=INDEX('Bf 4 Alarms'!A$2:A$2000,MATCH(INDEX('Bf 4
Alarms'!D$2:D$2000+ROW(A$2:A$2000)/1000,
MATCH(LARGE('Bf 4 Alarms'!D$2:D$2000+ROW(A$2:A$2000)/1000,ROW()),'Bf 4
Alarms'!D$2:D$2000+ROW(A$2:A$2000)/1000,0)),'Bf 4
Alarms'!D$2:D$2000+ROW(A$2:A$2000)/1000,0))





"Lars-Åke Aspelin" wrote:

On Tue, 5 Aug 2008 05:36:01 -0700, Bertha needs help
wrote:

I have a list of 700 alarms in column A then In column B i have a
formula
that counts how many times each alarm went off.

In another sheet i want to make a chart that shows the top 10 alarms
that
went off that day.

What formula could i use to find the top 10 values?


In cell A1 of the other sheet, put

=INDEX(Sheetq!A$1:A$700,MATCH(INDEX(Sheet1!B$1:B$7 00+ROW(A$1:A$700)/1000,
MATCH(LARGE(Sheet1!B$1:B$700+ROW(A$1:A$700)/1000,ROW()),
Sheet1!B$1:B$700+ROW(A$1:A$700)/1000,0)),Sheet1!B$1:B$700+ROW(A$1:A$700)/1000,0))

Note this formula is an array formula that has to be entered by
CTRL+SHIFT+ENTER rather than just ENTER.


In cell B1 of the other sheet, put

=LARGE(Sheet1!B$1:B$700,ROW())

Copy these formulas from A1:B1 down to A10:B10 to get the table of
the
names of the 10 most frequent alarms and their respective
frequencies.

Hope this helps / Lars-Åke