跳到主要內容

Excel VLOOKUP函數

新增一名作者 最後修改時間:2023-06-01

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 根據表數組中行的順序僅返回它在表數組中找到的第一個匹配項。

最佳辦公生產力工具

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

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

產品描述


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

  • 在Word,Excel,PowerPoint中啟用選項卡式編輯和閱讀,發布者,Access,Visio和Project。
  • 在同一窗口的新選項卡中而不是在新窗口中打開並創建多個文檔。
  • 將您的工作效率提高 50%,每天為您減少數百次鼠標點擊!
Comments (1)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
thank you so much
There are no comments posted here yet
Leave your comments
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations