在索引(匹配)数组中找到最小值[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.

基本上A和B是我的查找条件，而C是我想要的值。我希望C是匹配C值中的最小值。

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.

如果我以塔斯马尼亚和霍巴特为标准，我希望得到17，因为它是最小值，而不是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

我在索引匹配数组中尝试了嵌套(H4=$A:$A)*(I4=$B:$B)*(MIN($C:$C))，但它只会导致错误。

2 个解决方案

#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.

(如果…)比一个索引。在SUMIF或COUNTIF在Excel中实现偶数SUM之前(如果…或数(如果……必须用这种方式。

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.

这是一个数组公式。在没有花括号的情况下输入，按[Ctrl]+[Shift]+[Enter]来确认。然后，括号将自动出现。

#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, , ))

#1