INDEX & MATCH & LARGE question


Posted by Altug Bayram on November 19, 2001 1:17 PM

Hi,

I have 2 columns of data, say

AA 5
AB 10
AC 2
AD 10
AE 8

I would like to automatically rank order say top 3
of this data with a formula and without using any
macros. It doesn't matter which one of "identical"
entries come first.

So the answer could look like:
AB 10
AD 10
AE 8
AA 5
AC 2

Through some search, I found out that the
following almost works:

=INDEX($A$1:$A$5,MATCH((LARGE(B$1:B$5,1)),B$1:B$5,0))
paired with
LARGE(B$1:B$5,1)

I copy this formula to all the subsequent rows while
changing 1 to 2 (to 3, 4 , 5 etc...)

This almost works EXCEPT when there are identical
entries, it repeats the first value from the array
(since this the first matching one).

So it gives me the following wrong ranking:
AB 10
AB 10
AE 8
AA 5
AC 2

2 Questions:

1) Does anyone know how to do this using either
the above approach or some other brief formula

2) When information functions are used, isn't there
a way in excel to actually spit out the address of the
answer. Eg. if you are looking for the 3rd
largest value in the above array (original one at the top),
you would write:
LARGE(B$1:B$5,3) which correctly finds the value of 8.
What I want it to tell me is actually the reference
address of the ANSWER I am looking for, which is B5.
Is there any such function in excel ?

Thanks everyone.

Posted by Altug Bayram on November 19, 2001 1:21 PM

One thing may not have clear from the above statement.
When I copy, my formulas look like


=INDEX($A$1:$A$5,MATCH((LARGE(B$1:B$5,1)),B$1:B$5,0))
=LARGE(B$1:B$5,1)

=INDEX($A$1:$A$5,MATCH((LARGE(B$1:B$5,2)),B$1:B$5,0))
=LARGE(B$1:B$5,2)

=INDEX($A$1:$A$5,MATCH((LARGE(B$1:B$5,3)),B$1:B$5,0))
=LARGE(B$1:B$5,3)

=INDEX($A$1:$A$5,MATCH((LARGE(B$1:B$5,4)),B$1:B$5,0))
=LARGE(B$1:B$5,4)

=INDEX($A$1:$A$5,MATCH((LARGE(B$1:B$5,5)),B$1:B$5,0))
=LARGE(B$1:B$5,5)

each pair goes side by side into two columns to make
up a total of 5 rows & 2 columns.

Posted by Aladin Akyurek on November 19, 2001 1:33 PM

Altug --

I'll assume A2:B6 to house your sample data

{"AA",5;"AB",10;"AC",2;"AD",10;"AE",8}

In C2 enter: =RANK(B2,B$2:B$6)+COUNTIF(B$2:B2,B2)-1

In E2 enter: =IF(ROW()-1<=3,INDEX(A$2:A$6,MATCH(ROW()-1,$C$2:$C$6,0)),"")

In F2 enter: =IF(LEN(E2),INDEX(B$2:B$6,MATCH(ROW()-1,$C$2:$C$6,0)),"")

Select C2:F2 and copy down as far as needed.

You might adjust 3 which stands for top 3.

Aladin

That's how match is supposed to behave, whence the above system of formulas.



Posted by Altug Bayram on November 20, 2001 6:32 AM

Thanks Aladin, this again was a great help.

Assuming that you are Turkish, Tesekkurler ve Iyi Gunler.
Altug