Array Formula

[mc24]

Hi,

I have a set of data like below:
Key Value
A 4
B 2
C 2
D 1
E 5
F 5


I'm trying to find the keys with the largest 3 values-- however, since there are multiple matches (for e.g. E/F with the same value 5), its tricky to get F to be displayed.

I thought the following would work but it doesnt:

* I created a column showing the 3 LARGEST values in order (Col B): =LARGE(A:A,{1,2,3} with teh following result along with a column (Col C) that shows if they are duplicates: =COUNTIF($A$1:A1,A1)
5 1
5 2
4 1
* Now I need to get the rows associated iwth that result. I thought the following formula wouldve worked. =SMALL(IF(B1:B3=B1,ROW(B1:B3),9999),C1)

However, it does gives me a #VALUE.

Any ideas?

[NBVC]

How about?

=INDEX($A$1:$A$6,LARGE(IF($B$1:$B$6<>"",ROW($B$1:$B$6)),ROW(A1)))

confirmed with CTRL+SHIFT+ENTER not just ENTER... and then copied down.

[mc24]

Does not work.

Also logically:
IF($D$41:$D$46<>"",ROW($D$41:$D$46)) will always match up to true and return the row numbers.

LARGE(Row numbers,x) will just return the top x row numbers (rather than the row numbers with the top x values).

[NBVC]

sorry...wasn't thinking properly....

I know there must be a better way...I have a brain freeze at the moment... but this should work.

=INDEX($A$1:$A$6, LARGE(IF($B$1:$B$6>=LARGE($B$1:$B$6,3) ,ROW($B$1:$B$6)),ROW(A1)))

CSE confirmed and copied down

[mc24]

Logically that works... Just like the formula I had.

It just outputs a #VALUE and I cant understand why.

[daddylonglegs]

If data was like this

A 4
B 4
C 4
D 1
E 5
F 5

Would you want to return all "keys" except D?

[mc24]

Well, not really. Just the top 3. So: E, F, A is the output I'm looking for.

Basically the exact same functionality as if you did AutoFilter and went top 3.
But as I'm sorting on multiple columns in the report, cant do autofilter (and goes without saying is that I cant use VBA).

[NBVC]

If data was like this

A 4
B 4
C 4
D 1
E 5
F 5

Would you want to return all "keys" except D?

I was thinking along the same lines....but for some reason I can't come up with the right formulation....it's Friday.......

[NBVC]

Logically that works... Just like the formula I had.

It just outputs a #VALUE and I cant understand why.

I'm getting numbers....

Are you sure the formula, if changed, is syntactically correct?

[daddylonglegs]

Well, not really. Just the top 3. So: E, F, A is the output I'm looking for.

OK, here's my suggestion, see attached

[NBVC]

here's yet another using helper column that ranks the numbers first, then indexes against those rankings....

[mc24]

Thanks guys.

One problem I kept having was that in my data-set there were a few blank rows which made the array output #N/A. Had to put another IF condition to check for that. Ugly.