CODEWITHSUNDEEP
excelexcel-formula
BruceWayne
BruceWayne

Reputation: 23285

Index/Match with Min (or equivalent)

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!)

Upvotes: 1

Views: 992

Answers (2)

Scott Craner
Scott Craner

Reputation: 152660

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:

enter image description here

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

Upvotes: 4

gtwebb
gtwebb

Reputation: 3011

One answer using index/match would be

=INDEX($A$2:$A$15,MATCH(MIN(IF($C$2:$C$15="x",$B$2:$B$15,9999)),IF($C$2:$C$15="x",$B$2:$B$15,""),0))

columns are wrong since I used my own data but the concept works.

Upvotes: 3

Related Questions