Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Finding the 6 lowest + referance to text

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Posts
    91
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Denver, CO
    Posts
    1,743
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Denver, CO
    Posts
    1,743
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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. #4
    Board Regular
    Join Date
    Feb 2002
    Posts
    91
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Posts
    91
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Denver, CO
    Posts
    1,743
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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