如何運用 VLOOKUP 來比對兩個獨立工作表中的清單？

作者Xiaoyang修改日期2025-07-10

範例工作表1

範例工作表2

假設您有兩個工作表，每個工作表都包含一個名稱列表，如上方截圖所示。您可能想快速找出 Names-1 中有哪些名稱也同時出現在 Names-2 中。面對冗長的列表，手動比對不僅耗時費力，還極易出錯。本文將為您介紹幾種高效方法，助您輕鬆、準確地比較這兩個列表，迅速找出不同工作表之間相符的值！

使用公式透過 VLOOKUP 比較兩個分離工作表中的列表

使用 Kutools for Excel 透過 VLOOKUP 比較兩個分離工作表中的列表

使用條件格式跨工作表使用公式

VBA 程式碼－自動比對列表並標示或提取相符項目

使用公式透過 VLOOKUP 比較兩個分離工作表中的列表

想比較位於不同 Excel 工作表中的列表？一種實用又直接的方法就是使用 VLOOKUP 函數！此方法能有效提取或標記同時出現在 Names-1 與 Names-2 中的所有名稱：

1. 在 Names-1 工作表中，選取緊鄰您列表資料的儲存格（例如）B2），並輸入下列公式：

=VLOOKUP(A2,'Names-2'!$A$2:$A$19,1,FALSE)

接著按下 Enter。若目前列中的名稱存在於 Names-2 中，公式將傳回該名稱；若不存在，則會顯示 #N/A 錯誤。請參閱下方範例：

使用公式比較兩個清單

2. 向下拖曳填滿控點以複製公式，即可將 Names-1 中的每個名稱與 Names-2 中的所有名稱逐一比對。相符項目會顯示名稱，未找到的項目則會顯示錯誤值：

拖曳公式以取得結果

注意事項：

1. 為求更清晰，您可以使用以下替代公式，以「Yes」或「No」標示是否相符：

=IF(ISNA(VLOOKUP(A2,'Names-2'!$A$2:$A$19,1,FALSE)), "No", "Yes")

此公式會對同時出現在兩個工作表中的名稱顯示「Yes」，僅在 Names-1 中出現的名稱則顯示「No」：

另一個用來取得「是」與「否」結果的公式

2. 使用這些公式時，請將 A2 替換為您列表中的第一個儲存格，將 Names-2 替換為參考工作表的名稱，並將 $A$2:$A$19 調整為符合您工作表中的實際資料範圍。務必確認範圍的起始與結束位置正確，完整涵蓋所有資料！

3. 使用技巧：若應有相符結果卻出現 #N/A 錯誤，請仔細檢查列表中是否有多餘空格、資料格式不一致（文字 vs. 數字）或拼字錯誤。如有需要，可於輔助欄位中使用 TRIM 或 CLEAN 函數清理資料！

4. 為避免意外覆寫資料，建議在套用大量公式前先備份檔案。此外，在完成比對後，您可對公式結果欄位使用篩選功能，快速檢視所有相符或獨特項目。

使用 VLOOKUP 比較兩個分離工作表中的列表

若您已安裝 Kutools for Excel，只需幾個簡單步驟，即可透過其內建的選擇相同/不同單元格功能，快速找出並標示兩個獨立工作表中相同或不同的項目！此功能大幅降低手動比對的錯誤風險，尤其在處理大型資料集時，更能為您節省寶貴時間。立即點擊下載 Kutools for Excel！

使用 Kutools 比較不同工作表中的兩個清單

Kutools for Excel：提供超過 300 個實用的 Excel 增益集，免費試用 30 天，無任何限制。立即下載並免費試用！

使用 Kutools for Excel 透過 VLOOKUP 比較兩個分離工作表中的列表

若您已安裝 Kutools for Excel，其內建的選擇相同/不同單元格功能可協助您快速比對來自不同工作表的兩個列表，並立即選取或標示兩張工作表中的共同名稱——完全無需輸入複雜公式！當您處理大量資料，或希望獲得一目了然的彩色視覺效果時，此方法尤其高效。

