☰

Excel VLOOKUP 查找 2 列

tag-icon • tag-icon microsoft-excel
Excel VLOOKUP 查找 2 列

我对 VLOOKUP 函数有疑问。这是我的表格：

sheet1

id_1  id_2 count
28273 15   5
31866 19   4
2827  315  5
...

sheet2

id_1  id_2 count
2827  315  155
141   1064 555
...

基本上，我想将count来自添加sheet2到 中的相应行sheet1。但我遇到了一个问题：为了比较值，我将它们连接起来：因此，对于 中的第一行，sheet1我id_1&id_2在 VLOOKUP 中使用值。问题是我28273 and 15从中sheet1获得了来自的值。所以，这不是我需要的精确匹配。2827 and 315sheet2

我应该使用什么公式来获取count相应sheet2值，例如：

sheet1

id_1  id_2 count count2
2827  315  5     155
...

答案1

在您的评论中，您说您不想使用连接或辅助列。这里有一种方法可以做到这一点。

我使用了你的例子。Sheet2：

在此处输入图片描述

工作表1：

在此处输入图片描述

我只将公式放在匹配的单元格中，因为我不知道您想如何处理任何潜在的不匹配（您可以将其包装在 IFERROR 中并显示空白）。我确实在所有行上进行了测试，它正确处理了您的第 1 行条件（根据有限的数据和没有错误处理规定返回错误）。D4 中的公式：

=INDEX(Sheet2!$C$2:$C$6,MATCH(1,(Sheet2!$A$2:$A$6=A4)*(Sheet2!$B$2:$B$6=B4),0))

请注意，图中它被花括号包围。这是因为这是一个数组公式，必须用Ctrl++而不是 来确认。ShiftEnterEnter

INDEX 从 Sheet2 中提取匹配计数。它执行逻辑测试，只有当两个条件都为真时该测试才为真，因此它将1(True) 与每行的结果进行比较。它将 Sheet2 的 A 列值与 Sheet1 中的 A 列值进行比较，并将 Sheet2 的 B 列值与 Sheet1 中的 B 列值进行比较。如果它找到一条记录，其中 A 列和 B 列值都与当前行的 A 列和 B 列值匹配，则为匹配。

如果您可以设定 Sheet1 中并非每一行都有可在 Sheet2 上检索的计数，则在 Count2 列中显示 Sheet1 上的空白的简单方法是像这样包装公式（仍然是数组公式）：

=IFERROR( INDEX(Sheet2!$C$2:$C$6,MATCH(1,(Sheet2!$A$2:$A$6=A4)*(Sheet2!$B$2:$B$6=B4),0)) ,"")

答案2

如果两个键值创建唯一键，那么您可以使用 SUMIFS 并跳过数组公式：

=SUMIFS(Sheet2!C:C,Sheet2!A:A,A2,Sheet2!B:B,B2)

这将返回0任何没有匹配键值的结果，但您可以使用自定义格式在 0时#;#;;@显示空单元格。或者#;#;"No Match";@在No Match0

答案3

评论中提出了最简单的解决方案，即在列之间使用某种唯一符号来为两张表创建辅助列，例如：id_1&"."&id_2

因此，第一个 - 我为sheet2as创建了辅助列id_1&"."&id_2。

然后我有sheet1列：

id_1 id_2 count1 count2
...

在sheet2：

id_1 id_2 helper count2
...

VLOOKUP然后我通过以下方式比较列sheet1:count2： VLOOKUP(sheet1!id_1&"."&sheet1!id_2;sheet2!helper:count2;2;FALSE)

答案4

=INDEX(SHEET2!C:C,aggregate(14,6,row(Sheet2!A$2:A$10)/((Sheet2!A$2:A$10=A2)*(Sheet2!B$2:B$10=B2)),1))

上述公式使用 AGGREGATE，它执行类似数组的操作但实际上不是数组，至少对于某些函数（如 14 和 15）而言是如此。14 指示 AGGREGATE 按从大到小的顺序对结果进行排序，15 则按从小到大排序。6 指示 AGGREGATE 忽略导致错误的结果。

ROW(SHEET2!A$2:A$10)

这部分为 AGGREGATE 提供了当前正在评估的行号。

((Sheet2!A$2:A$10=A2)*(Sheet2!B$2:B$10=B2))

这部分是两个条件，为了让 AGGREGATE 不收到错误，这两个条件都需要为真。* 的作用类似于 AND 函数。当两个或其中一个为假时，结果最终为 0，从而导致除以 0 的错误。如果两个结果都为真，则结果为 1，并且行号不会因除以 1 而改变。您将获得符合条件的筛选结果列表。

1 告诉 AGGREGATE 从排序列表中返回 1 个结果。这意味着，如果有多个行符合您的条件，则 AGGREGATE 函数 14 将返回匹配的最后一行，而函数 15 将返回第一行。

现在您有了行号，可以将其放入完整的列引用中，以便 INDEX 从该列的相应行中提取信息。

由于 AGGREGATE 使用类似数组的计算，因此应避免在 AGGREGATE 函数中使用完整的列引用，以减少额外的计算，因为这可能会使系统陷入困境。在 AGGREGATE 函数中，范围应限制在您的数据内。

调整范围以适合您的数据。

当列表中未找到某些内容时，您没有说明您想要的结果。目前它将返回一个错误。为了整理它，您可以将整个内容包装在 IFERROR 函数中并使其看起来像以下内容：

=IFERROR(INDEX(Sheet2!C:C,AGGREGATE(14,6,ROW(Sheet2!A$2:A$10)/((Sheet2!A$2:A$10=A2)*(Sheet2!B$2:B$10=B2)),1)),"NO MATCH")

波克

poc2

相关内容