☰

搜索必须存在于一行中的两个值，然后返回不同列中的值

tag-icon • tag-icon microsoft-excel worksheet-function
搜索必须存在于一行中的两个值，然后返回不同列中的值

我在 A、B 和 C 列中有值：

1 - 1 - 2

1 - 2 - 3

2 - 2 - 4

3 - 4 - 1

2 - 3 - 5

和另一列（假设这是在 E 列）：

1

1

2

3

4

注意：所有列都从第 1 行开始。

我想搜索在 A 列范围内包含 E1 的值并在 B 列范围内包含 E2 的值的行。如果该行包含这两个值，则返回 C 列中的值

我在 F 列创建了以下函数：

=IF(AND(E1=$A$1:$A$4,E2=$B$1:$B$4),$C$1:$C$4)

只有当符合我的标准的值位于同一行时，它才有效，否则我会得到 false。举例说明问题：

EFABC

1 2 ---------------------> 1 1 2 ：因为 E1 = A1 且 E2 = B1 - F1 将 = 2

1 3 ---------------------> 1 2 3 ：因为 E2 = A2 且 E3 = B2 - F2 将为 = 3 2 假 -----------------> 2 2 4 ：因为 E3 = A3 但 E4 != B3 - F3 为假（应该是 5）

有什么建议吗？

答案1

尝试一下：

=INDEX(C:C,MATCH(1,(E1=A:A)*(E2=B:B),0))

不幸的是我不能承担这份荣誉，因为它来自exceljet.net 上的这篇精彩文章

（最）相关的部分，包含一些拼写错误更正：

此公式的工作原理：

通常情况下，INDEX MATCH 公式配置为 MATCH 设置为查看单列范围并根据给定条件提供匹配项。如果不在辅助列或公式本身中连接值，则无法提供多个条件。

此公式通过使用布尔逻辑创建一个由 1 和 0 组成的数组来表示符合所有 3 个条件的行，然后使用 MATCH 来匹配找到的第一个 1，从而解决了这个限制。

临时的 1 和 0 数组基于以下代码片段：

(H5=B5:B11)*(H6=C5:C11)*(H7=D5:D11)

在这里，我们将 H5 中的商品与所有商品进行比较，将 H6 中的尺寸与所有尺寸进行比较，将 H7 中的颜色与所有颜色进行比较。初始结果如下所示：

{TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE}*{FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE}*{TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE}

乘法运算将 TRUE FALSE 值转换为 1 和 0：

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

最终结果如下：

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

作为查找数组进入 MATCH：

MATCH(1,{0;0;1;0;0;0;0})

MATCH 返回 3，整个公式归结为标准 INDEX MATCH 公式

=INDEX(E5:E11,3)

最终结果是17.00美元。

答案2

您可以使用以下数组公式：

{=IFERROR(INDEX($C$1:$C$5,SMALL(IF(($A$1:$A$5=E1)*($B$1:$B$5=E2),ROW($1:$5)-ROW($1:$1)),ROW($A1))),"")}

注意：

  • 用 完成公式Ctrl+Shift+Enter。
  • 根据需要调整公式中的单元格引用。

相关内容