On Jun 3, 8:52*am, "Steve Dunn" wrote:
If you need to allow for two products of the same weight and type, try this:

=IFERROR(INDEX(Data!$A$1:$A$10,MATCH(LARGE(INDEX(
(Data!$B$2:$B$10=$A2)*Data!$C$2:$C$10+
ROW(Data!$C$2:$C$10),),B$1),INDEX(Data!$C$2:$C$10+
ROW(Data!$C$2:$C$10),),0)+1),"")

"Steve Dunn" wrote in message

...



Hi, this does not have to be array-entered, if that's what you mean.

=IFERROR(INDEX(Data!$A$1:$A$5,MATCH(LARGE(INDEX(
(Data!$B$2:$B$5=$A2)*Data!$C$2:$C$5,),B$1),Data!$C $1:$C$5,0)),"")

BTW, this (and your original) would fail if you had two products of the
same weight and type.

HTH
Steve D.

"exceluser" wrote in message
...
Can someone show me how to accomplish the following by using the LARGE
function "WITHOUT" using an array ?

I'm trying to rank each product (most, second most, etc.) by weight.

* Data worksheet

* * A B C
* 1 Product Type Pounds
* 2 Orange Fruit 600
* 3 Tomato Vegetable 500
* 4 Apple Fruit 700
* 5 Potato Vegetable 1,000

Using the LARGE function, the goal is to get the following result on
another worksheet:

* Order worksheet

* * A B C
* 1 Product 1 2
* 2 Fruit Apple Orange
* 3 Vegetable Potato Tomato

On the Order worksheet, the following formula is the one I'm using
with an array:

* {=IFERROR(INDEX(Data!$A$1:$C$5,MATCH(LARGE(IF(Data !$B$1:$B$5=
$A2,Data!$C$1:$C$5,""),B$1),Data!$C$1:$C$5,0),1)," ")}

Does anyone know how to get the same result without using an array -
even if it uses another function ?- Hide quoted text -

- Show quoted text -

Steve,

You are so THE MAN !

It makes me almost forget that whole BP thing ... for a few hours
anyway.

The reason why I wanted to avoid using an array was because the
original formula I was using with an array (in over 15,000 cells) was
using %100 of the CPU for approximately

90 minutes on an Intel 3.4 GHz processor with 2 GB of RAM.

To make matters worse, new data is imported daily and making any
change effecting those cells would cause a 90 minute recalculation.

This new formula has reduced the calculation from 90 minutes to
under 30 seconds.

Could you explain why ... ?

1) The second INDEX function skips the first row for 'Data!$B$2:$B$5
and 'Data!$C$2:$C$5 rather than using the whole column

2) The second INDEX function multiplies those two ranges

Thanks again for that super fast formula.



Exceluser