Return multiple same values

[Matt]

Say I have the following data:
items A through F have corresponding values
A 1
B 5
C 0
D 1
E 4
F 5

Is there a way to have excel output the items kind of like this:
large(range,1) --> B
large(range,2) --> F
large(range,3) --> E
large(range,4) --> A
large(range,5) --> D
large(range,6) --> C

as opposed to:
large(range,1) --> B
large(range,2) --> B
large(range,3) --> E
large(range,4) --> A
large(range,5) --> A
large(range,6) --> C

Thanks!

 

[T. Valko]

Try one of these:

Items = A1:A6
Numbers = B1:B6

Entered in C1 as an array using the key combination of CTRL,SHIFT,ENTER (not
just enter):

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

Copy down to C6

Another way is to use column of helper cells and rank the numbers using a
tie breaker:

Enter this in C1 and copy down to C6:

=RANK(B1,B$1:B$6)+COUNTIF(B$1:B1,B1)-1

Then, in D1 copied down to D6:

=INDEX(A$1:A$6,MATCH(SMALL(C$1:C$6,ROWS($1:1)),C$1:C$6,0))

Biff

 

[Matt]

Awesome! Thank you. How in the world did you come up with this
formula? experience??

 

[T. Valko]

You're welcome. Thanks for the feedback!

experience??

Yes, trial and error

Biff

 

[Matt]

What is the purpose of the " /10^10 " part of the formula?

 

[T. Valko]

It's used to break any ties.

10^10 = 10 to the 10th power = 10,000,000,000

Assume you have:

B1 = 5
B2 = 5

As you've discovered, ties are a problem. So, we need to coerce each value
to be unique. Within the array formula this is what's happening:

B1-ROW(1)/10^10 = 5 - (1/10,000,000,000) = 4.9999999999
B2-ROW(2)/10^10 = 5 - (2/10,000,000,000) = 4.9999999998

So, now we have unique values. MATCH(LARGE(...........,1) now will find the
first 5 and MATCH(LARGE(...........,2) will find the second 5.

Biff