Index/Match with Min (or equivalent)

Question

I'm trying to return a value based on the MIN() in a range. Here's a screenshot, but here's the data:

Category        Types       pts      Policeman       Hero
Jurisdiction    City        -10         x   
                State         0     
                Country      20                       x
Vehicle         Car          -2         x   
                Bus          20     
                Unicycle     20                       x
Sidekick        No Sidekick -20         x   
                One           5     
                Multiple     10                       x
Powers          None        -30                       x
                Super        2          x
                Kenny        30

My goal is for each "person" (Policeman and Hero), look in their column, and return the value from column B, where there's an X by the "minimum" value.

In other words, for "Policeman", the min. value with an 'X' is "No Sidekick", so I want my Index/Match to return that. For "Hero", the minimum value with an "X" is "None" (under "Powers").

I've tried:
=Index(B2:B13,Match(Index(Min(C2:C13),Match("x",D2:D13,0))&"x",C2:C13&D2:D13,0)) but it doesn't work.

I've also tried
=Index(B2:B13,Min(If(D2:D13="x"),Row(D2:D13)-1))

I can see the formula in my mind, just can't figure out where I'm going wrong. Thanks for any ideas/help.

(I don't have to use Index/Match, so if there's another way (Sumproduct() perhaps?), I'm open to it!)

Batman, who taught me index/match, is now asking about it??? Downvoted and flagged. — findwindow
Why do I need to recreate your data to test my formula? :P. Get your data where I can copy/paste and I am sure someone will get you the formula. — Scott Craner

Scott Craner Scott Craner · Accepted Answer · 2016-05-05T16:06:44

The following will do it(Untested):

=INDEX($B$2:$B$13,MATCH(1,INDEX((D$2:D$13="x")*($C$2:$C$13=AGGREGATE(15,6,$C$2:$C$13/(D$2:D$13="x"),1)),),0))

Only because it is Bruce.

I merged the column D and E and so forth, and yes I know I did the cardinal sin in merging but hey, I figure you can do what you want. I am just showing a concept:

In D14:

=AGGREGATE(15,6,$C$2:$C$13/(D$2:D$13="x"),ROW(1:1))

Then in E14:

=INDEX($B$2:$B$13,AGGREGATE(15,6,(ROW($C$2:$C$13)-1)/((D$2:D$13="x")*($C$2:$C$13=D14)),COUNTIF(D$14:D14,D14)))

Then copy down and over

Index/Match with Min (or equivalent)

2 Answers