Finding minimum value in index(match) array [EXCEL]

Question

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

Solution

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.

Comments

[Vylix]

Since we now have MINIFS, can you update the answer using MINIFS, please?

[Axel Richter]

@Vylix: Who has MINIFS? Only the ones who will have the newest Excel. And for those the MINIFS function is pretty good documented, isn't it?

[Vylix]

it seems I misunderstand the question, and thought this is very complicated formula. It's just MIN with 2 condition. Thanks!