如何使用 VLOOKUP 比較 Excel 中的兩列

在 Excel 中處理大型數據集時，您可能需要比較兩列以找出相似點或不同點。

VLOOKUP 是一個強大的功能，允許您搜索兩列之間的匹配數據。該函數允許您在一列中搜索出現在另一列中的值。

本文向您展示如何使用 VLOOKUP 比較兩列，以便您可以高效地分析數據。

目錄

• Vlookup 函數基礎
  • Vlookup函數
• 為 VLOOKUP 準備數據
  • 格式化數據
• 如何使用 VLOOKUP 比較兩列
  • 處理其他錯誤
• 用於列比較的 VLOOKUP 替代方法
  • 1.使用索引和匹配功能
  • 2. 在 Excel 中使用 XLOOKUP
• 提高 VLOOKUP 性能的五個技巧
  • 1.限制你的查找範圍
  • 2.使用絕對引用
  • 3. 對數據進行排序
  • 4.使用雙VLOOKUP
  • 5.使用條件格式
• VLOOKUP 的高級用途
• 我們的最終決定

Vlookup 函數基礎

假設您有一個電子表格，其中包含 A 列和 B 列中的兩個項目列表。您想要查找列表 1 中也出現在列表 2 中的項目。

您可以想像手動處理包含數千個項目的列表將非常耗時。值得慶幸的是，Excel提供了 VLOOKUP 來拯救！

術語 VLOOKUP 代表垂直查找。該函數將比較兩列，找到它們之間的匹配項，並返回相關聯的值。

Vlookup函數

下面是 VLOOKUP 函數的基本語法：

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

這是元素的細分：

• lookup_value：要在 table_array 的第一列中搜索的值。

• table_array：包含要在其中搜索的數據的單元格範圍。

• col_index_num：要從中返回值的 table_array 中的列號。

• range_lookup：可選。它要么是 TRUE（近似匹配），要么是 FALSE（完全匹配）。默認值為 TRUE。

為 VLOOKUP 準備數據

在使用 VLOOKUP 比較Excel 中的兩列之前，您需要準備數據。

在您要比較值的工作表中創建兩個單獨的列。本文使用 A 列和 B 列作為我們的示例。

這是本文中使用的示例數據：

這是帶有要添加到標題為“為 VLOOKUP 準備數據”部分的標題的示例表：

清單 1 清單 2
項目 1 項目 3
項目 2
項目 4 項目 3 項目 1
項目 4 項目 5

格式化數據

確保兩列中數據的格式相似很重要。VLOOKUP 區分大小寫，這意味著大寫和小寫字符很重要。

如果格式不一致，最終結果可能會出現匹配錯誤。

刪除任何重複值或空白單元格以最大限度地降低出錯風險也是一個好主意。

要刪除重複值：

1. 選擇列。

2. 轉到頂部功能區中的“數據”選項卡。

3. 單擊數據工具部分中的刪除重複項。

該按鈕可能有點難以看到。這張圖可以幫助你：

要刪除空白單元格：

1. 選擇列。

2. 轉到頂部功能區中的主頁選項卡。

3. 展開“查找並選擇”菜單。

4. 選擇“轉到特殊”。

5. 從選項中選擇“空白”。

6. 單擊“單元格”部分中的“刪除”。

如何使用 VLOOKUP 比較兩列

準備好數據後，您可以編寫 VLOOKUP 公式來比較 Excel 中的兩列並確定匹配項。按著這些次序：

1. 在要顯示比較結果的新列中選擇一個單元格（例如，單元格 C2）。

2. 輸入以下公式：=VLOOKUP(A2, B:B, 1, FALSE))

3. 按 Enter 鍵應用公式。

4. 在匹配值的情況下，該值將顯示在結果列的同一行中（例如，C 列）。

5. 將公式從 C2 向下拖動以將其複製到所需數量的單元格上。

如果您使用我們的示例數據，請將您的比較結果放在第三列。該公式將從列表 1 中的四個項目中找到三個匹配項。

請注意，項目 2 顯示為 #N/A。Excel 告訴我們匹配項不適用，即無法找到。

這是正確的，並且是有關缺失值的有用信息。但是，一些 Excel 用戶可能會認為存在一些數據問題或功能錯誤。

顯示未找到匹配項的不同指示是一種很好的做法。那可能只是一片空白。

為此，將 VLOOKUP 函數與 IFNA 函數結合起來，如下所示：

=IFNA(VLOOKUP(A1, B:B, 1, FALSE), “”)

IFNA 函數檢測到#N/A 錯誤並將輸出替換為空格（“”）。您還可以使用 ISNA 函數或使用 IF 函數進行附加邏輯。

處理其他錯誤

VLOOKUP 有時會在您的比較結果中產生其他錯誤。#REF ！Excel 中的錯誤是您的數據的真正問題。

它通常發生在您指定的範圍不正確時。我們的示例引用了整列，但您也可以使用垂直單元格範圍。

確保您所指的搜索範圍涵蓋了您要比較的所有值。

用於列比較的 VLOOKUP 替代方法

在 Excel 中比較兩列以查找匹配項時，有兩個主要的替代查找函數。

1.使用索引和匹配功能

您可以通過結合使用 INDEX 和 MATCH 函數來比較 Excel 中的兩列，而不是使用 VLOOKUP。

此方法提供了一種更靈活的數據查找方式，在處理非相鄰列或列索引可能更改時特別有用。

這是放入結果列的語法：

=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))

• Return_range：包含要返回的數據的單元格範圍。

