跳到主要內容

Excel VLOOKUP函數

Excel VLOOKUP 函數 是一個功能強大的工具,可幫助您通過垂直匹配表格或範圍的第一列來查找指定值,然後從同一行的另一列返回相應的值。 儘管 VLOOKUP 非常有用,但對於初學者來說有時很難掌握。 本教程旨在通過提供幫助您掌握 VLOOKUP 論據的逐步解釋, 有用的例子常見錯誤的解決方法 你在使用 VLOOKUP 函數時可能會遇到。


相關視頻


論證的逐步解釋

如上圖所示,VLOOKUP 函數用於根據給定的 ID 號查找電子郵件。 我現在將通過逐步分解每個參數來詳細說明如何在此示例中使用 VLOOKUP。

第一步:啟動VLOOKUP函數

選擇一個單元格(在本例中為 H6)輸出結果,然後通過在其中鍵入以下內容來啟動 VLOOKUP 函數 配方欄.

=VLOOKUP(
第 2 步:指定查找值

首先,在 VLOOKUP 函數中指定查找值(這就是您要查找的值)。 在這裡,我引用了包含特定 ID 號 6 的單元格 G1005。

=VLOOKUP(G6

備註: 查找值必須在數據范圍的第一列中。
第三步:指定表數組

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

=VLOOKUP(G6,B6:E12

備註:如果你想複製VLOOKUP函數在同一列中查找多個值並得到不同的結果,你需要通過添加美元符號來使用絕對引用,就像這樣:
=VLOOKUP(G6,$B$6:$E$12
第 4 步:指定要從中返回值的列

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

在此示例中,由於我需要根據 ID 號返回電子郵件,因此我在這裡輸入數字 4 以告訴 VLOOKUP 從數據范圍的第四列返回一個值。

=VLOOKUP(G6,B6:E12,4

第 5 步:查找近似或精確匹配

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

  • 要找到一個 完全匹配,你需要使用 作為最後一個論點。
  • 要找到一個 近似匹配使用 TRUE 作為最後一個參數,或者將其留空。

在此示例中,我使用 FALSE 進行精確匹配。 公式現在看起來像這樣:

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

按回車鍵得到結果

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


語法和參數

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

  • 查找值 (必需):您要查找的值(實際值或單元格引用)。 請記住,此值必須位於 table_array 的第一列中。
  • 表格數組 (必填):單元格範圍包含查找值的列和返回值的列。
  • 列索引 (必需):整數表示包含返回值的列號。 它從 table_array 最左邊的列的數字 1 開始。
  • 範圍查找 (可選):一個邏輯值,用於確定您希望 VLOOKUP 查找近似匹配還是精確匹配。
    • 近似匹配 - 將此參數設置為 TRUE, 1 或離開它 空白.
      重要:要找到近似匹配,table_array 的第一列中的值必須按升序排序,以防 VLOOKUP 返回錯誤結果。
    • 完全符合 - 將此參數設置為 or 0.

包機成本結構範例

本節通過一些示例來幫助您更全面地了解VLOOKUP函數。

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

如果您在使用 VLOOKUP 時對完全匹配和近似匹配感到困惑,本節可以幫助您消除這種困惑。

VLOOKUP 中的精確匹配

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

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

但是,由於數據范圍的第一列中不存在分數 98,因此 VLOOKUP 返回 #N/A 錯誤結果。

備註:這裡我在 VLOOKUP 函數中鎖定了表數組($B$6:$C$12),以便快速引用一個 一貫 針對多個查找值的數據集。
VLOOKUP 中的近似匹配

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

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

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

筆記:
  • 在這種近似匹配的情況下,table_array 第一列中的值必須按升序排序。 否則,VLOOKUP 可能不會返回正確的值。
  • 在這裡,我在 VLOOKUP 函數中鎖定了表數組 ($B$6:$C$12),以便針對多個查找值快速引用一組一致的數據。

示例 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)
結果

筆記:
  • 輔助列必須用作數據范圍的第一列。
  • 現在工資列是數據范圍的第五列,所以我們使用數字 5 作為公式中的列索引。
  • 我們需要加入標準 I5I6 (I5& " "&I6) 與輔助列相同的方式,並使用連接值作為 Lookup_Array中 公式中的參數。
  • 您也可以將這兩個條件直接放在 lookup_value 參數中,並用空格分隔它們(如果條件是文本,請不要忘記用雙引號將它們括起來)。
    =VLOOKUP("Albee IT",B6:F12,5,FALSE)
  • 更好的選擇 - 在幾秒鐘內使用多個條件進行查找
    多條件查找 的特點 Excel的Kutools 可以幫助您輕鬆地在幾秒鐘內使用多個條件進行查找。 立即獲得 30 天的全功能免費試用!

VLOOKUP常見錯誤及解決方法

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

  常見 VLOOKUP 錯誤概述:
          
         原因一:查找值不在第一列  
     原因二:未找到查找值  
  ------  原因三:查找值小於最小值  
     原因 4:數字被格式化為文本  
       原因 5:Table_array 不是常量  
         
  ------  原因 1:查找值超過 255 個字符  
   原因二:col_index小於2  
         
  ------  原因一:col_index大於列數  
   
         
  ------  原因一:查找列未按升序排序  
   原因2:插入或刪除了列  
         

#N/A 返回錯誤

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

原因一:查找值不在table_array的第一列

Excel VLOOKUP 的限制之一是它只允許您從左向右查看。 因此,查找值必須在 table_array 的第一列中。

如下圖所示,我想根據給定的職位返回一個名字。 這裡的查找值(銷售經理) 在 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)

原因二:在查找列中沒有找到查找值(精確匹配)

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

如下例所示,我們將根據 E98 中給定的 6 分來查找名稱。 但是,該分數在數據范圍的第一列中不存在,因此 VLOOKUP 返回 #N/A 錯誤結果。

解决方案

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

  • 如果要 VLOOKUP 搜索小於查找值的下一個最大值,請更改最後一個參數 (完全匹配)到 TRUE (近似匹配)。 欲了解更多信息,請參閱 示例 1:使用 VLOOKUP 的精確匹配與近似匹配.
  • 為避免更改最後一個參數並在未找到查找值時獲得提醒,您可以將 VLOOKUP 函數包含在 IFERROR 函數中:
    =IFERROR(VLOOKUP(E8,$B$6:$C$12,2,FALSE),"Not found")

原因三:查找值小於查找列中的最小值(近似匹配)

如下面的屏幕截圖所示,您正在執行近似匹配查找。 您要查找的值(在本例中為 ID 號 1001)小於查找列中的最小值 1002,因此,VLOOKUP 返回 #N/A 錯誤。

解决方案

這裡有兩種解決方案供您使用。

  • 確保查找值大於或等於查找列中的最小值。
  • 如果想讓Excel提示找不到查找值,只需要在IFERROR函數中嵌套VLOOKUP函數,如下:
    =IFERROR(VLOOKUP(G6,B6:E12,4,TRUE),"Not found")

原因 4:數字被格式化為文本

正如您在下面的屏幕截圖中看到的,此示例中的 #N/A 錯誤結果是由於原始表的查找單元格 (G6) 和查找列 (B6:B12) 之間的數據類型不匹配造成的。 這裡 G6 中的值是一個數字,B6:B12 範圍內的值是格式化為文本的數字。

尖端:如果將數字轉換為文本,單元格左上角會顯示一個綠色小三角形。

解决方案

要解決這個問題,您需要將查找值轉換回數字。 這裡有兩種方法供您使用。

  • 應用轉換為數字功能
    單擊要將文本轉換為數字的單元格,選擇此按鈕  在單元格旁邊,然後選擇 轉換成數字.
  • 應用一個方便的工具在文本和數字之間批量轉換
    在文字和數字之間轉換 的特點 Excel的Kutools 幫助您輕鬆地將一系列單元格從文本轉換為數字,反之亦然。 立即獲得 30 天的全功能免費試用!

原因五:拖拽VLOOKUP公式到其他單元格時table_array不是常量

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

解決方案

您需要通過添加一個來鎖定表數組以使其保持不變 $ 在單元格引用中的行和列之前簽名。 要了解有關 Excel 中絕對引用的更多信息,請查看本教程: Excel絕對參考(製作及使用方法).

#VALUE 返回錯誤

以下情況可能會導致 VLOOKUP 返回#VALUE 錯誤結果。

原因一:查找值超過1個字符

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

解决方案

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

  • 索引和匹配:
    =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("郵箱",B5:E5, 0) 函數用於獲取“電子郵件"日期範圍 B6:E12 中的列。此處結果為 4,用作 VLOOKUP 函數中的 col_index。

返回的值不正確

如果發現VLOOKUP返回的結果不正確,可能是以下原因造成的

原因一:查找列未按升序排序

如果您將最後一個參數設置為 TRUE 留空) 對於近似匹配,並且查找列未按升序排序,則結果值可能不正確。

解決方案

按升序對查找列進行排序可以幫助您解決這個問題。 為此,請按照以下步驟操作:

  1. 選擇查找列中的數據單元格,轉到 數據 標籤,點擊 最小到最大排序 ,在 排序和過濾 組。
  2. 排序警告 對話框中,選擇 擴大選擇 選項,然後單擊 OK.

原因2:插入或刪除了列

如下圖所示,我原本要返回的值在表數組的第四列,所以我指定col_index為4,因為插入了新的一列,結果列變成了表的第五列數組,導致 VLOOKUP 從錯誤的列返回結果。

解决方案

這裡有兩種解決方案供您使用。

  • 您可以手動更改列索引號以匹配返回列的位置。 這裡的公式應該改為:
    =VLOOKUP(H6,B6:F12,5,FALSE)
  • 如果你總是想從一個 certian 列返回結果,比如本例中的 Email 列。 下面的公式可以幫助根據給定的列標題自動匹配列索引,而不管列是從表數組中插入還是刪除。
    =VLOOKUP(H6,B6:F12,MATCH("Email",B5:E5,0),FALSE)

其他功能說明

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

最佳辦公生產力工具

熱門特色: 尋找、突出顯示或識別重複項   |  刪除空白行   |  合併列或儲存格而不遺失數據   |   沒有公式的回合 ...
超級查詢: 多條件VLookup    多值VLookup  |   跨多個工作表的 VLookup   |   模糊查詢 ....
高級下拉列表: 快速建立下拉列表   |  依賴下拉列表   |  多選下拉列表 ....
欄目經理: 新增特定數量的列  |  移動列  |  切換隱藏列的可見性狀態  |  比較範圍和列 ...
特色功能: 網格焦點   |  設計圖   |   大方程式酒吧    工作簿和工作表管理器   |  資源庫 (自動文字)   |  日期選擇器   |  合併工作表   |  加密/解密單元格    按清單發送電子郵件   |  超級濾鏡   |   特殊過濾器 (過濾粗體/斜體/刪除線...)...
前 15 個工具集12 文本 工具 (添加文本, 刪除字符,...)   |   50+ 圖表 類型 (甘特圖,...)   |   40+ 實用 公式 (根據生日計算年齡,...)   |   19 插入 工具 (插入二維碼, 從路徑插入圖片,...)   |   12 轉化 工具 (數字到單詞, 貨幣兌換,...)   |   7 合併與拆分 工具 (高級合併行, 分裂細胞,...)   |   ... 和更多

使用 Kutools for Excel 增強您的 Excel 技能,體驗前所未有的效率。 Kutools for Excel 提供了 300 多種進階功能來提高生產力並節省時間。  點擊此處獲取您最需要的功能...

kte選項卡201905


Office選項卡為Office帶來了選項卡式界面,使您的工作更加輕鬆

  • 在Word,Excel,PowerPoint中啟用選項卡式編輯和閱讀,發布者,Access,Visio和Project。
  • 在同一窗口的新選項卡中而不是在新窗口中打開並創建多個文檔。
  • 將您的工作效率提高 50%,每天為您減少數百次鼠標點擊!