Excel VLOOKUP 函數

Excel VLOOKUP 函數是一個強大的工具，幫助您通過在表格或區域的第一列中垂直匹配來查找指定的值，然後從同一行的另一列返回相應的值。儘管 VLOOKUP 非常有用，但對於初學者來說，有時可能難以掌握。本教程旨在通過逐步解釋參數、有用的示例以及您在使用 VLOOKUP 函數時可能遇到的常見錯誤的解決方案，幫助您掌握 VLOOKUP。

• 參數的逐步解釋
• 語法和參數
• VLOOKUP 示例
  精確匹配與近似匹配 | VLOOKUP 多條件查找
• VLOOKUP 常見錯誤及解決方案
  #N/A 錯誤 | #VALUE 錯誤 | #REF 錯誤 | 不正確的值

VLOOKUP 示例 .xlsx VLOOKUP 錯誤 .xlsx

相關影片

參數的逐步解釋

如上圖所示，VLOOKUP 函數用於根據給定的 ID 號碼查找電子郵件。現在，我將逐步詳細解釋如何在此示例中使用 VLOOKUP。

步驟1：啟動 VLOOKUP 函數

選擇一個單元格（此例中為 H6）以輸出結果，然後在編輯欄(F)中輸入以下內容以啟動 VLOOKUP 函數。

