excel vlookup如何做数据对比,Excel VLOOKUP 数据对比 教程

题图来自Unsplash，基于CC0协议

导读

在日常工作中，我们处理大量数据时常需要对两个不同表格或同一表格中不同区域的数据进行对比。Excel 的 VLOOKUP 函数在此场景下是极其有力的工具，它能帮助我们精确地查找一个表格中的数据在另一个表格中的存在与否或对应值。

核心思想：利用 VLOOKUP 查找键值信息

关键在于确认 VLOOKUP 查找目标列（查找键）是否在数据源列（查找范围）中存在。

基本语法回顾

=VLOOKUP(查找值， 查找范围， 索引号， [匹配方式])

其中：

• 查找值：你想在数据源中查找的值。
• 查找范围：包含 查找值 和目标数据列的多个列区域。
• 索引号：你想返回的数据所在列在 查找范围 中的序号。
• 匹配方式：FALSE（精确匹配，推荐），TRUE（近似匹配）。通常我们用 FALSE。

进行数据对比的步骤

假设我们有两个表格：“数据表A”和“数据表B”，都需要根据“ID”进行对比。

步骤 1：明确对比需求

你可能想：

• 检查 A 表某个 ID 是否存在 B 表中。
• 对比 A 表 ID 对应的某个字段在 B 表中的值是否相同。
• 找出 A 表有而 B 表没有的记录，或者反过来。

步骤 2：确定查找键

通常情况下，我们选择“ID”作为查找键，因为它应该是唯一的，并能唯一标识一个记录。确保在 A 表和 B 表中“ID”这一列的数据类型完全一致（都是数字或都是文本）。

步骤 3：构建 VLOOKUP 公式

以检查 A 表 ID B1 是否存在于 B 表（假设 B 表的 ID 列是 B:BSheet2!B:B）为例：

=IFERROR(VLOOKUP(A1, Sheet2!B:B, 2, FALSE), "B表找不到")

• A1 是我们要查找的目标值。
• Sheet2!B:B 是 B 表中包含了“ID”用于查找以及可能需要的数据（比如联系方式）的完整列范围。注意：这里 B 代表第二列，因此范围必须包含查找列和目的列，并且查找列必须是范围中的最左边一列。
• 2 表示返回查找范围（Sheet2!B:B）中的第二列的值，也就是 B 表中的“联系方式”列。
• FALSE 表示精确匹配。
• IFERROR(..., "B表找不到")：如果 VLOOKUP 找不到目标值，返回“B表找不到”这样的友好提示文本。IFERROR 是处理 VLOOKUP “#N/A” 错误的常用结构。

场景化的对比应用

场景一：单列精确匹配对比

如果你仅仅想确认 A 表 ID 是否出现在 B 表，可以使用 INDEX + MATCH 或直接使用 VLOOKUP 为了匹配方式的灵活性。两者都清楚匹配次数：

=IFERROR(VLOOKUP(A2, Sheet2!A:N, 15, FALSE), "")

这里 Sheet2!A:N 是 B 表的完整数据范围，15 是 S 列的索引（需要确保 S 列存在）。如果匹配成功，返回 B 表 S 列的值；如果失败，根据 IFERROR 返回空或提示。

场景二：查找完全匹配或部分匹配 (模糊匹配)

有时候我们想比较的信息不仅仅是精确匹配，也可能是按照日期或文本的部分内容，此时可以巧妙地结合 TEXT 函数或 LEFT 函数等。

例如，对比两个表中的日期是否在同一天： =IF(DATEVALUE(TEXT(A1,"yyyy/mm/dd")) = DATEVALUE(TEXT(VLOOKUP(A1,Sheet2!$B:$B,2,FALSE),"yyyy/mm/dd")), "日期一致", "日期不同")

注意事项

1. 查找值与范围格式一致：极容易忽略的是查找值和查找范围中指定列的格式必须完全一致。一个单元格是数字 100，另一个可能是文本 "100"，或者一个是纯数字，另一个包含了前导字符等，都会导致匹配失败。
2. 注意 $ 符号锁定范围：在构建查找范围时（如 $Sheet2!$A:$N），使用 $ 符号固定要查找和返回的列所在的区域，避免在拖拽填充公式时范围发生变化，这对长期维护数据表至关重要。
3. 索引号不能出错：一定要核对查找范围中目标数据列的序号是否正确，列序很重要。
4. 重复值问题：
   • VLOOKUP 查找时只返回第一个找到的结果。如果 ID 是重复的，你想知道匹配记录的条数或所有匹配项，就不能用 VLOOKUP。
   • 如果你需要找到所有匹配值，可能需要使用 FILTER（需要 Excel 365 或更高版本）、INDEX + SEQUENCE + FILTER 或 VBA 宏。
5. Text 再次强调：当比较日期或数字格式文本时（比如文本像"2023/10/27"这样的），非常容易因为格式差异匹配失败。可以使用 DATEVALUE 探针，或者都转换为 TEXT 再匹配。

VLOOKUP 无法匹配及解决方案

• Error #N/A：最常见的错误，原因包括：
  1. 格式不一致：查找值与查找范围内对应列的数据格式差异（数字/文本）。
  2. 数据确实不存在：查找值确实不在查找范围内。
  3. 文本包含空格或特殊字符：查找值或查找范围中的值前后有空格，或有不可见的换行符。可以使用 TRIM 和 CLEAN 来清理。
  4. 精度过度：如果设置了近似匹配 TRUE，查找值超出了范围内数据的范围。
• 公式指向错误单元格：
  • 当无法确定索引号时，可以使用 定位条件 -> 排除 > 空白（仅低版本）或 ISBLANK 函数辅助查找。手动检查是不可避免的验证方式。
• VLOOKUP 无法直接查找两个以上条件：这是 VLOOKUP 的固有缺陷，此场景下 INDEX + MATCH 大展拳脚。例如，你想根据“产品ID”和“销售日期”组合来查找数据。
• 查找范围过宽或过窄：确保查找范围包含了查找键列并且覆盖了需要返回数据的列。

关于 VLOOKUP 公式错误的检查

看到 # 错误代码时：

1. 仔细检查你的参数，看看是不是数值、索引号、范围、匹配类型这些参数填错了。
2. 公式是否指向上了错误的单元格？删除公式依赖区域的所有空单元格并检查。
3. IF 语句的嵌套是否正确？
4. 如果是文本和数值对比，用 &"" 强制数据类型转换试试吧。

总之，VLOOKUP 是强大的数据对比利器，但使用时需要细致和谨慎。理解它的特性，注意常见误区和错误原因，才能让它发挥最大效用。通过练习和理解基本公式结构，你会越来越熟练地用它来查找和对比数据。