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