Multicell Array Formula and List Question

[Guest]

I have data in the range A1:B5 as follows:

a 5
b 3
c 6
d 5
e 7

In the range E1:E5, I have added the following multi-cell array formula that
returns the largest values in B1:B5 sorted highest to lowest:

{=(LARGE($B$1:$B$5,{1;2;3;4;5})}

I want to add a similar formula in D15 that returns the corresponding "a,
b, c, d, and e" values that go with the largest to smallest number sort. In
other words, when complete the range D1:E5 should look like this:

e 7
c 6
a 5
d 5
b 3

I've tried multiple combinations of INDEX, MATCH, AND OFFSET but I keep
getting the letter "a" returned twice for the number 5 and/or error messages.

Thanks for your help.

John

 

[Max]

One play via a non-arrray approach ..

Put in H1: =B1-ROW()/10^10
Copy H1 down to H5

(H1:H5 will function as the arbitrary tiebreaker values)

Now just put in say, C1:

=INDEX(A:A,MATCH(LARGE($H:$H,ROWS($A$1:A1)),$H:$H,0))

Copy C1 across to D1, fill down to D5

C15 will return the desired:

e 7
c 6
a 5
d 5
b 3

 

[Bob Phillips]

Hi Max,

Do that without the helper column, and that will be a little beaut

Bob

 

[Guest]

Max,

Thanks for your help. That particular arbitrary tie-breaker formula is
something I've not seen before. It's just what I was looking for.

Thanks again.

John Mansfield

 

[Alan Beban]

I have data in the range A1:B5 as follows:

a 5
b 3
c 6
d 5
e 7

In the range E1:E5, I have added the following multi-cell array formula that
returns the largest values in B1:B5 sorted highest to lowest:

{=(LARGE($B$1:$B$5,{1;2;3;4;5})}

I want to add a similar formula in D15 that returns the corresponding "a,
b, c, d, and e" values that go with the largest to smallest number sort. In
other words, when complete the range D1:E5 should look like this:

e 7
c 6
a 5
d 5
b 3

I've tried multiple combinations of INDEX, MATCH, AND OFFSET but I keep
getting the letter "a" returned twice for the number 5 and/or error messages.

Thanks for your help.

John

Copy your data to D1:E5; click on Data|Sort; Select "No header row";
Select Column E and Descending; click "OK"

Alan Beban

 

[Max]

One way which seems to work, Bob <g>
(I'm sure there are others up your sleeve)

In C1, array-entered:

=INDEX(A$1:A$5,MATCH(LARGE($B$1:$B$5-ROW()/10^10,ROWS($A$1:A1)),$B$1:$B$5-RO
W()/10^10,0))

C1 copied to D1, filled down to D5, as before

 

[Max]

You're welcome, John !
Glad it was helpful

Do hang around awhile for insights from others ..

 

[Bob Phillips]

Max,

This didn't work for me as this part of the formula $B$1:$B$5-ROW()/10^10
returned

{4.9999999997;2.9999999997;5.9999999997;4.9999999997;6.9999999997}

for the first 5 value(reduced to 4.9999999997) , which returns a correctly,
but it then returned

{4.9999999996;2.9999999996;5.9999999996;4.9999999996;6.9999999996}

for the next 5 (reduced to 4.9999999996) value. As there are two
4.9999999996 in there it will match the first, which is a again.

I did make it work with a slight alteration

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

But nice one all the same

Regards

Bob

 

[Guest]

Bob,

I wanted to thank you for your help too as well as Max's help. All of the
examples have be very helpfull.

John Mansfield

 

[Bob Phillips]

John,

Thanks, but it was Max's solution. I just pushed him to take it one step
further

Bob

Bob,

I wanted to thank you for your help too as well as Max's help. All of the
examples have be very helpfull.

John Mansfield

 

[Max]

Thanks for the explanations and correction, Bob !

But nice one all the same

And .. for this, too <g>

 

[Max]

.. I just pushed him to take it one step further
urrgh ... in attempting to do so, I fell off the cliff !
But thank god there was a lifeline by a certain Bob P.
which held on and hauled me back <bg>