Thread: Returning value that fits all criteria

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

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

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

4. 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. 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
•