=VLOOKUP(

步驟2：指定查找的值

首先，在 VLOOKUP 函數中指定查找的值（即您要查找的內容）。在此，我引用包含某個 ID 號碼1005 的 G6 單元格。

=VLOOKUP(G6

步驟3：指定表格區域

接下來，指定包含您要查找的值和您想返回的值的單元格範圍。在此例中，我選擇範圍 B6:E12。公式現在顯示如下：

=VLOOKUP(G6,B6:E12

=VLOOKUP(G6,$B$6:$E$12

步驟4：指定要返回值的列

然後指定您要從中返回值的列。

在此示例中，由於我需要根據 ID 號碼返回電子郵件，因此我輸入數字4，告訴 VLOOKUP 從資料區域的第四列返回值。

=VLOOKUP(G6,B6:E12,4

步驟5：查找近似或精確匹配

最後，確定您是要查找近似匹配還是精確匹配。

• 要查找精確匹配，您需要將最後一個參數設為 FALSE。
• 要查找近似匹配，將最後一個參數設為 TRUE，或只留空。

在此示例中，我使用 FALSE進行精確匹配。公式現在看起來如下：

=VLOOKUP(G6,B6:E12,4,FALSE

按 Enter 鍵獲取結果

通過在上述示例中逐一解釋每個參數，VLOOKUP 函數的語法和參數現在更容易理解。

語法和參數

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

• Lookup_value（必需）：您要查找的值（實際值或單元格引用）。請記住，此值必須位於 table_array 的第一列。
• Table_array（必需）：包含查找值列和返回值列的單元格範圍。
• Col_index（必需）：表示包含返回值的列號的整數。它從 table_array 的最左列開始，編號為1。
• Range_lookup （可選）：一個邏輯值，決定您希望 VLOOKUP 查找近似匹配還是精確匹配。
  • 近似匹配 - 將此參數設為 TRUE、1 或留空。
    重要：要查找近似匹配，table_array 的第一列中的值必須按升序排序，以防 VLOOKUP 返回錯誤結果。
  • 精確匹配 - 將此參數設為 FALSE 或0。

示例

本節展示了一些示例，幫助您更全面地理解 VLOOKUP 函數。

示例1：VLOOKUP 中的精確匹配與近似匹配

如果您在使用 VLOOKUP 時對精確匹配和近似匹配感到困惑，本節可以幫助您釐清這種困惑。

VLOOKUP 中的精確匹配

在此示例中，我將根據範圍 E6:E8 中列出的分數查找相應的名稱，因此我在單元格 F6 中輸入以下公式，然後將自動填充手柄拖動到 F8。在此公式中，最後一個參數指定為 FALSE以執行精確匹配查找。

=VLOOKUP(E6,$B$6:$C$12,2,FALSE)

然而，由於分數98 不存在於資料區域的第一列中，VLOOKUP 返回 #N/A 錯誤結果。

VLOOKUP 中的近似匹配

仍然使用上述示例，如果您將最後一個參數更改為 TRUE，VLOOKUP 將執行近似匹配查找。如果未找到匹配項，它將找到小於查找值的下一個最大值並返回相應的結果。

=VLOOKUP(E6,$B$6:$C$12,2,TRUE)

由於分數98 不存在，VLOOKUP 找到小於98 的下一個最大值，即95，並返回分數95 的名稱作為最接近的結果。

示例2：使用 VLOOKUP 和多個條件

本節演示如何在 Excel 中使用 VLOOKUP 和多個條件。如下面的截圖所示，如果您嘗試根據提供的姓名（在單元格 H5 中）和部門（在單元格 H6 中）查找工資，請按照以下步驟完成。

步驟1：添加輔助列以連接查找列中的值

在此情況下，我們需要創建一個輔助列來連接姓名列和部門列中的值。

1. 在您的資料區域左側添加一個輔助列，並為此列添加標題。請參見截圖：
   
   
2. 在此輔助列中，選擇標題下的第一個單元格，在 編輯欄中輸入以下公式，然後按 Enter.
   

   =C6&" "&D6

   
   備註：在此公式中，我們使用 & 符號將兩列中的文本連接起來，生成一個文本。

   • C6 是 姓名 列中要連接的第一個名稱，D6 是 部門 列中要連接的第一個部門。
   • 這兩個單元格的值之間用空格連接。
3. 選擇此結果單元格，然後拖動 自動填充手柄以將此公式應用於同一列中的其他單元格。
   
   

步驟2：使用給定條件應用 VLOOKUP 函數

選擇一個您想輸出結果的單元格（此處我選擇 I7），在編輯欄中輸入以下公式，然後按Enter。

=VLOOKUP(I5& " "&I6,B6:F12,5,FALSE)

結果

常見 VLOOKUP 錯誤及解決方案

本節列出了您在使用 VLOOKUP 時可能遇到的常見錯誤，並提供了解決方案來修復它們。

返回 #N/A 錯誤

VLOOKUP 最常見的錯誤是 #N/A 錯誤，這意味著 Excel 找不到您要查找的值。以下是 VLOOKUP可能返回 #N/A 錯誤的一些原因。

原因1：查找的值不在表格區域的第一列

Excel VLOOKUP 的一個限制是它只允許您從左到右查找。因此，查找的值必須位於表格區域的第一列。

如下面的截圖所示，我想根據給定的職稱返回一個名稱。這裡查找的值（銷售經理）位於表格區域的第二列，而返回值位於查找列的左側，因此 VLOOKUP 返回 #N/A 錯誤。

解決方案

您可以應用以下任何解決方案來修復此錯誤。

• 重新排列列
  
  您可以重新排列列以將查找列放在表格區域的第一列。
• 一起使用 INDEX 和 MATCH 函數
  
  在此我們將 INDEX 和 MATCH 函數一起使用作為 VLOOKUP 的替代方案來解決此問題。

  =INDEX(B6:B12,MATCH(F6,C6:C12,0))

  
• 使用 XLOOKUP 函數（適用於 Excel365、Excel2021及更高版本）
  

  =XLOOKUP(F6,C6:C12,B6:B12)

原因2：查找的值在查找列中未找到（精確匹配）

VLOOKUP 返回 #N/A 錯誤的最常見原因之一是因為找不到您要查找的值。

如下面的示例所示，我們將根據 E6 中給定的分數98 查找名稱。然而，這個分數不存在於資料區域的第一列中，因此 VLOOKUP 返回 #N/A 錯誤結果。

解決方案

要修復此錯誤，您可以嘗試以下解決方案之一。

• 如果您希望 VLOOKUP 查找小於查找值的下一個最大值，請將最後一個參數從 FALSE（精確匹配）更改為 TRUE（近似匹配）。有關更多信息，請參見示例1：使用 VLOOKUP 的精確匹配與近似匹配。
• 為了避免更改最後一個參數並在查找值未找到時獲得提醒，您可以將 VLOOKUP 函數嵌入到 IFERROR 函數中：
  

  =IFERROR(VLOOKUP(E8,$B$6:$C$12,2,FALSE),"Not found")

原因3：查找的值小於查找列中的最小值（近似匹配）

如下面的截圖所示，您正在執行近似匹配查找。您要查找的值（此例中的 ID 號碼1001）小於查找列中的最小值1002，因此 VLOOKUP 返回 #N/A 錯誤。

解決方案

這裡有兩個解決方案供您選擇。

• 確保查找的值大於或等於查找列中的最小值。
• 如果您希望 Excel 提醒您查找的值未找到，只需將 VLOOKUP 函數嵌入到 IFERROR 函數中，如下所示：
  

  =IFERROR(VLOOKUP(G6,B6:E12,4,TRUE),"Not found")

原因4：數字格式為文本

如下面的截圖所示，此示例中的 #N/A 錯誤結果是由於查找單元格（G6）與原始表格的查找列（B6:B12）之間的數據類型不匹配。這裡 G6 中的值是一個數字，而範圍 B6:B12 中的值是格式為文本的數字。

解決方案

要解決此問題，您需要將查找的值轉換回數字。這裡有兩種方法供您選擇。

• 應用轉換為數字功能
  
  點擊您想要將文本轉換為數字的單元格，選擇此按鈕  在單元格旁邊，然後選擇 轉換為數字.
  
• 應用一個方便的工具來批量轉換文本和數字之間的轉換
  
  Kutools for Excel 的文字與數值之間的轉換功能幫助您輕鬆將一個範圍的單元格從文本轉換為數字，反之亦然。立即獲取30 天全功能免費試用！

原因5：拖動 VLOOKUP公式到其他單元格時，表格區域不一致

如下面的截圖所示，E6 和 E7 中有兩個查找值。在 F6 中獲得第一個結果後，將 VLOOKUP公式從 F6 拖動到 F7，返回了 #N/A 錯誤結果。這是因為單元格引用（B6:C12）默認為相對引用，並在您向下移動行時進行調整。表格區域已下移至 B7:C13，不再包含查找分數73。

解決方案

您需要鎖定表格區域，通過在單元格引用中的行和列前添加 $ 符號來保持其不變。要了解有關 Excel 中絕對引用的更多信息，請查看本教程：Excel 絕對引用（如何製作和使用）。

返回 #VALUE 錯誤

以下條件可能導致 VLOOKUP 返回 #VALUE 錯誤結果。

原因1：查找的值超過255 個字符

如下面的截圖所示，單元格 H4 中的查找值超過255 個字符，因此 VLOOKUP 返回 #VALUE 錯誤結果。

解決方案

要解決此限制，您可以應用其他可以處理更長字符串的查找函數。嘗試以下公式之一。

• INDEX 和 MATCH:
  

  =INDEX(E5:E11, MATCH(TRUE, INDEX(B5:B11=H4, 0), 0))

  
• XLOOKUP 函數 （適用於 Excel365、Excel2021及更高版本）：
  

  =XLOOKUP(H4,B5:B11,E5:E11)

原因2：col_index參數小於1

列索引指定表格區域中包含您要返回的值的列號。此參數必須是對應於表格區域中有效列的正數。

如果您輸入的列索引小於1（即零或負數），VLOOKUP 將無法在表格區域中定位該列。

解決方案

要解決此問題，請確保 VLOOKUP公式中的列索引參數是對應於表格區域中有效列的正數。

返回 #REF 錯誤

本節列出了一個 VLOOKUP 返回 #REF 錯誤的原因，並提供了解決此問題的方案。

原因：col_index參數大於列數

如下面的截圖所示，表格區域只有4 列。然而，您在 VLOOKUP公式中指定的列索引是5，這大於表格區域中的列數。因此，VLOOKUP 將無法定位該列，最終返回 #REF 錯誤。

解決方案

• 指定正確的列號
  確保 VLOOKUP公式中的列索引參數是對應於表格區域中有效列的數字。
• 根據指定的列標題自動獲取列號
  如果表格包含許多列，您可能很難確定正確的列索引號。在此，您可以將 MATCH 函數嵌入到 VLOOKUP 函數中，以根據某個列標題找到列的位置。

  =VLOOKUP(G6,B6:E12,MATCH("Email",B5:E5,0),FALSE)

  備註：在上述公式中，MATCH("Email",B5:E5,0) 函數用於獲取日期範圍 B6:E12 中 "Email" 列的列號。這裡的結果是4，作為 VLOOKUP 函數中的 col_index 使用。

返回不正確的值

如果您發現 VLOOKUP 沒有返回正確的結果，可能是由以下原因造成的

原因1：查找列未按升序排序

如果您將最後一個參數設為 TRUE（或留空）以進行近似匹配，而查找列未按升序排序，則結果值可能不正確。

解決方案

按升序排序查找列可以幫助您解決此問題。要做到這一點，請按照以下步驟操作：

1. 選擇查找列中的數據單元格，轉到數據選項卡，單擊排序和篩選組中的 從小到大排序。
2. 在排序警告對話框中，選擇展開選擇選項，然後單擊確定。

原因2：插入或刪除了一列

如下面的截圖所示，我最初想要返回的值位於表格區域的第四列，因此我將 col_index 號指定為4。由於插入了一個新列，結果列成為表格區域的第五列，導致 VLOOKUP 從錯誤的列返回結果。

解決方案

這裡有兩個解決方案供您選擇。

• 您可以手動更改列索引號以匹配返回列的位置。這裡的公式應更改為：

  =VLOOKUP(H6,B6:F12,5,FALSE)

• 如果您始終希望從某個特定列返回結果，例如此示例中的 Email 列。以下公式可以幫助根據給定的列標題自動匹配列索引，無論是否從表格區域中插入或刪除列。

  =VLOOKUP(H6,B6:F12,MATCH("Email",B5:E5,0),FALSE)

其他函數備註

• VLOOKUP 僅從左到右查找值。
  查找的值位於最左列，結果值應位於查找列右側的任何列中。
• 如果您將最後一個參數留空，VLOOKUP 默認使用近似匹配。
• VLOOKUP 執行不區分大小寫的查找。
• 對於多個匹配項，VLOOKUP 僅返回它在表格區域中找到的第一個匹配項，基於表格區域中行的順序。

相關文章