Finding minimum value in index(match) array [EXCEL

问题:

This is my simple table

A                           B               C
tasmania                   hobart            21
queensland                 brisbane          22
new south wales            sydney            23
northern territory         darwin            24
south australia            adelaide          25
western australia          perth             26
tasmania                   hobart            17
queensland                 brisbane          18
new south wales            sydney            19
northern territory        darwin             11
south australia           adelaide           12
western australia         perth              13

index match array formula:

=INDEX(A2:C9,MATCH(1,(H4=$A:$A)*(I4=$B:$B),0),3)

Basically A and B are my lookup criteria while C is the value I want to get. I want C to be the minimum value among the matched C value.

Ex. If I have tasmania and hobart as my criteria, I would want to get 17 because it is the minimum value and not 21.

I tried nesting MIN inside the index match array (H4=$A:$A)*(I4=$B:$B)*(MIN($C:$C)) but it only results in errors

回答1:

This is rather a MIN(IF... than a INDEX. Before SUMIF or COUNTIF was implemented in Excel even SUM(IF... or COUNT(IF... had to be used this way.

Since there is not a MINIFS until now, for this we must further use:

{=MIN(IF($A$1:$A$1000=H4,IF($B$1:$B$1000=I4,$C$1:$C$1000,NA())))}

This is an array formula. Input it into the cell without the curly brackets and press [Ctrl]+[Shift]+[Enter] to confirm. The curly brackets should then appear automatically.

回答2:

With the AGGREGATE function as a standard formula,

=AGGREGATE(15, 6, C2:INDEX(C:C, MATCH(1E+99,C:C ))/((A2:INDEX(A:A, MATCH(1E+99,C:C ))=F2)*(B2:INDEX(B:B, MATCH(1E+99,C:C ))=G2)), 1)

    

As an older style standard formula, your original would look like,

=MIN(INDEX(C2:C13+((H4<>A2:A13)+(I4<>B2:B13))*1E+99, , ))