如何在excel单元格中返回多个条件的数据

实际上，我们经常需要从一列返回数据，这对应于满足一个或多个条件的另一列中数据的最大值。

如下图1所示，需要返回指定序列号(a列)的最新版本(b列)对应的日期(c列)。

图1

解决方案1:

在单元格F2中输入数组公式：

=INDEX(C2:C10，MATCH(MAX)(IF(A2:A10=F1，B2:B10))、IF(a 2: a10=F1，B2:B10)，0))

请注意，有两个IF子句，不仅在生成参数lookup_value的值的构造中，而且在生成参数lookup_array的值的构造中。重要的一点不容忽视，即如果采用以下简单方法：

=INDEX(C2:C10，MATCH(MAX(IF(A2:A10=F1，B2:B10))，B2:B10，0))

虽然这个公式构造仍然可以返回正确的值，但不能保证在所有情况下都是正确的。原因是条件对应的最大值不在B2:B10，而是针对不同的序列号。此外，如果这发生在我们想要返回的值之前的行中，MATCH函数显然不会返回我们想要的值。

以上公式可以解析验证：

=INDEX(C2:C10，MATCH(MAX)(IF({ FALSE；假；假；假；假；真；真；真；真}，{ 4；2;5;3;1;3;4;1;2}))，B2:B10，0))

转换为：

=INDEX(C2:C10，MATCH(MAX)({ FALSE；假；假；假；假；3;4;1;2})，B2:B10，0))

转换为：

=INDEX(C2:C10，MATCH(4，B2:B10，0))

转换为：

=INDEX(C2:C10，MATCH(4，{ 4；2;5;3;1;3;4;1;2},0))

显然，数组中满足条件的第一个值不是我们要查找的值的位置：

=INDEX(C2:C10，1)

获取：

2013-2-21

这不是与我们的条件相对应的值。

回到正确的公式：

=INDEX(C2:C10，MATCH(MAX)(IF(A2:A10=F1，B2:B10))、IF(a 2: a10=F1，B2:B10)，0))

转换为：

=INDEX(C2:C10，MATCH(4，IF(A2:A10=F1，B2:B10)，0))

转换为：

=INDEX(C2:C10，MATCH(4，IF({ FALSE；假；假；假；假；真；真；真；真}，{ 4；2;5;3;1;3;4;1;2}),0))

转换为：

=INDEX(C2:C10，MATCH(4，{ FALSE假；假；假；假；3;4;1;2},0))

这一次，参数lookup_array数组中的最大值4与条件指定的序号有关，公式转换为：

=INDEX(C2:C10，7)

结果是：

2014-10-7

解决方案2:

下面的公式更优雅：

=LOOKUP(1，0/FREQUENCY(0，1/(1 (A2:A10=F1)*B2:B10))、C2:C10)

先看公式：

(A2:A10=F1)*B2:B10

转换为：

({12345;12345;12345;12345;12345;54321;54321;54321;54321}=54321)*B2:B10

转换为：

({ FALSE假；假；假；假；真；真；真；TRUE})*B2:B10

获取：

{0;0;0;0;0;3;4;1;2}

现在，来看看公式的巧妙之处。为了找到该数组中最大值的位置(而不是像方案1那样使用MATCH(MAX，…)组合，需要重复生成上述数组的子句)，请执行以下操作：

我们首先给上面数组中的每个值添加一个小值。这是必要的，因为数组中的值接下来将被递减计数。如果不执行此操作，数组中的零将导致# #DIV/0！错误，这使得将数组传递给FREQUENCY函数时变得更加复杂。

所以：

1/(1 (A2:A10=F1)*B2:B10)

转换为：

1/(1 {0;0;0;0;0;3;4;1;2})

获取：

{1;1;1;1;1;0.25;0.2;0.5;0.333333333333333}

这里的关键点是将这个数组作为bins_array参数的值传递给FREQUENCY函数，将零作为data_array参数的值。此时，公式构造：

FREQUENCY(0，1/(1 (A2:A10=F1)*B2:B10))

转换为：

频率(0，{ 1；1;1;1;1;0.25;0.2;0.5;0.333333333333333})

求倒数前数组中的最大值(即我们关心的值)，求倒数后数组中的最小值。此外，如果我们传递一个值数组，其中0和1之间的所有值作为FREQUENCY函数的参数bin _ array的值，0作为其参数data_array的值，那么零将被赋给参数bin _ array中的最小值；其余为空或零。

由于数组中的最小值为0.2，位于数组中的第7位，因此上述公式构造的结果为：

{0;0;0;0;0;0;1;0;0;0}

获得这个数组后，我们只需要从c列中与这个数组中出现的非零条目(即1)对应的位置返回数据。因此，公式为：

=LOOKUP(1，0/FREQUENCY(0，1/(1 (A2:A10=F1)*B2:B10))、C2:C10)

转换为：

=LOOKUP(1，0/{ 0；0;0;0;0;0;1;0;0;0}，C2:C10)

转换为：

=LOOKUP(1，{#DIV/0！#DIV/0！#DIV/0！#DIV/0！#DIV/0！#DIV/0！0;#DIV/0！#DIV/0！#DIV/0！}，C2:C10)

获取：

2014-10-7