找出遺漏的值

有時您需要比對兩個清單，確認清單 A 中的項目是否也出現在清單 B 中。例如，您手邊有一份產品清單，想確認這些產品是否同時列於供應商所提供的清單內。為協助您完成這項任務，以下提供三種方法，歡迎選用最適合您的方式。

使用 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，因為當某個值在另一份清單中不存在（即遺漏）時，VLOOKUP 會傳回 #N/A 錯誤，其行為與 MATCH 相同。

通用語法

=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 存在於陣列 $B$6:$B$10 的第一欄中，VLOOKUP 將傳回該值；否則將傳回 #N/A 錯誤。由於此處 3004 並未出現在該陣列中，因此結果為 #N/A。#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 時所傳回的值）。

相關函數

相關公式