查找缺失值

有时，您需要对比两个列表，检查列表 A 中的值是否出现在列表 B 中（Excel）。例如，您拥有一份产品清单，需要核查这些产品是否包含在供应商提供的产品列表中。为帮助您实现这一目标，本文汇总了三种实用方法，您可根据实际需求灵活选择。

用 MATCH、ISNA 及 IF 查找缺失值
用 VLOOKUP、ISNA 及 IF 查找缺失值
用 COUNTIF 和 IF 查找缺失值

用 MATCH、ISNA 和 IF 查找缺失值

如上图所示，若要查找您列表中所有产品是否存在于供应商列表中，可以先使用 MATCH 函数查找某一产品（列表 A 中的值）在供应商列表（列表 B）中的位置。若未找到，MATCH 会返回 #N/A 错误，然后可将该结果传递给 ISNA，将 #N/A 错误转换为 TRUE，表示该产品缺失。最后，IF 函数即可返回您想要的结果。

通用语法

=IF(ISNA(MATCH(）））"lookup_value",lookup_range,0)),"Missing","Found")

√ 注意：您可以根据实际需求，将“Missing”和“Found”修改为任意值。

要查找您列表中所有产品是否存在于供应商的列表，请在单元格 H6 输入或粘贴以下公式，并按下 Enter 即可查看结果：

=IF(ISNA(MATCH(）））30002,$B$6:$B$10,0)),"Missing","Found")

或者，使用一个单元格引用让公式更具动态性：

=IF(ISNA(MATCH(）））G6,$B$6:$B$10,0)),"Missing","Found")

√ 注意：上述美元符号($) 表示绝对引用，也就是说，当您移动或复制公式到其他单元格时，公式中的 lookup_range 范围不会发生变化。而 lookup_value 未加美元符号，是为了让其能够动态变化。输入公式后，向下拖动填充柄即可将公式应用到下方单元格。

公式说明

此处以以下公式为例：

=IF(）ISNA(）MATCH(G8,$B$6:$B$10,0)),"Missing","Found")

• MATCH(G8,$B$6:$B$10,0)： 将 match_type 设置为 0 时，MATCH 函数会返回一个数值，表示 3004（即单元格 G8 中的值）在数组 $B$6:$B$10 中首次出现的位置。但在本例中，MATCH 未能在查找数组中找到该值，因此会返回 #N/A 错误。
• ISNA(）MATCH(G8,$B$6:$B$10,0))=ISNA(）#N/A)：ISNA 用于判断某个值是否为 “#N/A” 错误。如果是，函数返回 TRUE；如果不是 “#N/A” 错误，则返回 FALSE。因此，该 ISNA 公式将返回 TRUE。
• IF(）ISNA(）MATCH(G8,$B$6:$B$10,0)),"Missing","Found") = IF(TRUE，“Missing“，“Found“）： 当 ISNA 与 MATCH 判断结果为 TRUE 时，IF 函数会返回 Missing，否则返回 Found。因此，该公式将返回 Missing。

通过 VLOOKUP、ISNA 和 IF 查找缺失值

如果您想判断列表中的所有产品是否都包含在供应商列表中，可以将上述的 MATCH 函数替换为 VLOOKUP。因为与 MATCH 一样，VLOOKUP 在另一个列表中找不到对应值时，也会返回 #N/A 错误，表示该项缺失。

通用语法

=IF(ISNA(VLOOKUP(）））"lookup_value",lookup_range,1,FALSE)),"Missing","Found")

√ 注意：您可根据需求将 “Missing”、“Found” 更改为任何值。

要判断列表中的所有产品是否都存在于供应商列表中，请将以下公式复制或输入到单元格 H6 中，然后按下 Enter 键获取结果：

=IF(ISNA(VLOOKUP(）））30002,$B$6:$B$10,1,FALSE)),"Missing","Found")

或者，使用一个单元格引用让公式更具动态性：

=IF(ISNA(VLOOKUP(）））G6,$B$6:$B$10,1,FALSE)),"Missing","Found")

√ 注意：上述美元符号（$） 表示绝对引用，也就是说，lookup_range 在您移动或复制公式到其他单元格时，公式中的该范围不会发生变化。但对于 lookup_value 没有添加美元符号，是因为需要它动态变化。输入公式后，向下拖动填充柄以将公式应用于下方单元格。

公式说明

此处以以下公式为例：

=IF(）ISNA(）VLOOKUP(G8,$B$6:$B$10,1,FALSE)),"Missing","Found")

• VLOOKUP(G8,$B$6:$B$10,1,FALSE)： 将 range_lookup 设置为 FALSE，可让 VLOOKUP 函数查找并返回与 3004（即单元格 G8 的值）完全匹配的数值。如果 3004 出现在 1 数组第 1 列 $B$6:$B$10 中，VLOOKUP 即会返回该值；否则将返回 #N/A 错误值。本例中，3004 未出现在数组中，因此结果为 #N/A。
• ISNA(）VLOOKUP(G8,$B$6:$B$10,1,FALSE))=ISNA(）#N/A)：ISNA 用于判断某个值是否为 “#N/A” 错误。如果是，函数将返回 TRUE；如果不是 “#N/A” 错误，则返回 FALSE。因此，该 ISNA 公式会返回 TRUE。
• IF(）ISNA(）VLOOKUP(G8,$B$6:$B$10,1,FALSE)),"Missing","Found") = IF(TRUE，“Missing“，“Found“）： 当 ISNA 与 VLOOKUP 的判断结果为 TRUE 时，IF 函数将返回 Missing，否则返回 Found。因此，该公式会返回 Missing。

通过 COUNTIF 和 IF 查找缺失值

如果您想要判断列表中的所有产品是否都存在于供应商的列表中，可以用更简单的 COUNTIF 和 IF 函数公式实现。该公式利用 Excel 除零（0)外任何数字都会被判断为 TRUE 这一特性。如果某个值在另一个列表中存在，COUNTIF 会返回该值在列表中的出现次数，IF 会将该数字作为 TRUE；如果值不在列表中，COUNTIF 会返回 0，IF 就会将其作为 FALSE。

通用语法

=IF(COUNTIF(））"lookup_range",lookup_value),"Found","Missing")

√ 注意：您可以根据需要将 “Found”、“Missing” 更改为任意内容。

要判断列表中的所有产品是否都存在于供应商列表中，请将以下公式复制或输入到单元格 H6 中，然后按下 Enter 键获取结果：

=IF(COUNTIF(））$B$6:$B$10,30002),"Found","Missing")

或者，使用一个单元格引用让公式更具动态性：

=IF(COUNTIF(））$B$6:$B$10,G6),"Found","Missing")

√ 注意：上述美元符号（$） 表示绝对引用，也就是说，lookup_range 在您移动或复制公式到其他单元格时，公式中的该范围不会发生变化。但对于 lookup_value 没有添加美元符号，是因为需要它动态变化。输入公式后，向下拖动填充柄以将公式应用于下方单元格。

公式说明

此处以以下公式为例：

=IF(）COUNTIF($B$6:$B$10,G8),"Found","Missing")

• COUNTIF($B$6:$B$10,G8)：COUNTIF 函数用于统计 3004（即单元格 G8 的值）在数组 $B$6:$B$10 中出现的次数。由于 3004 并未出现在该数组中，因此结果为 0.
• IF(）COUNTIF($B$6:$B$10,G8),"Found","Missing") = IF(0，“Found“，“Missing“）：IF 函数会将 0 视为 FALSE。因此，该公式会返回 Missing，即当第一个条件判断为 FALSE 时所返回的值。

相关函数

相关公式