Finding the 6 lowest + referance to text

[drag-driver]

This is what i need to do ---- I have a row which i haved search to find the lowest numbers in. Now that i have found that number i dont want it printed in this cell i want the name of that row printed in the cell, ie the product name. i.e. what i have is a row of products with a row of numbers underneath i want the 6 products with the lowest number to be displayed on a sepearate colume, ie the most important ones.

 

[IML]

On 2002-03-20 07:19, drag-driver wrote:
This is what i need to do ---- I have a row which i haved search to find the lowest numbers in. Now that i have found that number i dont want it printed in this cell i want the name of that row printed in the cell, ie the product name. i.e. what i have is a row of products with a row of numbers underneath i want the 6 products with the lowest number to be displayed on a sepearate colume, ie the most important ones.

Lets say your price in in A1:A10 and product in B1:B10. I would add a column you could hide in and use the formula
=RANK(A1,$A$1:$A$10,1)+COUNTIF($A$1:A1,A1)-1
and copy it down.

Then below you could use index and match to capture your six lowest. For example, use
=INDEX($A$1:$C$10,MATCH(ROW(1:1),$C$1:$C$10,0),2)

and copy it 5 lines below.

I originally thought just a vlookup such as
=VLOOKUP(SMALL(A1:A10,1),$A$1:$B$10,2,0)

may work, but with this you would miss different products that have the same price.

good luck

 

[IML]

I just noticed you've already posted this identical question a bit below and have already had this type solution suggested. Just wasted my time and yours...


This message was edited by IML on 2002-03-20 07:42

 

[drag-driver]

the promblem is my data is in rows not coloums so that idea wont work

 

[drag-driver]

This will not work as my dat is in rows not colums any other sugestions

 

[IML]

On 2002-03-20 07:43, drag-driver wrote:
This will not work as my dat is in rows not colums any other sugestions

same formula for rows.
assumes price in A1:J1
assumes product in A2:J2
formula for a3 (copy through j3)
=RANK(A1,$A$1:$J$1,1)+COUNTIF($A$1:A1,A1)-1

assuming you copy your formulas down still.

The lowest priced would be
=INDEX($A$1:$J$3,2,MATCH(ROW(1:1),$A$3:$J$3,0))

copy down five more rows.
This message was edited by IML on 2002-03-20 08:03