CODEWITHSUNDEEP
arraysexcelindexingmatchlookup
mindblues
mindblues

Reputation: 87

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

Upvotes: 7

Views: 17627

Answers (2)

user4039065
user4039065

Reputation:

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)

    aggregate_min

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

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

Upvotes: 3

Axel Richter
Axel Richter

Reputation: 61862

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.

Upvotes: 4

Related Questions