• Lookup_value：您要在查找範圍內搜索的值。

• Lookup_range：要在其中查找查找值的單元格範圍。

使用前面示例中的相同數據，我們將 VLOOKUP 公式替換為如下所示：

=(索引(B:B, 匹配(A2, B:B, 0)))

MATCH函數返回查找值在查找範圍內的相對位置，INDEX函數返回返回範圍內的對應值。

相同數據的結果將與我們之前使用的 VLOOKUP 相同。此圖顯示了正在使用的功能：

您還可以用自定義消息替換 #N/A 錯誤。下面是使用 IFERROR 函數的示例。

=IFERROR(INDEX(B:B, MATCH(A2, B:B, 0)), “未找到”)

2. 在 Excel 中使用 XLOOKUP

對於使用 Excel 365 或 Excel 2019 的用戶，XLOOKUP 是 VLOOKUP 的替代邏輯測試，用於查找兩列中的共同值。

XLOOKUP 提供了幾個優勢。您可以使用它來水平和垂直搜索數據，處理不相鄰的列，並為錯誤指定自定義值。

XLOOKUP 的語法是：

=XLOOKUP(lookup_value, lookup_range, return_range, [if_not_found], [match_mode], [search_mode])

• lookup_value：您要在查找範圍內搜索的值。

• lookup_range：要在其中查找查找值的單元格範圍。

• return_range：包含要返回的數據的單元格範圍。

• 根據需要添加自定義錯誤值、匹配模式和搜索模式參數。

XLOOKUP 會在查找範圍內查找查找值，並從返回範圍中返回對應的值。

當您將兩個列表嵌入到多列數據集中時，該公式特別有用。在我們之前的示例中，列表位於第一列和第二列，但您的工作表可能包含更多數據。

這是一個示例，在電子表格的第一列和第三列中有兩個列表：

XLOOKUP 公式如下所示：

=XLOOKUP(A2,C:C,C:C,””)

此圖片顯示了 D 列中比較的結果。第一個值存在但第二個值缺失。

請注意，我沒有額外的錯誤公式，但缺失值顯示為空白。那是因為我使用的是第四個參數，它是一個自定義錯誤值。在本例中，它是一個空字符串。

作為獎勵，我將向您展示如何使用 XLOOKUP 進行跨行比較。如果您有兩個列表，分別位於第 1 行和第 2 行，則最簡單的公式用法如下所示：

=XLOOKUP(B1, 2:2, 2:2, “”)

此圖顯示了兩行的結果。

憑藉其改進的功能和靈活性，XLOOKUP 是 VLOOKUP 的絕佳替代品，用於比較 Excel 中的列表以確定是否存在匹配項。

提高 VLOOKUP 性能的五個技巧

優化 VLOOKUP 函數可以幫助您避免長時間等待並提高 Excel 工作表的整體響應能力。

您可以應用以下六個提示和技巧來提高 Excel 中 VLOOKUP 的性能。

1.限制你的查找範圍

我在示例中使用了整列以使其簡單。如果您正在處理大量數據，則應避免這樣做。使用完整的列會減慢 Excel 的計算過程。

相反，請嘗試使用您的數據所需的確切範圍（例如，A1:A100）。這減少了 VLOOKUP 函數需要評估的單元格數量。

2.使用絕對引用

在指定數據范圍時（例如從 B2 到 B5 的單元格），使用絕對引用。這可確保在跨多個單元格複製公式時公式一致且數據存在。

這是一個例子：

=VLOOKUP(A2, $B$2:$B$5, 1, FALSE)

3. 對數據進行排序

如果您知道查找列中的數據按升序排序，則可以將 VLOOKUP 與 TRUE 或 1 一起用於 'range_lookup' 參數。

這將導致 Excel 執行近似匹配，這比跨所有單元格的精確匹配更快。但是，使用此選項時要小心，因為不正確的排序可能會導致不正確的結果。

4.使用雙VLOOKUP

您可以使用兩個 VLOOKUP 函數來加快 Excel 中的搜索過程。

第一個 VLOOKUP 將通過將“col_index_num”設置為 1 並將“range_lookup”設置為 TRUE 來確定查找值是否存在。

如果它返回 TRUE，第二個 VLOOKUP 將檢索所需的值，並將“range_lookup”設置為 TRUE。

5.使用條件格式

您可以在 Excel 工作表中使用條件格式來突出顯示指定列中的匹配值或缺失值。您還可以將顏色應用於唯一值。這使您的數據更易於閱讀。

您將在主頁選項卡的樣式組中找到條件格式菜單。

VLOOKUP 的高級用途

配對樣本 T 檢驗用於比較兩個相關樣本的均值。該視頻展示了它們在 Excel 中的用法。

如果在分析過程中需要引用其他數據或變量，則可以使用 VLOOKUP 從另一個表或工作表中檢索必要的值。

例如，您可以使用 VLOOKUP 檢索數據集中每個配對觀察的人口統計信息或治療條件。

我們的最終決定

到目前為止，您對使用 VLOOKUP 有了深入的了解。這個強大的功能可以讓您快速識別兩個列表之間的差異和匹配值，使數據分析更加高效和準確。

您還看到了其他查找和引用函數的示例，例如 XLOOKUP、INDEX 和 MATCH。將這些元素添加到您的技能中將進一步增強您的數據分析能力。

不斷練習和完善您的 VLOOKUP 技能，您很快就會成為在 Excel 中比較列的專家，從而節省時間並提高您的工作效率。