如何在分開的工作表中使用vlookup比較兩個清單？

使用公式在不同的工作表中用Vlookup比較兩個清單

一種實用且直接的方法是利用VLOOKUP函數來比較位於不同Excel工作表中的清單。此方法可幫助你有效地提取或標記在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. 為了更清楚起見，你可以使用此替代公式來返回“是”或“否”的匹配指示：

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

該公式對於兩張工作表中都存在的名稱顯示“是”，而僅在Names-1中找到的名稱則顯示“否”：

2. 使用這些公式時，將A2替換為你清單中的第一個單元格，將Names-2替換為參考工作表的名稱，並將$A$2:$A$19調整為與你工作表中的實際數據範圍相符。請記住，範圍必須以正確的行號開始和結束，以確保所有數據都被包括在內。

3. 使用提示：如果在應該有匹配項的地方遇到#N/A錯誤，請仔細檢查可能由額外空格、數據格式差異（文本與數字）或列表中的拼寫錯誤引起的問題。如有必要，可以使用TRIM或CLEAN在輔助列中清理數據。

4. 為避免意外覆蓋，考慮在應用批量公式之前備份你的數據。此外，在比較之後，你可以在公式結果列上使用篩選功能，快速查看所有匹配項或唯一項目。

使用Kutools for Excel在不同工作表中用Vlookup比較兩個清單

如果你有Kutools for Excel，其選擇相同和不同單元格功能可以幫助你快速比較來自不同工作表的兩個清單，並選擇或突出顯示這兩個工作表之間的共同名稱——所有這些都無需輸入複雜的公式。這個方法在處理大量數據或希望獲得視覺上的顏色編碼結果時特別有效，一目了然。

安裝Kutools for Excel後，按照以下步驟輕鬆比較你的清單：

1. 轉到Kutools選項卡，然後點擊選擇 > 選擇相同和不同單元格，如下所示：

2. 在打開的選擇相同和不同單元格對話框中：

(1.) 在查找值中，選擇你要比較的Names-1範圍；

(2.) 在根據中，選擇要比較的Names-2範圍；

(3.) 在基於部分，選擇按行分別比較行；

(4.) 從查找部分，選擇相同值來識別並突出顯示匹配的名稱；

(5.) 可選地，你可以設置背景色或字體顏色來突出顯示結果，使匹配項在視覺上突出。

3. 點擊確定，你將看到一個提示框，顯示有多少匹配的單元格已被找到並突出顯示。所有在兩個清單中存在的名稱將被選擇並視覺化強調，簡化進一步的審查或修改：

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

實用提示：如果你的工作表包含大型數據集，考慮在突出顯示後使用篩選功能，快速查看僅匹配的項目。另外，在運行比較之前，仔細檢查你的範圍選擇是否正確對齊，並且不要包括標題行（除非有意為之），因為不匹配可能會影響結果。

在極少數情況下，如果該功能未返回預期結果，請檢查兩個清單是否格式化相同（例如，都是文本，沒有隱藏的前導/尾隨空格），因為格式不一致可能會導致匹配遺漏。

跨工作表使用帶公式的條件格式化

如果你不想在列中寫入公式或使用插件，你可以利用帶自定義公式的條件格式化來視覺化識別基於另一個工作表數據的匹配名稱。此方法簡單直觀，不需要VBA，但不會返回單獨的結果列表——它只是格式化匹配項以便快速瀏覽。

適用場景：這個解決方案非常適合想要非侵入性、視覺指標匹配值並且不希望改變工作表結構的用戶。局限性在於條件格式化規則不能直接引用另一個工作簿，並且跨工作表引用公式只在同一文件中有效。

步驟：

1. 在Names-1中，選擇你希望應用高亮的範圍（例如，A2:A19）。

2. 轉到Home > 條件格式化 > 新規則 > 使用公式確定要格式化的單元格。

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的不同工作表中比較清單。