VLOOKUP 比较 Microsoft Excel 中的两列以查找共同值和缺失数据

当数据存在于两个不同的列表中时，我们可能经常需要比较它们，以查看其中一个列表中缺少哪些信息，或者两个列表中通常存在哪些数据（即常见数据）。并且可以以 Excel 中可用的各种方式执行比较，个人使用的方法将取决于结果（个人想要的结果）。

在本教程中，我们将探讨以下各种概念：

1. VLOOKUP 公式用于比较 Microsoft Excel 中的两列。
2. 比较 Excel 不同工作表中的两列。
3. 比较两列以在 Excel 工作表中查找匹配项。
4. 比较两列以在 Microsoft Excel 中查找缺失数据。
5. 如何识别 Microsoft Excel 中两列之间的匹配项和差异？

我们如何借助 VLOOKUP 公式在 Microsoft Excel 中比较两列？

在某些情况下，我们有两列数据，并且我们想找出其中一个列表中的哪些数据点主要存在于另一个列表中，为了实现这一点，我们可以使用“VLOOKUP”函数来比较列表以获取常见值。

如果我们要以基本形式构建一个 “VLOOKUP”公式，那么我们需要执行以下操作：

1. 对于 lookup_value（第一个参数），它使用列表 1 中的最顶部单元格。
2. 对于 table_array（第二个参数），用于提供整个列表 2。
3. 对于 col_index_num（第三个参数），它使用 1，因为数组中存在一列。
4. 对于 range_lookup（第四个参数），设置为 FALSE - 用于精确匹配。

假设我们有列“A”中存在的参与者姓名（例如列表 1），以及列“B”中存在的通过资格赛的参与者姓名（例如列表 2）。我们想比较这两个列表，以确定小组 A 中的哪些参与者成功入围主赛事。为了实现这一点，我们将使用下面提到的公式：

公式

上述相应的公式将输入到单元格 E2 中。之后，我们将它拖到包含列表 1 中项目的单元格中。

table_array 主要用绝对引用（$C$2:$C$9）锁定，因此当将公式复制到下面的各个单元格时，它保持不变。

现在我们遇到了一个 Excel 工作表，其中合格运动员的姓名显示在 E 列中。对于其余的参与者，主要出现 #N/A 错误，这意味着他们的姓名与列表 2 不关联。

隐藏 Excel 工作表中的 #N/A 错误

我们上面借助示例讨论的 VLOOKUP 公式基本上有效地实现了其主要目标，这意味着返回共享值并识别各个使用的工作表中缺失的数据点。

此外，它们还会产生一堆 #N/A 错误，这可能会让没有经验的用户感到困惑，并让他们认为自己在创建公式时犯了一些错误。

要用相应的空白单元格替换 #N/A 错误，请将 VLOOKUP 公式与 “IFNA”或 “IFERROR” 函数结合使用。

公式

众所周知，我们改进的公式将返回一个空字符串（“”）而不是返回 #N/A 错误，我们还可以返回我们**自定义的文本**，例如“列表 2 中不可用”、“列表中不存在”或“不在列表 2 中”。

• 例如

这被认为是根据特定任务可用于比较 Microsoft Excel 中两列的基本 VLOOKUP 公式。

如何借助 VLOOKUP 比较 Microsoft Excel 不同工作表中的两列？

在现实生活中，我们需要比较的特定列有时存在于不同的工作表中。在小型数据集中，我们可以通过并排查看两个工作表来手动发现差异。

要在其他工作表中通过公式搜索它，我们必须使用外部引用。最好的做法是在主工作表中输入公式并切换到不同的工作表，用鼠标选择列表。

假设我们在 Sheet1 的 A 列中有列表 1，在 Sheet2 的 A 列中有列表 2，我们可以比较这两列并借助下面提到的公式查找匹配项：

我们如何比较两列并返回一个共同值？

在上面的示例中，我们讨论了最简单的 VLOOKUP 公式：

上述公式的结果是主要存在于两列中的值列表，以及在第二列中不可用的值的位置的空白单元格。为了获得没有间隙的共同值列表，我们将向结果列添加自动筛选器并筛选出空白。

在支持动态数组的 Microsoft 365 和 Excel 2021 版 Microsoft Excel 中，我们可以使用“FILTER”函数来动态筛选出相应的空白。

为此，我们将使用 IFNA VLOOKUP 公式作为 FILTER 的条件：

我们应该注意，在这种情况下，我们必须将整个列表 1 (A2:A14) 提供给 VLOOKUP 的 lookup_value 参数。该函数会将每个查找值与列表 2 (C2:C9) 进行比较，并返回一个匹配项数组和表示缺失值的 #N/A 错误。IFNA 函数通常将错误替换为空字符串，并将结果提供给 FILTER 函数，该函数会筛选出空白（<>“”）并有效地输出匹配项数组作为最终结果。

我们如何比较两列并查找缺失值？

现在要在 Microsoft Excel 中比较两列以查找差异或缺失值，我们可以使用以下方法：

1. 首先，我们将编写核心公式，以在列表 2 ($C$2:$C$9) 中搜索列表 1 (A2) 中的第一个值：

1. 之后，我们将上述公式嵌套在 ISNA 函数中，以检查 VLOOKUP 的输出中是否存在 #N/A 错误，如果出现错误，ISNA 返回 TRUE；否则，它将返回 FALSE。

1. 我们将使用 ISNA VLOOKUP 公式作为 IF 函数的逻辑测试。如果测试结果为 TRUE（#N/A 错误），它将返回同一行中列表 1 的值；如果测试结果为 FALSE（在列表 2 中找到匹配项），则返回一个空字符串。

完整的公式通常采用以下形式：

为了消除空白，我们将应用 Microsoft Excel 的筛选器。

如何识别 Microsoft Excel 中两列之间的匹配项和差异？

现在，如果我们基本上想为第一个列表添加文本标签，以指示哪些值在第二个列表中可用，哪些值不可用，那么我们将使用 VLOOKUP 公式与 IF 和 ISNA/ISERROR 函数结合使用。

• 例如：假设我们想识别在 A 列和 D 列中都存在的姓名以及只在 A 列中存在的姓名，公式如下：

在这里，ISNA 函数捕获 VLOOKUP 生成的 #N/A 错误，并将该中间结果传递给 IF 函数，以便为错误返回指定的文本，并为成功查找返回其他文本。

在此示例中，我们使用了“未合格”或“合格”标签，它们适合我们选择的数据集。它可以替换为“不在列表 2 中”或“在列表 2 中”。

上述公式可以有效地插入到列表 1 旁边的列中，并通过包含我们所选列表中项目的各个单元格进行复制。

还可以使用“MATCH”函数来识别两列中的匹配项和差异的其他基本方法。