Kutools for Excel 提供超過 300 項進階功能，簡化複雜作業，提升創造力與效率。整合 AI 功能，Kutools 能精準自動化各項任務，讓資料管理輕鬆無負擔。Kutools for Excel 的詳細資訊…… 免費試用……

安裝 Kutools for Excel 後，請依照下列步驟輕鬆比對您的列表：

1. 前往 Kutools 頁籤，點擊選取 > 選擇相同/不同單元格，如下圖所示：

按一下 Kutools > 選取 > 選取相同與不同的儲存格

2. 在開啟的選擇相同／不同單元格對話方塊中：

（1.）在尋找值於欄位中，選取您要進行比對的 Names-1 範圍；

（2.）在依據欄位中，選取用於比對的 Names-2 範圍；

（3.）在基於區段中，選取每一列，即可分別比對各列；

（4.）在尋找區段中，選取相同值，即可識別並標示相符的名稱；

（5.）您還可設定背景色或字體顏色，讓比對結果更加醒目。

在對話方塊中指定選項

3. 點擊 Ok 後，系統將彈出提示框，顯示已找到並標示了多少個相符的儲存格。所有同時出現在兩個列表中的名稱都會被自動選取並以視覺方式強調，方便您後續檢閱或修改：

跳出提示框，提醒已選取多少個相符的儲存格

立即點擊下載，免費試用 Kutools for Excel！

實用提示：若您的工作表包含大型資料集，建議標示後立即啟用篩選功能，快速檢視所有相符項目。此外，在執行比對前，請再次確認所選範圍是否正確對齊，並檢查是否包含標題列（除非您有意包含），因為範圍錯誤可能影響比對結果。

在極少數情況下，若功能未傳回預期結果，請檢查兩個列表的格式是否一致（例如皆為文字格式，且不含隱藏的前導或尾隨空格），因為格式差異可能導致相符項目遺漏。

使用條件格式跨工作表使用公式

如果您不想在欄位中撰寫公式或使用增益集，可以善用條件格式搭配自訂公式，根據另一個工作表的資料，以視覺方式標示其中一個工作表中的相符名稱。此方法簡單直接、無需使用 VBA，但不會產生獨立的結果列表——僅透過格式設定，讓相符項目一目了然！

適用情境：此解決方案適合希望以非侵入性的視覺方式標示相符數值，且不願變更工作表結構的使用者。需注意的是，條件格式規則無法直接參照其他活頁簿，且跨工作表的公式參照僅限於同一檔案內。

操作步驟：

1. 在 Names-1 中，選取您要套用標示的範圍（例如）A2:A19）。

2. 前往開始 > 使用條件格式 > 新增規則 > 使用公式決定要格式化哪些儲存格。

3. 在公式框中輸入下列公式：

=COUNTIF('Names-2'!$A$2:$A$19,A2)>0

此處檢查 Names-1 中 A2 儲存格的值，是否存在於 Names-2!A2:A19 範圍內的任一位置。

4. 點選格式以選擇醒目提示色彩，再點選確定套用此規則，所有相符項目將自動在您選取的區域中醒目提示。

實用技巧：您可以根據實際資料調整範圍，並將 COUNTIF 步驟與篩選功能結合，專注於已醒目提示的儲存格。設定跨工作表參照時，請務必確保兩個工作表位於同一活頁簿中，因為 Excel 不支援在條件格式規則中參照外部檔案。

錯誤提醒：若醒目提示未如預期顯示，請檢查儲存格範圍的選取與跨工作表參照是否正確。確保資料前後無多餘空格或格式不一致，以免造成比對遺漏。如有需要，可於輔助欄位使用 TRIM 函數清理清單，確保比對結果準確無誤！

VBA 程式碼－自動比對列表並標示或提取相符項目

對於熟悉巨集的使用者來說，運用 VBA 程式碼能在不同工作表間靈活且自動化地比對兩個清單。此方法不僅能醒目標示相符的姓名，還可將相符項目提取至新位置，特別適合處理大量資料，或在清單變動時快速更新的場景。

適用情境：此解決方案特別適合需要反覆執行比對、處理超大資料集、自動化報表，或進一步自訂相符項目的處理與呈現方式。雖然需具備 VBA 知識，但您將獲得完整的自動化能力與掌控權。唯一限制是活頁簿必須啟用巨集，而某些環境可能因安全性設定而不允許。

