Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Dec 2013
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Post Returning value that fits all criteria

    Dear All,

    I have this table as shown below.

    table2.JPG

    I have a few criteria for the decision.
    I would like to return the Stroller that gives me the lowest estimated price, greatest harness points, lightest weight, most numbers of color choices and the most number of features.

    In this case, it should return me "Bugaboo Bee".

    Can anyone please help me?? Thank you so much! Appreciate your help!!!

  2. #2
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    Welcome to the lounge.

    What would you do if there is a tie?

    If your data is in columns A through H, this ARRAY formula (CTRL+Shift+Enter) should find the one you want:

    =INDEX(C2:C5,SUMPRODUCT((D2:D5=MIN(D2:D5))*(E2:E5= MAX(E2:E5))*(F2:F5=MIN(F2:F5))*(G2:G5=MAX(G2:G5))* (H2:H5=MAX(H2:H5))*ROW(1:4)))
    Last edited by RetiredGeek; 2013-12-03 at 05:43. Reason: Added NOPARSE tags to formula.

  3. #3
    New Lounger
    Join Date
    Dec 2013
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi kweaver!

    Thanks for you help! It works perfectly!
    However, after much consideration, I decided to add in this additional form called Input sheet.

    form.JPG

    The brand, type and price range are defined as list as shown in the image below.
    list.JPG

    I can choose a specific brand and a specific type and specific price range or choose ALL and then come up with a decision based on the previous criteria in the my previous post. Below is the table.

    table2.JPG

    If there's a tie, will it be able to display the cheapest one among the results?

    Thank you so much for your help!! Sorry, this is my first time doing such excel! Really appreciate your help!
    Attached Images Attached Images

  4. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    La Jolla, CA
    Posts
    1,470
    Thanks
    30
    Thanked 62 Times in 58 Posts
    I'm not fully sure what you now want to do. Could you be a bit more specific and also attach a sample spreadsheet rather than images?

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    In addition to attaching a copy, you will need a range with the price in it, not just a "text range". Most likely a separate column for min and max price to base the calculations on it.

    Steve

Posting Permissions

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