Excel VLOOKUP 函數

Excel VLOOKUP 函數 是一項強大工具，可協助您在表格或範圍的第一欄中垂直搜尋指定值，並從同一列的其他欄位傳回對應值。雖然 VLOOKUP 功能極為實用，但初學者有時難以掌握。本教學旨在透過逐步說明各參數、實用範例及常見錯誤的解決方案 ，幫助您精通 VLOOKUP 函數。

• 引數的逐步說明
• 語法與引數
• VLOOKUP 範例
  完全符合與近似比對|多重條件的 Vlookup
• VLOOKUP 常見錯誤與解決方案
  #N/A 錯誤|#VALUE 錯誤|#REF 錯誤|不正確的值

VLOOKUP 範例.xlsx VLOOKUP 錯誤.xlsx

相關影片

引數的逐步說明

如上圖所示，VLOOKUP 函數用於根據指定的 ID 號碼查找對應的電子郵件。接下來，我將逐步拆解此範例中的每個參數，詳細說明 VLOOKUP 的使用方法。

步驟 1：啟動 VLOOKUP 函數

選取一個儲存格（本例為 H6）以輸出結果，接著在編輯欄中輸入以下內容，立即啟動 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])

• 查詢值（必要）：您要搜尋的值（可為實際數值或儲存格參照）。請注意，此值必須位於 table_array 的第一欄中。
• 表格陣列（必要）：包含查詢值欄位與傳回值欄位的儲存格範圍。
• 欄位索引（必要）：代表包含返回值之欄位編號的整數，編號從 table_array 最左側欄位開始，由 1 起算。
• 範圍比對（選用）：決定 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 是要合併的 Name 欄位，D6 是要合併的 Department 欄位的第一個部門。
   • 這兩個儲存格的值會以空格連接在一起。
3. 選取此結果儲存格，然後向下拖曳自動填滿控制點，即可將公式快速套用至同欄其他儲存格！
   
   

步驟 2：套用含指定條件的 VLOOKUP 函數

選取要輸出結果的儲存格（此處我選取 I7），在編輯欄中輸入下列公式，然後按下 Enter。

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

結果

常見 VLOOKUP 錯誤與解決方案

本節列出使用 VLOOKUP 時常見的錯誤，並提供相應的修正方法。

傳回 #N/A 錯誤

VLOOKUP 最常見的錯誤是 #N/A，表示 Excel 無法找到您要搜尋的值。以下是導致 VLOOKUP 傳回 #N/A 錯誤的幾個常見原因。

原因 1：查閱值不在 table_array 的第一欄中

Excel 的 VLOOKUP 函數有一項限制：僅能向右查詢，因此待檢索值所在的區域必須位於 table_array 的第一欄。

如下圖所示，我想根據指定的職務傳回姓名。此處查閱值（sales manager）位於 table_array 的第二欄，而返回值卻在查閱欄的左側，因此 VLOOKUP 函數會傳回 #N/A 錯誤。

解決方案

您可以選擇以下任一解決方案來修正此錯誤。

• 重新排列欄位
  
  您可以重新排列欄位，將查詢欄置於 table_array 的第一欄。
• 搭配使用 INDEX 與 MATCH 函數
  
  在此，我們運用 INDEX 與 MATCH 函數的組合，作為 VLOOKUP 的強大替代方案，有效解決此問題！

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

  
• 使用 XLOOKUP 函數（適用於 Excel 365、Excel 2021 及更新版本）
  

  =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 公式拖曳至其他儲存格時，table_array 未設為絕對參照

如下方截圖所示，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 函數（適用於 Excel 365、Excel 2021 及更新版本）：
  

  =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 將僅依據表格陣列中的列順序，傳回第一個找到的比對結果。

相關文章