如何執行巨集，若 Names-1 中的姓名也出現在 Names-2 中，則予以醒目提示：

1. 按一下開發人員工具 > Visual Basic，以開啟 Microsoft Visual Basic for Applications 視窗。接著，在視窗中按一下插入 > 模組，並將下列程式碼貼到新模組中：

Sub HighlightMatchingNames()
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim rng1 As Range
    Dim cell As Range
    Dim matchFound As Range
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set ws1 = Worksheets("Names-1")
    Set ws2 = Worksheets("Names-2")
    
    Set rng1 = ws1.Range("A2", ws1.Cells(ws1.Rows.Count, "A").End(xlUp))
    
    ws1.Range("A2:A" & ws1.Cells(ws1.Rows.Count, "A").End(xlUp).Row).Interior.ColorIndex = xlNone
    
    For Each cell In rng1
        Set matchFound = ws2.Range("A2:A" & ws2.Cells(ws2.Rows.Count, "A").End(xlUp).Row).Find( _
            What:=cell.Value, LookIn:=xlValues, LookAt:=xlWhole)
        
        If Not matchFound Is Nothing And cell.Value <> "" Then
            cell.Interior.Color = vbYellow
        End If
    Next cell
End Sub

2. 在 VBA 編輯器中，按一下按鈕以執行程式碼。此巨集會掃描「Names-1」工作表 A 欄中的姓名，若該姓名也出現在「Names-2」工作表的 A 欄中，便會在「Names-1」中將對應儲存格以黃色填充醒目提示。執行新比對前，系統會自動清除範圍內先前的醒目提示。

疑難排解：若未有任何儲存格被醒目提示，請確認兩個工作表名稱確實為「Names-1」與「Names-2」，且資料區域從 A2 開始。同時，請確保已啟用巨集，且任一工作表皆未受保護或套用篩選。此方法可輕鬆自訂——例如，您可變更醒目提示色彩，或調整程式碼將相符結果複製至其他工作表或欄位！

總結與建議：根據您的需求與技術熟練度，可選擇內建公式解法、巨集自動化、Kutools 等智慧型增益集，或運用條件格式直觀呈現結果。使用公式或 VBA 時，務必檢查資料是否含有常見錯誤來源，例如多餘空格或格式不一致。首次透過巨集或增益集進行批次變更前，請務必先備份資料。若遇到公式未更新或比對錯誤等問題，請檢查相對／絕對範圍設定是否正確，並驗證工作表名稱。選用最契合您工作流程的方法，即可在 Excel 中高效且精準地跨工作表比較清單！

最佳 Office 生產力工具

🤖	KUTOOLS AI 助手：基於以下內容徹底革新數據分析：智慧執行｜產生程式碼｜建立自訂公式｜分析資料並產生圖表｜呼叫增強函數……
	熱門功能：尋找、醒目提示或標記重複值｜刪除空白行｜合併列或儲存格而不遺失資料｜不使用公式的四捨五入……
	高級 LOOKUP：多重條件 VLookup ｜多重數值 VLookup ｜跨多個工作表 VLookup ｜模糊查找……
	高級下拉列表：快速建立下拉式清單｜相依式下拉式清單｜多選下拉式清單……
	欄位管理員：新增指定數量的欄位｜移動欄位｜切換隱藏欄位的可見狀態｜比較範圍與欄位……
	精選功能：網格聚焦｜設計視圖｜增強編輯欄｜工作簿與工作表管理員｜資源庫（自動文字）｜日期提取｜合併工作表｜加密／解密儲存格｜依清單傳送電子郵件｜超級篩選｜特殊篩選（篩選粗體儲存格／斜體／刪除線……） ......
	頂尖 15 工具組：12 文字工具（添加文本，刪除特定字符，……）｜ 50+ 圖表類型（甘特圖，……）｜ 40+ 實用公式（基於生日計算年齡，……）｜ 19 插入工具（插入二維碼，從路徑插入圖片，……）｜ 12 轉換工具（金額轉大寫，匯率轉換，……）｜ 7 合併和拆分工具（高級合併行，分割儲存格，……）｜……以及更多