Thanks:  0
Likes:  0

# Thread: Finding the 6 lowest + referance to text

1. 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.

2. 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

3. 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 ]

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

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

6. 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 ]

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•