Using a single array formula** :

=INDEX(B$2:B$6,MATCH(LARGE(A$2:A$6-ROW(A$2:A$6)/10^10,ROWS($1:1)),A$2:A$6-ROW(A$2:A$6)/10^10,0))

Copied down

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

--
Biff
Microsoft Excel MVP


"Mike H" wrote in message
...
Thanks for the feedback. I still think it can be done in a single formula
though.

Mike

"Cavy" wrote:

Brilliant!

It works perfect, and it is a clever way to solve the problem.

Thanks!!

"Mike H" wrote:

Hi,

Change column D formula to this

=(RANK(A2,$A$2:$A$6))+ROW()/10^10

and you can do away with column C

Mike


"Mike H" wrote:

Hmmm,

There has to be an easier way but this does it with 3 columns and
your data
as shown below in columns A and B. I've assumed your data start in
row 2 and
that row 1 are labels.

Put his in C2 and drag down
=RANK(A2,$A$2:$A$6)

Put this in D2 and drag down
=IF(C2="","",C2+ROW()/10^10)

Put this in E2 and drag down and you should get what you requi-
=IF(ROWS($1:1)COUNT($D$2:$D$6),"",INDEX($B$2:$B$6 ,MATCH(SMALL($D$2:$D$6,ROWS($1:1)),$D$2:$D$6,0)))

Now how do I combine that into a single formula?

Mike

"Cavy" wrote:

Hi,

I have a list of Customers in Column B and their Orders in Column
A.
I am trying to find the top 10 Customers on Column C by using the
following
formula:
=VLOOKUP(LARGE(A:A,ROW()),A:B,2,0)
The problem I am having is that if two customers have the same
number of
orders, and they are in the Top 10, the first customer listed
appears twice.
For example in a list of 5 customers I get the following:
Units Customer Top 5
89 C1 C1
59 C2 C1
89 C3 C2
19 C4 C5
51 C5 C4

I would have wanted to see C3 in the Top 5 but instead C1 appears
twice.

Thanks for your help!