array formula

[Tim]

Hi All,

Col# A B D
1 5 Prod1 Prod2
2 10 Prod2 Prod4
3 4 Prod3 Prod1
4 10 Prod4 Prod3
5 3 Prod5 Prod5
6 3 Prod6 Prod6

There are 2 Columns on a worksheet-Column A and Column B. In Column A are
values and in Column B are product names. Need a formula to get the names
from Column B and to put them in Column D in the next order: In CellD1 is the
name of the first product with the largest value in column A(in the example
this is Prod2), in Cell D2 is the name of the second largest value in column
A(in the example this is Prod4) and so on.
I tried this formula

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

But it doesn’t work because often in column A there are 2 or 3 cells with
the same largest value (in the example above cells A2 and A4) and in this
case the formula gives me wrong results.
I think this task can be done with array formula but can’t manage to create
it by myself.


Tim

 

[Mike]

No need for a formula, use sort instead.

In D1 put =b1 and drag down. Select Cols A * B and sort the data descending

 

[Tim]

Thanks Mike but i need to do it by formula

Tim

No need for a formula, use sort instead.

In D1 put =b1 and drag down. Select Cols A * B and sort the data descending

 

[Bob Phillips]

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

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Tim]

Perfect.
Thank you Bob!!!

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

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

--
HTH

Bob Phillips

(there's no email, no snail mail, but somewhere should be gmail in my addy)