20+ VLOOKUP 示例,適合 Excel 初學者和進階使用者
VLOOKUP 函數是 Excel 中最受歡迎的函數之一。本教程將逐步介紹如何在 Excel 中使用 VLOOKUP 函數,並提供數十個基本和高級示例。
目錄:
- 2.1 精確匹配和近似匹配 VLOOKUP
- 2.2 區分大小寫 VLOOKUP
- 2.3 從右到左查找
- 2.4 VLOOKUP 第二個、第 n 個或最後一個匹配值
- 2.5 查找介於兩值或日期之間的數據
- 2.6 在 VLOOKUP 函數中使用通配符進行部分匹配
- 2.7 從其他工作表中 VLOOKUP 值
- 2.8 從其他工作簿中查找值
- 2.9 VLOOKUP 並返回空白或特定文字,而不是0 或 #N/A 錯誤值
下載 VLOOKUP 示例文件
VLOOKUP 函數介紹 – 語法和參數
在 Excel 中,VLOOKUP 函數對大多數 Excel 用戶來說是一個強大的工具,它允許您在資料區域的最左列查找一個值,並從您指定的列中返回同一行的匹配值,如下圖所示。
VLOOKUP 函數的語法:
參數:
"查找的值"(必需):您想要搜尋的值。它可以是數值(數字、日期或文字)或單元格引用。它必須位於表格區域的最左列。
"表格區域"(必需):查找的值所在的資料區域或表格,以及結果值所在的列。
"Col_index_num"(必填):包含返回值的列號。它從表格區域的最左列開始,編號為1。
"Range_lookup"(可選):一個邏輯值,用於確定此 VLOOKUP 函數是返回精確匹配還是近似匹配。
- "近似匹配" –1 / 能 /省略(默認):如果未找到完全匹配,公式將搜尋最接近的匹配值 - 小於查找的值的最大值。
- "精確匹配" –0 / FALSE:這用於搜尋與查找的值完全相等的值。如果找不到精確匹配,將返回錯誤值 #N/A。
函數備註:
- Vlookup 函數僅從左到右查找值。
- Vlookup 函數執行不區分大小寫的查找。
- 如果根據查找的值有多個匹配值,使用 Vlookup 函數時只會返回第一個匹配的值。
2.1.1 執行精確匹配的 VLOOKUP
通常,如果您使用 VLOOKUP 函數尋找精確匹配,只需將 FALSE 作為最後一個參數即可。
例如,要根據特定的ID號碼獲取相應的數學成績,請按以下步驟操作:
請將以下公式複製並粘貼到一個空白單元格中(此處我選擇 G2),然後按 "Enter" 鍵以獲取結果:
=VLOOKUP(F2,$A$2:$D$7,3,FALSE)
備註:在上述公式中,有四個參數:
- "F2" 是包含您要查找的值 C1005 的單元格;
- "A2:D7" 是您正在執行查找的表格區域;
- "3" 是返回匹配值的列號;(一旦函數找到 ID - C1005,它將轉到表格區域的第三列,並返回與 ID - C1005 相同行的值。)
- "FALSE" 指的是精確匹配。
VLOOKUP公式如何運作?
首先,它在表格的最左列中尋找 ID - C1005。它從上往下查找,並在單元格 A6 中找到該值。
一旦找到該值,它將向右轉到第三列並提取其中的值。
因此,您將獲得如下截圖所示的結果:
? | Kutools AI 助手:基於以下功能徹底改變數據分析方式:智能執行 | 生成代碼 | 創建自訂公式 | 分析數據並生成圖表 | 調用 Kutools 函數… |
熱門功能:查找、標記或標識重複值 | 刪除空行 | 合併列或單元格而不丟失數據 | 四捨五入 ... | |
高級 LOOKUP: 多條件 VLookup | 多值 VLookup | 多表查找 | 模糊查找 ... | |
高級下拉列表:快速創建下拉列表 | 依賴下拉列表 | 多選下拉列表 ... | |
列管理器: 添加指定數量的列 | 移動列 | 取消隱藏列 | 比較區域與列 ... | |
特色功能:網格聚焦 | 設計檢視 | 增強編輯欄 | 工作簿與工作表管理器 | 資源庫 | 日期提取器 | 合併資料 | 加密/解密儲存格 | 按列表發送電子郵件 | 超級篩選 | 特殊篩選(按粗體/斜體...) ... | |
頂級 15 工具集: 12 文本工具(添加文本,刪除特定字符,...) | 50+ 圖表 類型(甘特圖,...) | 40+ 實用 公式(基於生日計算年齡,...) | 19 插入工具(插入QR碼,從路徑插入圖片,...) | 12 轉換工具(金額轉大寫,匯率轉換,...) | 7 合併與分割工具(高級合併行,分割儲存格,...) | 更多功能... |
Kutools for Excel 擁有超過 300 種功能,確保您需要的功能僅需一鍵即可實現...
2.1.2 執行近似匹配的 VLOOKUP
近似匹配對於在資料區域之間搜尋值非常有用。如果找不到精確匹配,近似 VLOOKUP 將返回小於查找值的最大值。
例如,如果您有以下數據區域,而指定的訂單不在訂單列中,如何在 B 列中獲得其最接近的折扣?
步驟1:應用 VLOOKUP公式並填充至其他單元格
將以下公式複製並粘貼到您希望放置結果的單元格中,然後向下拖動填充柄以將此公式應用到其他單元格。
=VLOOKUP(D2,$A$2:$B$9,2,TRUE)
結果:
現在,您將根據給定的值獲得近似匹配,請參見截圖:
備註:
- 在上述公式中:
- "D2" 是您想要返回其相關資訊的值;
- "A2:B9" 是資料區域;
- "2" 表示返回匹配值的列號;
- "TRUE" 指的是近似匹配。
- 如果找不到完全匹配,近似匹配將返回小於您特定查找值的最大值。
- 要使用 VLOOKUP 函數獲取近似匹配值,您必須將資料區域的最左列按升序排序,否則會返回錯誤的結果。
2.2 在 Excel 中執行區分大小寫的 VLOOKUP
默認情況下,VLOOKUP 函數執行的是不區分大小寫的查找,這意味著它將小寫和大寫字符視為相同。有時,您可能需要在 Excel 中執行區分大小寫的查找,普通的 VLOOKUP 函數可能無法解決這個問題。在這種情況下,您可以使用其他函數,例如 INDEX 和 MATCH 結合 EXACT 函數,或者使用 LOOKUP 和 EXACT 函數。
例如,我有以下資料區域,其中 ID 列包含大寫或小寫的字串,現在,我想返回給定 ID 號碼的對應數學成績。
步驟1:應用任意一個公式並填充至其他單元格
請將以下任一公式複製並粘貼到您希望獲得結果的空白單元格中。然後,選擇公式單元格,向下拖動填充柄至您希望填充此公式的單元格。
公式1:粘貼公式後,請按 "Ctrl" + "Shift" + "Enter" 鍵。
=INDEX($C$2:$C$10,MATCH(TRUE,EXACT(F2,$A$2:$A$10),0))
公式2:粘貼公式後,請按 "Enter" 鍵。
=LOOKUP(2,1/EXACT(F2,$A$2:$A$10),$C$2:$C$10)
結果:
然後您將獲得所需的正確結果。請參閱截圖:
備註:
- 在上述公式中:
- "A2:A10" 是包含您要查找的特定值的列;
- "F2" 是查找的值;
- "C2:C10" 是返回結果的列。
- 如果找到多個匹配項,此公式將始終返回最後一個匹配項。
2.3 在 Excel 中從右到左查找值
VLOOKUP 函數總是在資料區域的最左列搜尋一個值,並從右側的列返回相應的值。如果您想執行反向 VLOOKUP,即在右側列中查找特定值並返回其在左側列中的相應值,如下圖所示:
2.4 在 Excel 中使用 VLOOKUP 查找第二個、第 n 個或最後一個匹配值
通常,使用 Vlookup 函數時,如果找到多個匹配值,則只會返回第一個匹配的記錄。在本節中,我將討論如何在資料區域中獲取第二個、第 n 個或最後一個匹配值。
2.4.1 VLOOKUP 並返回第2 個或第 n 個匹配的值
假設您在列 A 中有一個名稱列表,列 B 中是他們購買的培訓課程。現在,您希望找到指定客戶購買的第二或第 n 個培訓課程。請參閱截圖:
在這裡,VLOOKUP 函數可能無法直接解決此任務。但是,您可以使用 INDEX 函數作為替代方案。
步驟1:應用並填充公式至其他單元格
例如,要根據給定的條件獲取第二個匹配值,請將以下公式應用到一個空白單元格中,然後同時按下 "Ctrl" + "Shift" + "Enter" 鍵以獲得第一個結果。接著,選擇公式單元格,將填充柄向下拖動到您希望填充此公式的單元格。
=INDEX($B$2:$B$14,SMALL(IF(E2=$A$2:$A$14,ROW($A$2:$A$14)-ROW($A$2)+1),2))
結果:
現在,所有基於給定名稱的第二個匹配值已經一次顯示出來。
備註:在上述公式中:
- "A2:A14" 是包含所有查找值的區域;
- "B2:B14" 是您要返回的匹配值的區域;
- "E2" 是查找的值;
- "2" 表示您想要獲取的第二個匹配值,若要返回第三個匹配值,只需將其更改為3。
2.4.2 VLOOKUP 並返回最後匹配的值
如果您想要使用VLOOKUP並返回最後一個匹配的值,如下圖所示,本教程“VLOOKUP And Return The Last Matching Value”可能會幫助您詳細獲取最後一個匹配的值。
2.5 VLOOKUP 查找介於兩個給定值或日期之間的數據
有時候,您可能希望查找介於兩個值或日期之間的數據,並返回相應的結果,如下圖所示。在這種情況下,您可以使用 LOOKUP 函數,而不是使用 VLOOKUP 函數,並搭配排序的表格。
2.5.1 使用公式在兩個給定值或日期之間匹配 VLOOKUP 值
步驟1:整理數據並應用以下公式
您的原始表格應該是已排序的資料區域。然後,將以下公式複製或輸入到空白單元格中。接著,拖曳填充柄以將此公式填充到其他需要的單元格中。
=LOOKUP(2,1/($A$2:$A$6<=E2)/($B$2:$B$6>=E2),$C$2:$C$6)
結果:
現在,您將根據給定的值獲得所有匹配的記錄,請參見截圖:
備註:
- 在上述公式中:
- "A2:A6" 是較小值的區域;
- "B2:B6" 是較大數字的區域;
- "E2" 是您想要獲取其對應值的查找的值;
- "C2:C6" 是您想要返回對應值的列。
- 此公式也可用於提取介於兩個日期之間的匹配值,如下圖所示:
2.5.2 使用便捷功能在兩個給定值或日期之間進行 VLOOKUP 匹配
如果您覺得記住和理解上述公式很困難,我將為您介紹一個簡單的工具——「Kutools for Excel」。利用其「查找介於兩值之間的數據」功能,您可以輕鬆根據特定的值或日期返回相應的項目。
- 點擊「Kutools」>「高級 LOOKUP」>「查找介於兩值之間的數據」以啟用此功能。
- 然後根據您的數據從對話框中指定計算。
2.6 在 VLOOKUP 函數中使用萬用字元進行部分匹配
在 Excel 中,可以在 VLOOKUP 函數中使用萬用字元,這允許您對查找的值進行部分匹配。例如,您可以使用 VLOOKUP 根據查找值的一部分從表格中返回匹配的值。
假設,我有一個如下面截圖所示的數據區域,現在,我想根據姓(而不是全名)提取分數。如何在 Excel 中解決這個任務?
步驟1:應用公式並填充至其他單元格
請將以下公式複製或輸入到空白單元格中,然後拖動填充手柄以將此公式填充到其他需要的單元格中:
=VLOOKUP(E2&"*", $A$2:$C$11, 3, FALSE)
結果:
所有匹配的分數已返回,如下圖所示:
備註:在上述公式中:
- "E2&"*"" 是部分匹配的條件。這意味著您正在尋找任何以單元格 E2 中的值開頭的值。(通配符 "*" 表示任意一個字符或多個字符)
- "A2:C11" 是您要搜尋匹配值的資料區域;
- 「3」表示從資料區域的第3列返回匹配的值;
- "False" 表示精確匹配。(使用萬用字元時,必須將函數中的最後一個參數設為 FALSE 或0,以啟用 VLOOKUP 函數的精確匹配模式。)
- 要查找並返回以特定值結尾的匹配值,您應在該值前面放置通配符 "*"。請應用此公式:
-
=VLOOKUP("*"&E2, $A$2:$C$11, 3, FALSE)
- 要根據部分字串查找並返回匹配的值,無論指定的文字是在字串的開頭、結尾或中間,只需在文字或單元格引用的兩側加上兩個星號(*)。請使用此公式進行操作
-
=VLOOKUP("*"&D2&"*", $A$2:$B$11, 2, FALSE)
2.7 從其他工作表中查找 VLOOKUP 值
通常,您可能需要處理多個工作表,VLOOKUP 函數可以用來從另一個工作表查找數據,就像在一個工作表上一樣。
例如,您有兩個工作表,如下圖所示,若要查找並返回您指定工作表中的相應數據,請按照以下步驟操作:
步驟1:應用公式並填充至其他單元格
請在您希望獲得匹配項目的空白單元格中輸入或複製以下公式。然後,將填充柄向下拖動到您想要應用此公式的單元格。
=VLOOKUP(A2,'Data sheet'!$A$2:$C$15,3,0)
結果:
您將獲得所需的相應結果,請參閱截圖:
![]() | ![]() | ![]() |
備註:在上述公式中:
- "A2"代表查找的值;
- "'Data sheet'!A2:C15" 表示在名為 Data sheet 的工作表上搜尋區域 A2:C15 的值;(如果工作表名稱包含空格或標點符號,應將工作表名稱用單引號括起來,否則可以直接使用工作表名稱,例如:
=VLOOKUP(A2,Datasheet!$A$2:$C$15,3,0) 。 - "3" 是包含您要返回匹配數據的列號;
- "0" 表示執行精確匹配。
2.8 從另一個工作簿中 VLOOKUP 值
本節將介紹如何使用 VLOOKUP 函數從不同的工作簿中查找並返回匹配的值。
例如,假設您有兩個工作簿。第一個工作簿包含產品列表及其相應的成本。在第二個工作簿中,您希望提取每個產品項目的相應成本,如下圖所示。
步驟1:應用公式
打開您要使用的兩個工作簿,然後在第二個工作簿中您希望放置結果的單元格中應用以下公式。接著,拖動並複製此公式到其他需要的單元格中
=VLOOKUP(B2,'[Product list.xlsx]Sheet1'!$A$2:$B$6,2,0)
結果:
備註:
- 在上述公式中:
- "B2"代表查找的值;
- "'[Product list.xlsx]Sheet1'!A2:B6" 表示從工作簿 Product list 中名為 Sheet1 的工作表的區域 A2:B6進行搜尋;(工作簿的引用用方括號括起,整個工作簿 + 工作表用單引號括起。)
- "2" 是包含您想要返回的匹配數據的列號;
- "0" 表示返回精確匹配。
- 如果查找工作簿已關閉,則查找工作簿的完整檔案路徑將在公式中顯示,如以下截圖所示:
2.9 返回空白或特定文字,而不是0 或 #N/A 錯誤
通常,當您使用 VLOOKUP 函數返回對應值時,如果匹配的單元格為空白,則會返回0。而如果找不到匹配值,您將獲得 #N/A 錯誤值,如下圖所示。如果您希望顯示空白單元格或特定值而不是0 或 #N/A,這篇教學文章“VLOOKUP To Return Blank Or Specific Value Instead Of0 Or N/A”可能會對您有所幫助。
3.1 雙向查找(在行和列中使用 VLOOKUP)
有時候,您可能需要執行二維查找,也就是同時在行和列中搜尋一個值。例如,如果您有以下資料區域,您可能需要獲取特定產品在指定季度的值。本節將介紹一個在 Excel 中處理此任務的公式。
在 Excel 中,您可以結合使用 VLOOKUP 和 MATCH 函數來進行雙向查找。
請將以下公式應用到一個空白單元格中,然後按下 "Enter" 鍵以獲得結果。
=VLOOKUP(G2, $A$2:$E$7, MATCH(H1, $A$2:$E$2, 0), FALSE)
備註:在上述公式中:
- "G2" 是您要根據其獲取相應值的列中的查找的值;
- "A2:E7" 是您要查找的數據表;
- "H1" 是您要根據行中查找的值以獲得相應值的查找值;
- "A2:E2" 是列頁首的單元格;
- "FALSE" 表示獲取精確匹配。
3.2.1 使用公式根據兩個或多個條件匹配 VLOOKUP 值
在這種情況下,Excel中的LOOKUP或MATCH和INDEX函數可以幫助您快速輕鬆地完成這項工作。
例如,我有以下數據表,為了根據特定產品和大小返回匹配的價格,以下公式可能會對您有所幫助。
步驟1:應用以下任一公式
公式1:輸入以下公式並按下「Enter」。
=LOOKUP(2,1/($A$2:$A$12=G1)/($B$2:$B$12=G2),($D$2:$D$12))
公式2:輸入以下公式並按下 "Ctrl" + "Shift" + "Enter"。
=INDEX($D$2:$D$12,MATCH(1,($A$2:$A$12=G1)*($B$2:$B$12=G2),0))
結果:
備註:
- 在上述公式中:
- "A2:A12=G1" 表示在區域 A2:A12 中搜尋 G1 的條件;
- "B2:B12=G2" 表示在區域 B2:B12 中搜尋 G2 的條件;
- "D2:D12" 是您想要返回相應值的區域。
- 如果您有超過兩個條件,只需將其他條件加入到公式中,例如:
=LOOKUP(2,1/($A$2:$A$12=G1)/($B$2:$B$12=G2)/($C$2:$C$12=G3),($D$2:$D$12))
=INDEX($D$2:$D$12,MATCH(1,($A$2:$A$12=G1)*($B$2:$B$12=G2)*($C$2:$C$12=G3),0))
3.2.2 使用 Kutools for Excel 根據兩個或多個條件匹配 VLOOKUP 值
記住上述需要重複應用的複雜公式可能具有挑戰性,這會降低您的工作效率。然而,"Kutools for Excel" 提供了一個 "多條件查找" 功能,您只需點擊幾下即可根據一個或多個條件返回相應的結果。
- 點擊「Kutools」>「高級 LOOKUP」>「多條件查找」以啟用此功能。
- 然後根據您的數據從對話框中指定計算。
3.3 使用 VLOOKUP 根據一個或多個條件返回多個值
在 Excel 中,VLOOKUP 函數會搜尋一個值,並且只返回第一個匹配的值,即使找到多個對應值。有時候,您可能希望在一行、一列或單一單元格中返回所有對應的值。本節將討論如何在工作簿中使用一個或多個條件返回多個匹配值。
3.3.1 水平查找所有符合一個或多個條件的值
假設您有一個包含國家、城市和名稱的數據表,範圍為A1:C14,現在,您希望將所有來自 "US" 的名稱水平返回,如下圖所示。要解決此任務,請 點擊此處逐步獲得結果。
3.3.3 VLOOKUP 根據一個或多個條件將所有匹配的值合併至單一單元格
如果您想使用 Vlookup 並將多個匹配的值返回到一個單元格中,並使用指定的區分符,新的 TEXTJOIN 函數可以幫助您快速輕鬆地完成這項工作。
備註:
- TEXTJOIN 函數僅在 Excel2019、Excel365及更高版本中可用。
- 如果您使用 Excel2016及更早版本,請使用以下文章中的自訂函數:
- 在 Excel 中使用 Vlookup 返回多個值於一個單元格
3.4 VLOOKUP 返回匹配單元格的整行
在本節中,我將討論如何使用 VLOOKUP 函數檢索匹配值的整行。
步驟1:應用以下公式
請將以下公式複製或輸入到您希望輸出結果的空白單元格中,然後按下「Enter」鍵以獲得第一個值。接著,將公式單元格向右拖曳,直到顯示整行的數據。
=VLOOKUP($F$2,$A$1:$D$12,COLUMN(A1),FALSE)
結果:
現在,您可以看到整行數據已返回。請參閱截圖:
注意:在上述公式中:
- "F2" 是您想要根據查找的值返回整行的值;
- "A1:D12" 是您要搜尋待檢索值區域的資料區域;
- "A1" 表示您的資料區域中的第一列號碼;
- "FALSE" 表示精確查找。
提示:
- 如果根據匹配的值找到多行,並希望返回所有對應的行,請應用以下公式,然後同時按下 "Ctrl" + "Shift" + "Enter" 鍵以獲取第一個結果。接著將填充柄向右拖動。然後繼續將填充柄向下拖動穿過單元格以獲取所有匹配的行。請參閱下面的演示:
=IFERROR(INDEX(A:A,SMALL(IF(ISNUMBER(SEARCH($F$2,$A$2:$A$12)),ROW($A$2:$A$12),""),ROW()-1)),"")
3.5 Excel 中的嵌套 VLOOKUP
有時候,您可能需要查找在多個對照表中相互關聯的值。在這種情況下,您可以將多個 VLOOKUP 函數嵌套在一起以獲取最終值。
例如,我有一個工作表,其中包含兩個獨立的對照表。第一個對照表列出了所有產品名稱及其相應的銷售員。第二個對照表列出了每位銷售員的總銷售額。現在,如果您想查找每個產品的銷售額,如下圖所示,您可以嵌套 VLOOKUP 函數來完成此任務。
嵌套 VLOOKUP 函數的通用公式是:
備註:
- "查找的值" 是您要查找的值;
- "表格區域1", "表格區域2" 是包含查找的值和返回值的對照表;
- "col_index_num1" 表示第一個表格中用於查找中間公共數據的列號;
- "col_index_num2" 表示您希望返回匹配值的第二個表中的列號;
- "0" 用於精確匹配。
步驟1:應用並填入以下公式
請將以下公式應用到一個空白單元格中,然後向下拖動填充柄到您想要應用此公式的單元格。
=VLOOKUP(VLOOKUP(G3,$A$3:$B$7,2,0),$D$3:$E$7,2,0)
結果:
現在,您將獲得如下截圖所示的結果:
備註:在上述公式中:
- "G3" 包含您要查找的值;
- "A3:B7", "D3:E7" 是查找的值和返回值所在的表格區域;
- "2" 是區域中用來返回匹配值的列號。
- "0" 表示 VLOOKUP 精確匹配。
3.6 檢查值是否存在於另一列的列表數據中
VLOOKUP 函數也可以幫助您根據另一列中的數據列表檢查值是否存在。例如,如果您想在列 C 中查找名稱,並僅返回是或否以指示名稱是否在列 A 中找到,如下圖所示。
步驟1:應用以下公式
請將以下公式應用到一個空白單元格中,然後拖動填充柄向下至您想填充此公式的單元格。
=IF(ISNA(VLOOKUP(C2,$A$2:$A$10,1,FALSE)), "No", "Yes")
結果:
您將獲得所需的結果,請參見截圖:
備註:在上述公式中:
- "C2" 是您要查找的值;
- "A2:A10" 是要檢查待檢索值是否存在的區域列表;
- "FALSE" 表示獲取精確匹配。
3.7.1 VLOOKUP 並求和所有匹配的值在一行或多行中
假設您有一個產品列表,其中包含幾個月的銷售數據,如下圖所示。現在,您需要根據給定的產品對所有月份的所有訂單進行求和。
步驟1:應用以下公式
請將以下公式複製或輸入到一個空白單元格中,然後同時按下 "Ctrl" + "Shift" + "Enter" 鍵以獲得第一個結果。接著,向下拖動填充柄以將此公式複製到其他需要的單元格。
=SUM(VLOOKUP(H2, $A$2:$F$9, {2,3,4,5,6}, FALSE))
結果:
第一個匹配值所在行的所有值已經被求和,請參見截圖:
備註:在上述公式中:
- "H2" 是包含您要查找的值的單元格;
- "A2:F9" 是資料區域(不含列標題),其中包括查找的值和匹配的值;
- "{2,3,4,5,6}" 是用來計算區域總和的列號;
- 「否」表示精確匹配。
提示:如果您想在多行中求和所有匹配項,請使用以下公式:
-
=SUMPRODUCT(($A$2:$A$9=H2)*$B$2:$F$9)
3.7.2 VLOOKUP 並求和列或多列中所有匹配的值
如果您想要對特定月份的總值進行求和,如下圖所示。普通的 VLOOKUP 函數可能無法幫助您,此時,您應該將 SUM、INDEX 和 MATCH 函數結合使用來創建公式。
步驟1:應用以下公式
將以下公式應用到一個空白單元格中,然後向下拖動填充柄以將此公式複製到其他單元格。
=SUM(INDEX($B$2:$F$9,0,MATCH(H2,$B$1:$F$1,0)))
結果:
現在,基於特定月份在列中的第一個匹配值已經被求和,請參見截圖:
備註:在上述公式中:
- "H2" 是包含您要查找的值的單元格;
- "B1:F1" 是包含查找的值的列標題;
- "B2:F9" 是包含您想要求和的數字字符的資料區域。
提示:要使用 VLOOKUP 並求和多列中的所有匹配值,您應使用以下公式:
-
=SUMPRODUCT($B$2:$F$9*(($B$1:$F$1)=H2))
3.7.3 使用 Kutools for Excel 查找並求和首個匹配或所有匹配的值
可能上述公式對您來說難以記住,在這種情況下,我會推薦一個強大的功能——Kutools for Excel 的「查找求和」,使用此功能,您可以輕鬆地在行或列中 Vlookup 並求和第一個匹配或所有匹配的值。
- 點擊 "Kutools" > "Super LOOKUP" > "查找求和"以啟用此功能。
- 然後根據您的需求從對話框中指定計算。
3.7.4 VLOOKUP 和求和所有在行和列中匹配的值
如果您需要在匹配列和行時進行求和,例如,要獲取如下面截圖所示的三月份 Sweater產品的總值。
在這裡,您可以使用 SUMPRODCT 函數來完成此任務。
請將以下公式應用到單元格中,然後按下「Enter」鍵以獲得結果,請參閱截圖:
=SUMPRODUCT(($B$2:$F$9)*($B$1:$F$1=I2)*($A$2:$A$9=H2))
備註:在上述公式中:
- "B2:F9" 是包含您要求和的數字字符的資料區域;
- "B1:F1" 是包含您要根據查找值進行求和的列標題;
- "I2" 是您要查找的列標題中的查找的值;
- "A2:A9" 是包含您要求和的查找值的行標題;
- "H2" 是您在查找的行頁首中的查找的值。
3.8 使用 VLOOKUP 根據關鍵列合併兩個對照表
在日常工作中,分析數據時,您可能需要根據一個或多個關鍵列將所有必要的信息匯集到一個表中。為了完成這項任務,您可以使用 INDEX 和 MATCH 函數,而不是 VLOOKUP 函數。
3.8.1 使用 VLOOKUP 根據一個關鍵列合併兩個對照表
例如,您有兩個表格,第一個表格包含產品和名稱資料,第二個表格包含產品和訂單資料,現在,您希望通過匹配共同的產品列將這兩個表格合併為一個表格。
步驟1:應用以下公式
請在空白單元格中輸入以下公式。然後,將填充柄向下拖動到您想要應用此公式的單元格。
=INDEX($F$2:$F$8, MATCH($A2, $E$2:$E$8, 0))
結果:
現在,您將獲得一個合併的對照表,其中訂單列基於關鍵列數據加入到第一個對照表中。
備註:在上述公式中:
- "A2" 是您要查找的查找的值;
- "F2:F8" 是您想要返回匹配值的資料區域;
- "E2:E8" 是查找區域,其中包含查找的值。
3.8.2 使用 VLOOKUP 根據多個主鍵列合併兩個對照表
如果您要合併的兩個表格有多個關鍵列,請按照以下步驟根據這些共同列合併表格。
通用公式為:
備註:
- "lookup_table" 是包含查找數據和匹配記錄的資料區域;
- "lookup_value1" 是您正在尋找的第一個條件;
- "lookup_range1" 是包含第一個條件的數據列表;
- "lookup_value2" 是您正在尋找的第二個條件;
- "lookup_range2" 是包含第二條件的數據列表;
- "return_column_number" 表示您希望返回匹配值的查找表中的列號。
步驟1:應用以下公式
請將以下公式應用到您想要放置結果的空白單元格中,然後同時按下 "Ctrl" + "Shift" + "Enter" 鍵以獲取第一個匹配的值,請參見截圖:
=INDEX($E$2:$G$9, MATCH(1, ($A2=$E$2:$E$9) * ($B2=$F$2:$F$9), 0), 3)
步驟2:將公式填充到其他單元格
然後,選擇第一個公式單元格,並拖動填充柄以將此公式複製到其他需要的單元格:
3.9 VLOOKUP 匹配多個工作表中的值
您是否曾經需要在 Excel 中跨多個工作表執行 VLOOKUP?例如,如果您有三個工作表包含資料區域,並且您希望根據這些工作表中的條件檢索特定值,您可以按照逐步教程 VLOOKUP Values Across Multiple Worksheets來完成此任務。
4.1 VLOOKUP 匹配值並保留單元格顏色和字體格式
眾所周知,正常的 VLOOKUP 函數只能從另一個資料區域中檢索匹配的值。然而,有時您可能希望獲取對應的值以及單元格格式,例如填充顏色、字體顏色和字體樣式。在本節中,我們將討論如何在 Excel 中檢索匹配值同時保留源格式。
請按照以下步驟查找並返回其對應的值及單元格格式:
步驟1:將代碼1 複製到工作表代碼模組中
- 在包含您要 VLOOKUP 的數據的工作表中,右鍵點擊工作表標籤,然後從上下文選單中選擇「檢視代碼」。請參見截圖:
- 在打開的「Microsoft Visual Basic for Applications」視窗中,請將以下的 VBA代碼複製到代碼視窗中。
- VBA 程式碼1:使用 VLOOKUP 查找值並獲取單元格格式
-
Sub Worksheet_Change(ByVal Target As Range) 'Updateby Extendoffice Dim I As Long Dim xKeys As Long Dim xDicStr As String On Error Resume Next Application.ScreenUpdating = False xKeys = UBound(xDic.Keys) If xKeys >= 0 Then For I = 0 To UBound(xDic.Keys) xDicStr = xDic.Items(I) If xDicStr <> "" Then Range(xDic.Keys(I)).Interior.Color = _ Range(xDic.Items(I)).Interior.Color Range(xDic.Keys(I)).Font.FontStyle = _ Range(xDic.Items(I)).Font.FontStyle Range(xDic.Keys(I)).Font.Size = _ Range(xDic.Items(I)).Font.Size Range(xDic.Keys(I)).Font.Color = _ Range(xDic.Items(I)).Font.Color Range(xDic.Keys(I)).Font.Name = _ Range(xDic.Items(I)).Font.Name Range(xDic.Keys(I)).Font.Underline = _ Range(xDic.Items(I)).Font.Underline Else Range(xDic.Keys(I)).Interior.Color = xlNone End If Next Set xDic = Nothing End If Application.ScreenUpdating = True End Sub
步驟2:將代碼2 複製到模組視窗中
- 仍然在 "Microsoft Visual Basic for Applications" 視窗中,點擊 "插入" > "模組",然後將以下 VBA代碼2 複製到 "模組" 視窗中。
- VBA代碼2:使用 VLOOKUP 查找值並獲取單元格格式
-
Public xDic As New Dictionary Function LookupKeepFormat (ByRef FndValue, ByRef LookupRng As Range, ByRef xCol As Long) Dim xFindCell As Range On Error Resume Next Set xFindCell = LookupRng.Find(FndValue, , xlValues, xlWhole) If xFindCell Is Nothing Then LookupKeepFormat = "" xDic.Add Application.Caller.Address, "" Else LookupKeepFormat = xFindCell.Offset(0, xCol - 1).Value xDic.Add Application.Caller.Address, xFindCell.Offset(0, xCol - 1).Address End If End Function
步驟3:選擇 VBAproject 的選項
- 插入上述代碼後,請在 "Microsoft Visual Basic for Applications" 視窗中點擊 "工具" > "參考"。然後在 "參考 – VBAProject" 對話框中勾選 "Microsoft Scripting Runtime" 選項。請參閱截圖:
- 然後,點擊「確定」以關閉對話框,然後保存並關閉代碼窗口。
步驟4:輸入公式以獲得結果
- 現在,返回工作表,應用以下公式。然後,向下拖動填充柄以獲取所有結果及其格式。請參閱截圖:
=LookupKeepFormat(E2,$A$1:$C$10,3)
備註:在上述公式中:
- "E2" 是您要查找的值;
- "A1:C10" 是表格區域;
- "3" 是您要從中檢索匹配值的表格的列號。
4.2 保持 VLOOKUP 返回值的日期格式
當使用 VLOOKUP 函數查找並返回具有日期格式的值時,返回的結果可能顯示為數字。要保持返回結果的日期格式,您應該將 VLOOKUP 函數包含在 TEXT 函數中。
步驟1:應用以下公式
請將以下公式應用到一個空白單元格中。然後,拖動填充柄以將此公式複製到其他單元格。
=TEXT(VLOOKUP(E2,$A$2:$C$9,3,FALSE),"mm/dd/yyyy")
結果:
所有匹配的日期已返回,如下截圖所示:
備註:在上述公式中:
- "E2" 是查找的值;
- "A2:C9" 是查找區域;
- "3" 是您希望返回值的列號;
- "否" 表示獲得精確匹配;
- "mm/dd/yyyy" 是您想要保留的日期格式。
4.3 從 VLOOKUP 返回單元格批註
您是否曾經需要使用 VLOOKUP 在 Excel 中同時檢索匹配的單元格數據及其相關批註,如下圖所示?如果是這樣,下面提供的使用者自訂函數可以幫助您完成此任務。
步驟1:將代碼複製到模組中
- 按住 "ALT" + "F11" 鍵以開啟 "Microsoft Visual Basic for Applications" 視窗。
- 點擊「插入」>「模組」,然後在「模組」視窗中複製並粘貼以下代碼。
VBA代碼:Vlookup 並返回匹配值及批註:Function VlookupComment(LookVal As Variant, FTable As Range, FColumn As Long, FType As Long) As Variant 'Updateby Extendoffice Application.Volatile Dim xRet As Variant 'could be an error Dim xCell As Range xRet = Application.Match(LookVal, FTable.Columns(1), FType) If IsError(xRet) Then VlookupComment = "Not Found" Else Set xCell = FTable.Columns(FColumn).Cells(1)(xRet) VlookupComment = xCell.Value With Application.Caller If Not .Comment Is Nothing Then .Comment.Delete End If If Not xCell.Comment Is Nothing Then .AddComment xCell.Comment.Text End If End With End If End Function
- 然後儲存並關閉程式碼視窗。
步驟2:輸入公式以獲得結果
- 現在,輸入以下公式,然後拖曳填充柄以將此公式複製到其他單元格。它將同時返回匹配的值和批註,請參見截圖:
=vlookupcomment(D2,$A$2:$B$9,2,FALSE)
備註:在上述公式中:
- "D2" 是您想要返回其對應值的查找的值;
- "A2:B9" 是您想要使用的資料表;
- "2" 是包含您要返回的匹配值的列號;
- "FALSE" 表示獲取精確匹配。
4.4 VLOOKUP 數字以文字形式儲存
例如,我有一個資料區域,其中原始表格中的ID號碼是數字格式,而查找單元格中的ID號碼則以文字形式儲存,使用普通的VLOOKUP函數時可能會遇到#N/A錯誤。在這種情況下,為了獲取正確的信息,您可以在VLOOKUP函數中包裹TEXT和VALUE函數。以下是實現此目的的公式:
步驟1:應用並填入以下公式
請在空白單元格中應用以下公式,然後向下拖動填充柄以複製此公式。
=IFERROR(VLOOKUP(VALUE(D2),$A$2:$B$8,2,0),VLOOKUP(TEXT(D2,0),$A$2:$B$8,2,0))
結果:
現在,您將獲得正確的結果,如下圖所示:
備註:
- 在上述公式中:
- "D2" 是您要返回其對應值的查找的值;
- "A2:B8" 是您要使用的資料表;
- "2" 是包含您想要返回的匹配值的列號;
- "0" 表示獲得精確匹配。
- 如果您不確定數字和文字的位置,這個公式也能很好地運作。
最佳辦公效率工具
🤖 | Kutools AI 助手:基於智能執行方式革新數據分析:智能執行 | 生成代碼 | 創建自訂公式 | 分析數據並生成圖表 | 調用 Kutools 函數… |
熱門功能:查找、標記重複值或識別重複項 | 刪除空行 | 合併列或單元格而不丟失數據 | 四捨五入無需公式 ... | |
高級 LOOKUP: 多條件 VLookup | 多值 VLookup | 多表查找 | 模糊查找 .... | |
高級下拉列表:快速創建下拉列表 | 依賴下拉列表 | 多選下拉列表 .... | |
列管理器: 添加特定數量的列 | 移動列 | 切換隱藏列的可見狀態 | 比較區域和列 ... | |
特色功能:網格聚焦 | 設計檢視 | 增強編輯欄 | 工作簿與工作表管理器 | 資源庫(自動文本) | 日期提取器 | 合併資料 | 加密/解密儲存格 | 按列表發送電子郵件 | 超級篩選 | 特殊篩選(篩選粗體/斜體/刪除線...) ... | |
頂級 15 種工具集: 12 個文本工具(添加文本、刪除特定字符、...) | 50+ 圖表 類型(甘特圖、...) | 40+ 實用 公式(基於生日計算年齡、...) | 19 個插入工具(插入QR碼、根據路徑插入圖片、...) | 12 個轉換工具(金額轉大寫、匯率轉換、...) | 7 個合併與分割工具(高級合併行、分割儲存格、...) | ... 還有更多 |
使用 Kutools for Excel 提升您的 Excel 技巧,體驗前所未有的高效。 Kutools for Excel 提供超過 300 種高級功能來提高生產力並節省時間。 點擊這裡獲取您最需要的功能...
Office Tab 將標籤式界面帶到 Office,讓您的工作更加輕鬆
- 在 Word、Excel、PowerPoint、Publisher、Access、Visio 和 Project 中啟用標籤式編輯和閱讀。
- 在同一窗口的新標籤中打開和創建多個文檔,而不是在新窗口中。
- 將您的生產力提高 50%,每天為您減少數百次鼠標點擊!
目錄
- 1.介紹 VLOOKUP 函數
- 2. 基本 VLOOKUP 示例
- 2.1 精確和近似 Vlookup
- 完全匹配
- 近似匹配
- 2.2 區分大小寫的 Vlookup
- 2.3 從右到左查找
- 2.4 Vlookup 第二個、第 n 個或最後一個匹配的值
- 第二個或第 n 個匹配的值
- 最後匹配的值
- 2.5 查找介於兩值之間的數據
- 使用公式
- 使用一個方便的功能 - Kutools
- 2.6 部分匹配 Vlookup
- 2.7 從其他工作表查找
- 2.8 從另一個工作簿查找
- 2.9 修正 Vlookup 中的0 或 #N/A 錯誤值
- 3.進階 VLOOKUP 示例
- 3.1 雙向查找
- 3.2 根據更多條件進行 Vlookup
- 使用公式
- 透過使用智能功能 - Kutools
- 3.3 Vlookup 多個匹配值
- 水平返回值
- 垂直返回值
- 將返回值合併到一個單元格中
- 3.4 Vlookup 整行
- 3.5 嵌套 Vlookup
- 3.6 檢查值是否存在
- 3.7 查找求和
- 在行中
- 在列中
- 擁有強大的功能 - Kutools
- 同時在行和列中
- 3.8 使用 Vlookup 合併兩個對照表
- 按一個關鍵列
- 通過多個主鍵列
- 3.9 在多個工作表中進行 Vlookup
- 4. VLOOKUP 並保持單元格格式
- 4.1 保留顏色和字體格式
- 4.2 保持日期格式
- 4.3 保留單元格批註
- 4.4 數字以文字形式儲存
- 最佳辦公效率工具