Skip to main content

20+ VLOOKUP 示例,適合 Excel 初學者和進階使用者

Author: Xiaoyang Last Modified: 2025-05-15

VLOOKUP 函數是 Excel 中最受歡迎的函數之一。本教程將逐步介紹如何在 Excel 中使用 VLOOKUP 函數,並提供數十個基本和高級示例。


VLOOKUP 函數介紹 – 語法和參數

在 Excel 中,VLOOKUP 函數對大多數 Excel 用戶來說是一個強大的工具,它允許您在資料區域的最左列查找一個值,並從您指定的列中返回同一行的匹配值,如下圖所示。
Syntax and Arguments of vlookup function

VLOOKUP 函數的語法:

=VLOOKUP (查找的值, 表格區域, col_index_num, [range_lookup])

參數:

"查找的值"(必需):您想要搜尋的值。它可以是數值(數字、日期或文字)或單元格引用。它必須位於表格區域的最左列。 

"表格區域"(必需):查找的值所在的資料區域或表格,以及結果值所在的列。

"Col_index_num"(必填):包含返回值的列號。它從表格區域的最左列開始,編號為1。

"Range_lookup"(可選):一個邏輯值,用於確定此 VLOOKUP 函數是返回精確匹配還是近似匹配。

  • "近似匹配" –1 / 能 /省略(默認):如果未找到完全匹配,公式將搜尋最接近的匹配值 - 小於查找的值的最大值。
  • "精確匹配" –0 / FALSE:這用於搜尋與查找的值完全相等的值。如果找不到精確匹配,將返回錯誤值 #N/A。

函數備註

  • Vlookup 函數僅從左到右查找值。
  • Vlookup 函數執行不區分大小寫的查找。
  • 如果根據查找的值有多個匹配值,使用 Vlookup 函數時只會返回第一個匹配的值。

基本 VLOOKUP 示例

在本節中,我們將討論一些您經常使用的 Vlookup公式。

2.1 精確匹配和近似匹配 VLOOKUP

 2.1.1 執行精確匹配的 VLOOKUP

通常,如果您使用 VLOOKUP 函數尋找精確匹配,只需將 FALSE 作為最後一個參數即可。

例如,要根據特定的ID號碼獲取相應的數學成績,請按以下步驟操作:
 sample data

請將以下公式複製並粘貼到一個空白單元格中(此處我選擇 G2),然後按 "Enter" 鍵以獲取結果:

=VLOOKUP(F2,$A$2:$D$7,3,FALSE)

 apply the vlookup formula

備註:在上述公式中,有四個參數:

  • "F2" 是包含您要查找的值 C1005 的單元格;
  • "A2:D7" 是您正在執行查找的表格區域;
  • "3" 是返回匹配值的列號;(一旦函數找到 ID - C1005,它將轉到表格區域的第三列,並返回與 ID - C1005 相同行的值。)
  • "FALSE" 指的是精確匹配。

VLOOKUP公式如何運作?

首先,它在表格的最左列中尋找 ID - C1005。它從上往下查找,並在單元格 A6 中找到該值。
  It goes from top to bottom and finds the value in specific cell

一旦找到該值,它將向右轉到第三列並提取其中的值。
it goes to the right in the third column and extracts the value in it

因此,您將獲得如下截圖所示的結果:
get the result

注意:如果在最左列中找不到查找的值,則返回 #N/A 錯誤。
? Kutools AI 助手:基於以下功能徹底改變數據分析方式:智能執行   |  生成代碼  |  創建自訂公式  |  分析數據並生成圖表  |  調用 Kutools 函數
熱門功能查找、標記或標識重複值   |  刪除空行   |  合併列或單元格而不丟失數據   |   四捨五入 ...
高級 LOOKUP多條件 VLookup  |   多值 VLookup  |   多表查找   |   模糊查找 ...
高級下拉列表快速創建下拉列表   |  依賴下拉列表   |  多選下拉列表 ...
列管理器添加指定數量的列  |  移動列   |  取消隱藏列  |  比較區域與列 ...
特色功能網格聚焦   |  設計檢視   |   增強編輯欄   |  工作簿與工作表管理器  |  資源庫   |  日期提取器  |  合併資料   |  加密/解密儲存格    按列表發送電子郵件   |  超級篩選   |   特殊篩選(按粗體/斜體...) ...
頂級 15 工具集12 文本工具添加文本刪除特定字符,...)   |   50+ 圖表 類型甘特圖,...)   |   40+ 實用 公式基於生日計算年齡,...)   |   19 插入工具插入QR碼從路徑插入圖片,...)   |   12 轉換工具金額轉大寫匯率轉換,...)   |   7 合併與分割工具高級合併行分割儲存格,...)   |   更多功能...

Kutools for Excel 擁有超過 300 種功能確保您需要的功能僅需一鍵即可實現...

 
 2.1.2 執行近似匹配的 VLOOKUP

近似匹配對於在資料區域之間搜尋值非常有用。如果找不到精確匹配,近似 VLOOKUP 將返回小於查找值的最大值。

例如,如果您有以下數據區域,而指定的訂單不在訂單列中,如何在 B 列中獲得其最接近的折扣?
Do an approximate match VLOOKUP

步驟1:應用 VLOOKUP公式並填充至其他單元格

將以下公式複製並粘貼到您希望放置結果的單元格中,然後向下拖動填充柄以將此公式應用到其他單元格。

=VLOOKUP(D2,$A$2:$B$9,2,TRUE)

結果:

現在,您將根據給定的值獲得近似匹配,請參見截圖:
Apply the VLOOKUP formula and fill it to other cells

備註:

  • 在上述公式中:
    • "D2" 是您想要返回其相關資訊的值;
    • "A2:B9" 是資料區域;
    • "2" 表示返回匹配值的列號;
    • "TRUE" 指的是近似匹配。
  • 如果找不到完全匹配,近似匹配將返回小於您特定查找值的最大值。
  • 要使用 VLOOKUP 函數獲取近似匹配值,您必須將資料區域的最左列按升序排序,否則會返回錯誤的結果。

2.2 在 Excel 中執行區分大小寫的 VLOOKUP

默認情況下,VLOOKUP 函數執行的是不區分大小寫的查找,這意味著它將小寫和大寫字符視為相同。有時,您可能需要在 Excel 中執行區分大小寫的查找,普通的 VLOOKUP 函數可能無法解決這個問題。在這種情況下,您可以使用其他函數,例如 INDEX 和 MATCH 結合 EXACT 函數,或者使用 LOOKUP 和 EXACT 函數。

例如,我有以下資料區域,其中 ID 列包含大寫或小寫的字串,現在,我想返回給定 ID 號碼的對應數學成績。
Do a case sensitive VLOOKUP

步驟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)

結果:

然後您將獲得所需的正確結果。請參閱截圖:
Apply any one formula and fill it to other cells

備註:

  • 在上述公式中:
    • "A2:A10" 是包含您要查找的特定值的列;
    • "F2" 是查找的值;
    • "C2:C10" 是返回結果的列。
  • 如果找到多個匹配項,此公式將始終返回最後一個匹配項。

2.3 在 Excel 中從右到左查找值

VLOOKUP 函數總是在資料區域的最左列搜尋一個值,並從右側的列返回相應的值。如果您想執行反向 VLOOKUP,即在右側列中查找特定值並返回其在左側列中的相應值,如下圖所示:

點擊查看此任務的詳細步驟...

VLOOKUP values from right to left


2.4 在 Excel 中使用 VLOOKUP 查找第二個、第 n 個或最後一個匹配值

通常,使用 Vlookup 函數時,如果找到多個匹配值,則只會返回第一個匹配的記錄。在本節中,我將討論如何在資料區域中獲取第二個、第 n 個或最後一個匹配值。

 2.4.1 VLOOKUP 並返回第2 個或第 n 個匹配的值

假設您在列 A 中有一個名稱列表,列 B 中是他們購買的培訓課程。現在,您希望找到指定客戶購買的第二或第 n 個培訓課程。請參閱截圖:
VLOOKUP and return the second or nth matching value

在這裡,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))

結果:

現在,所有基於給定名稱的第二個匹配值已經一次顯示出來。
Apply and fill the formula to other cells

備註:在上述公式中:

  • "A2:A14" 是包含所有查找值的區域;
  • "B2:B14" 是您要返回的匹配值的區域;
  • "E2" 是查找的值;
  • "2" 表示您想要獲取的第二個匹配值,若要返回第三個匹配值,只需將其更改為3。
 2.4.2 VLOOKUP 並返回最後匹配的值

如果您想要使用VLOOKUP並返回最後一個匹配的值,如下圖所示,本教程“VLOOKUP And Return The Last Matching Value”可能會幫助您詳細獲取最後一個匹配的值。

VLOOKUP and return the last matching value


2.5 VLOOKUP 查找介於兩個給定值或日期之間的數據

有時候,您可能希望查找介於兩個值或日期之間的數據,並返回相應的結果,如下圖所示。在這種情況下,您可以使用 LOOKUP 函數,而不是使用 VLOOKUP 函數,並搭配排序的表格。
VLOOKUP matching values between two values

 2.5.1 使用公式在兩個給定值或日期之間匹配 VLOOKUP 值

步驟1:整理數據並應用以下公式

您的原始表格應該是已排序的資料區域。然後,將以下公式複製或輸入到空白單元格中。接著,拖曳填充柄以將此公式填充到其他需要的單元格中。

=LOOKUP(2,1/($A$2:$A$6<=E2)/($B$2:$B$6>=E2),$C$2:$C$6)

結果:

現在,您將根據給定的值獲得所有匹配的記錄,請參見截圖:
Arrange the data and apply a formula

備註:

  • 在上述公式中:
    • "A2:A6" 是較小值的區域;
    • "B2:B6" 是較大數字的區域;
    • "E2" 是您想要獲取其對應值的查找的值;
    • "C2:C6" 是您想要返回對應值的列。
  • 此公式也可用於提取介於兩個日期之間的匹配值,如下圖所示:
    this formula also can extract matched values between two dates
 2.5.2 使用便捷功能在兩個給定值或日期之間進行 VLOOKUP 匹配

如果您覺得記住和理解上述公式很困難,我將為您介紹一個簡單的工具——「Kutools for Excel」。利用其「查找介於兩值之間的數據」功能,您可以輕鬆根據特定的值或日期返回相應的項目。

  1. 點擊「Kutools」>「高級 LOOKUP」>「查找介於兩值之間的數據」以啟用此功能。
  2. 然後根據您的數據從對話框中指定計算。
注意:要應用此功能,請下載 Kutools for Excel 並享受30 天免費試用

VLOOKUP matching values between two given values or dates by kutools

Kutools for Excel 提供超過 300 種進階功能,簡化複雜任務,提升創造力與效率。 結合 AI 能力,Kutools 能夠精準自動化任務,讓數據管理變得輕而易舉。Kutools for Excel 的詳細資訊...免費試用...

2.6 在 VLOOKUP 函數中使用萬用字元進行部分匹配

在 Excel 中,可以在 VLOOKUP 函數中使用萬用字元,這允許您對查找的值進行部分匹配。例如,您可以使用 VLOOKUP 根據查找值的一部分從表格中返回匹配的值。

假設,我有一個如下面截圖所示的數據區域,現在,我想根據姓(而不是全名)提取分數。如何在 Excel 中解決這個任務?
VLOOKUP partial matches

步驟1:應用公式並填充至其他單元格

請將以下公式複製或輸入到空白單元格中,然後拖動填充手柄以將此公式填充到其他需要的單元格中:

=VLOOKUP(E2&"*", $A$2:$C$11, 3, FALSE)

結果:

所有匹配的分數已返回,如下圖所示:
Apply and fill the formula to other cells

備註:在上述公式中:

  • "E2&"*"" 是部分匹配的條件。這意味著您正在尋找任何以單元格 E2 中的值開頭的值。(通配符 "*" 表示任意一個字符或多個字符)
  • "A2:C11" 是您要搜尋匹配值的資料區域;
  • 「3」表示從資料區域的第3列返回匹配的值;
  • "False" 表示精確匹配。(使用萬用字元時,必須將函數中的最後一個參數設為 FALSE 或0,以啟用 VLOOKUP 函數的精確匹配模式。)
提示:
  • 要查找並返回以特定值結尾的匹配值,您應在該值前面放置通配符 "*"。請應用此公式:
  • =VLOOKUP("*"&E2, $A$2:$C$11, 3, FALSE)

    To return the matching values ending with a specific value, put the wildcard in front of the value
  • 要根據部分字串查找並返回匹配的值,無論指定的文字是在字串的開頭、結尾或中間,只需在文字或單元格引用的兩側加上兩個星號(*)。請使用此公式進行操作
  • =VLOOKUP("*"&D2&"*", $A$2:$B$11, 2, FALSE)

    to return the matched value based on part of the text string, enclose the cell reference with two asterisks on both sides

2.7 從其他工作表中查找 VLOOKUP 值

通常,您可能需要處理多個工作表,VLOOKUP 函數可以用來從另一個工作表查找數據,就像在一個工作表上一樣。

例如,您有兩個工作表,如下圖所示,若要查找並返回您指定工作表中的相應數據,請按照以下步驟操作:
VLOOKUP from another worksheet

步驟1:應用公式並填充至其他單元格

請在您希望獲得匹配項目的空白單元格中輸入或複製以下公式。然後,將填充柄向下拖動到您想要應用此公式的單元格。

=VLOOKUP(A2,'Data sheet'!$A$2:$C$15,3,0)

結果:

您將獲得所需的相應結果,請參閱截圖:

data in one sheet arrow right get the corresponding results in another sheet

備註:在上述公式中:

  • "A2"代表查找的值;
  • "'Data sheet'!A2:C15" 表示在名為 Data sheet 的工作表上搜尋區域 A2:C15 的值;(如果工作表名稱包含空格或標點符號,應將工作表名稱用單引號括起來,否則可以直接使用工作表名稱,例如:
    =VLOOKUP(A2,Datasheet!$A$2:$C$15,3,0) 。
  • "3" 是包含您要返回匹配數據的列號;
  • "0" 表示執行精確匹配。

2.8 從另一個工作簿中 VLOOKUP 值

本節將介紹如何使用 VLOOKUP 函數從不同的工作簿中查找並返回匹配的值。

例如,假設您有兩個工作簿。第一個工作簿包含產品列表及其相應的成本。在第二個工作簿中,您希望提取每個產品項目的相應成本,如下圖所示。
VLOOKUP from another workbook

步驟1:應用公式

打開您要使用的兩個工作簿,然後在第二個工作簿中您希望放置結果的單元格中應用以下公式。接著,拖動並複製此公式到其他需要的單元格中

=VLOOKUP(B2,'[Product list.xlsx]Sheet1'!$A$2:$B$6,2,0)

結果:

Apply and fill the formula

備註:

  • 在上述公式中:
    • "B2"代表查找的值;
    • "'[Product list.xlsx]Sheet1'!A2:B6" 表示從工作簿 Product list 中名為 Sheet1 的工作表的區域 A2:B6進行搜尋;(工作簿的引用用方括號括起,整個工作簿 + 工作表用單引號括起。)
    • "2" 是包含您想要返回的匹配數據的列號;
    • "0" 表示返回精確匹配。
  • 如果查找工作簿已關閉,則查找工作簿的完整檔案路徑將在公式中顯示,如以下截圖所示:
    If the lookup workbook is closed, the full file path for the lookup workbook is shown in the formula

2.9 返回空白或特定文字,而不是0 或 #N/A 錯誤

通常,當您使用 VLOOKUP 函數返回對應值時,如果匹配的單元格為空白,則會返回0。而如果找不到匹配值,您將獲得 #N/A 錯誤值,如下圖所示。如果您希望顯示空白單元格或特定值而不是0 或 #N/A,這篇教學文章“VLOOKUP To Return Blank Or Specific Value Instead Of0 Or N/A”可能會對您有所幫助。

Return blank or specific text instead of 0 or #N/A error


高級 VLOOKUP 示例

3.1 雙向查找(在行和列中使用 VLOOKUP)

有時候,您可能需要執行二維查找,也就是同時在行和列中搜尋一個值。例如,如果您有以下資料區域,您可能需要獲取特定產品在指定季度的值。本節將介紹一個在 Excel 中處理此任務的公式。
VLOOKUP in row and column

在 Excel 中,您可以結合使用 VLOOKUP 和 MATCH 函數來進行雙向查找。

請將以下公式應用到一個空白單元格中,然後按下 "Enter" 鍵以獲得結果。

=VLOOKUP(G2, $A$2:$E$7, MATCH(H1, $A$2:$E$2, 0), FALSE)

use a combination of VLOOKUP and MATCH functions to get the result

備註:在上述公式中:

  • "G2" 是您要根據其獲取相應值的列中的查找的值;
  • "A2:E7" 是您要查找的數據表;
  • "H1" 是您要根據行中查找的值以獲得相應值的查找值;
  • "A2:E2" 是列頁首的單元格;
  • "FALSE" 表示獲取精確匹配。

3.2 根據兩個或多個條件匹配值的 VLOOKUP

根據一個條件查找匹配值很容易,但如果有兩個或更多條件,該怎麼辦?

 3.2.1 使用公式根據兩個或多個條件匹配 VLOOKUP 值

在這種情況下,Excel中的LOOKUP或MATCH和INDEX函數可以幫助您快速輕鬆地完成這項工作。

例如,我有以下數據表,為了根據特定產品和大小返回匹配的價格,以下公式可能會對您有所幫助。
VLOOKUP based on two or more criteria

步驟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))

結果:

Apply any one formula to get the result

備註:

  • 在上述公式中:
    • "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))
  • join the other criteria into the formula if there are more than two criteria
 3.2.2 使用 Kutools for Excel 根據兩個或多個條件匹配 VLOOKUP 值

記住上述需要重複應用的複雜公式可能具有挑戰性,這會降低您的工作效率。然而,"Kutools for Excel" 提供了一個 "多條件查找" 功能,您只需點擊幾下即可根據一個或多個條件返回相應的結果。

  1. 點擊「Kutools」>「高級 LOOKUP」>「多條件查找」以啟用此功能。
  2. 然後根據您的數據從對話框中指定計算。
注意:要應用此功能,請下載 Kutools for Excel 並享受30 天免費試用

VLOOKUP based on two or more criteria by kutools

Kutools for Excel 提供超過 300 種進階功能,簡化複雜任務,提升創造力與效率。 結合 AI 能力,Kutools 能夠精準自動化任務,讓數據管理變得輕而易舉。Kutools for Excel 的詳細資訊...免費試用...

3.3 使用 VLOOKUP 根據一個或多個條件返回多個值

在 Excel 中,VLOOKUP 函數會搜尋一個值,並且只返回第一個匹配的值,即使找到多個對應值。有時候,您可能希望在一行、一列或單一單元格中返回所有對應的值。本節將討論如何在工作簿中使用一個或多個條件返回多個匹配值。

 3.3.1 水平查找所有符合一個或多個條件的值

假設您有一個包含國家、城市和名稱的數據表,範圍為A1:C14,現在,您希望將所有來自 "US" 的名稱水平返回,如下圖所示。要解決此任務,請 點擊此處逐步獲得結果

 VLOOKUP all matching values based on one or more conditions horizontally

 3.3.2 垂直查找所有符合一個或多個條件的值

如果您需要根據特定條件垂直查找並返回所有匹配的值,如下圖所示,請點擊此處以獲取詳細解決方案

 VLOOKUP all matching values based on one or more conditions vertically

 3.3.3 VLOOKUP 根據一個或多個條件將所有匹配的值合併至單一單元格

如果您想使用 Vlookup 並將多個匹配的值返回到一個單元格中,並使用指定的區分符,新的 TEXTJOIN 函數可以幫助您快速輕鬆地完成這項工作

 VLOOKUP all matching values based on one or more conditions into single cell

備註:


3.4 VLOOKUP 返回匹配單元格的整行

在本節中,我將討論如何使用 VLOOKUP 函數檢索匹配值的整行。

步驟1:應用以下公式

請將以下公式複製或輸入到您希望輸出結果的空白單元格中,然後按下「Enter」鍵以獲得第一個值。接著,將公式單元格向右拖曳,直到顯示整行的數據。

=VLOOKUP($F$2,$A$1:$D$12,COLUMN(A1),FALSE)

結果:

現在,您可以看到整行數據已返回。請參閱截圖:
VLOOKUP to return entire row of a matched cell by a formula

注意:在上述公式中:

  • "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 函數來完成此任務。
Nested VLOOKUP

嵌套 VLOOKUP 函數的通用公式是:

=VLOOKUP(VLOOKUP(查找的值, 表格區域1, col_index_num1,0), 表格區域2, col_index_num2,0)

備註:

  • "查找的值" 是您要查找的值;
  • "表格區域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)

結果:

現在,您將獲得如下截圖所示的結果:
Apply and fill a formula

備註:在上述公式中:

  • "G3" 包含您要查找的值;
  • "A3:B7", "D3:E7" 是查找的值和返回值所在的表格區域;
  • "2" 是區域中用來返回匹配值的列號。
  • "0" 表示 VLOOKUP 精確匹配。

3.6 檢查值是否存在於另一列的列表數據中

VLOOKUP 函數也可以幫助您根據另一列中的數據列表檢查值是否存在。例如,如果您想在列 C 中查找名稱,並僅返回是或否以指示名稱是否在列 A 中找到,如下圖所示。
Check if value exists based on a list data in another column

步驟1:應用以下公式

請將以下公式應用到一個空白單元格中,然後拖動填充柄向下至您想填充此公式的單元格。

=IF(ISNA(VLOOKUP(C2,$A$2:$A$10,1,FALSE)), "No", "Yes")

結果:

您將獲得所需的結果,請參見截圖:
Apply and fill a formula

備註:在上述公式中:

  • "C2" 是您要查找的值;
  • "A2:A10" 是要檢查待檢索值是否存在的區域列表;
  • "FALSE" 表示獲取精確匹配。

3.7 VLOOKUP 並求和所有匹配的行或列中的值

在處理數據時,您可能需要從對照表中提取匹配的值,並對多個列或行中的數字進行求和。本節將介紹一些可以幫助您完成此任務的公式。

 3.7.1 VLOOKUP 並求和所有匹配的值在一行或多行中

假設您有一個產品列表,其中包含幾個月的銷售數據,如下圖所示。現在,您需要根據給定的產品對所有月份的所有訂單進行求和。
VLOOKUP and sum all matched values in a row

步驟1:應用以下公式

請將以下公式複製或輸入到一個空白單元格中,然後同時按下 "Ctrl" + "Shift" + "Enter" 鍵以獲得第一個結果。接著,向下拖動填充柄以將此公式複製到其他需要的單元格。

=SUM(VLOOKUP(H2, $A$2:$F$9, {2,3,4,5,6}, FALSE))

Apply and fill a formula

結果:

第一個匹配值所在行的所有值已經被求和,請參見截圖:
all values in a row of the first matching value are summed together

備註:在上述公式中:

  • "H2" 是包含您要查找的值的單元格;
  • "A2:F9" 是資料區域(不含列標題),其中包括查找的值和匹配的值;
  • "{2,3,4,5,6}" 是用來計算區域總和的列號;
  • 「否」表示精確匹配。

提示:如果您想在多行中求和所有匹配項,請使用以下公式:

  • =SUMPRODUCT(($A$2:$A$9=H2)*$B$2:$F$9)
  • apply a formula to sum all matches in multiple rows
 3.7.2 VLOOKUP 並求和列或多列中所有匹配的值

如果您想要對特定月份的總值進行求和,如下圖所示。普通的 VLOOKUP 函數可能無法幫助您,此時,您應該將 SUM、INDEX 和 MATCH 函數結合使用來創建公式。
VLOOKUP and sum all matched values in a column

步驟1:應用以下公式

將以下公式應用到一個空白單元格中,然後向下拖動填充柄以將此公式複製到其他單元格。

=SUM(INDEX($B$2:$F$9,0,MATCH(H2,$B$1:$F$1,0)))

結果:

現在,基於特定月份在列中的第一個匹配值已經被求和,請參見截圖:
Apply and fill a formula

備註:在上述公式中:

  • "H2" 是包含您要查找的值的單元格;
  • "B1:F1" 是包含查找的值的列標題;
  • "B2:F9" 是包含您想要求和的數字字符的資料區域。

提示:要使用 VLOOKUP 並求和多列中的所有匹配值,您應使用以下公式:

  • =SUMPRODUCT($B$2:$F$9*(($B$1:$F$1)=H2))
  • use a formula to sum all matched values in multiple columns
 3.7.3 使用 Kutools for Excel 查找並求和首個匹配或所有匹配的值

可能上述公式對您來說難以記住,在這種情況下,我會推薦一個強大的功能——Kutools for Excel 的「查找求和」,使用此功能,您可以輕鬆地在行或列中 Vlookup 並求和第一個匹配或所有匹配的值。

  1. 點擊 "Kutools" > "Super LOOKUP" > "查找求和"以啟用此功能。
  2. 然後根據您的需求從對話框中指定計算。
備註:要應用此功能,請下載 Kutools for Excel 並享受30 天免費試用
Kutools for Excel 提供超過 300 種進階功能,簡化複雜任務,提升創造力與效率。 結合 AI 能力,Kutools 能夠精準自動化任務,讓數據管理變得輕而易舉。Kutools for Excel 的詳細資訊...免費試用...
 3.7.4 VLOOKUP 和求和所有在行和列中匹配的值

如果您需要在匹配列和行時進行求和,例如,要獲取如下面截圖所示的三月份 Sweater產品的總值。
VLOOKUP and sum all matched values both in rows and columns

在這裡,您可以使用 SUMPRODCT 函數來完成此任務。

請將以下公式應用到單元格中,然後按下「Enter」鍵以獲得結果,請參閱截圖:

=SUMPRODUCT(($B$2:$F$9)*($B$1:$F$1=I2)*($A$2:$A$9=H2))

use the SUMPRODCT function to get the result

備註:在上述公式中:

  • "B2:F9" 是包含您要求和的數字字符的資料區域;
  • "B1:F1" 是包含您要根據查找值進行求和的列標題;
  • "I2" 是您要查找的列標題中的查找的值;
  • "A2:A9" 是包含您要求和的查找值的行標題;
  • "H2" 是您在查找的行頁首中的查找的值。

3.8 使用 VLOOKUP 根據關鍵列合併兩個對照表

在日常工作中,分析數據時,您可能需要根據一個或多個關鍵列將所有必要的信息匯集到一個表中。為了完成這項任務,您可以使用 INDEX 和 MATCH 函數,而不是 VLOOKUP 函數。

 3.8.1 使用 VLOOKUP 根據一個關鍵列合併兩個對照表

例如,您有兩個表格,第一個表格包含產品和名稱資料,第二個表格包含產品和訂單資料,現在,您希望通過匹配共同的產品列將這兩個表格合併為一個表格。
VLOOKUP to merge two tables based on one key column

步驟1:應用以下公式

請在空白單元格中輸入以下公式。然後,將填充柄向下拖動到您想要應用此公式的單元格。

=INDEX($F$2:$F$8, MATCH($A2, $E$2:$E$8, 0))

結果:

現在,您將獲得一個合併的對照表,其中訂單列基於關鍵列數據加入到第一個對照表中。
Apply and fill a formula to get the result

備註:在上述公式中:

  • "A2" 是您要查找的查找的值;
  • "F2:F8" 是您想要返回匹配值的資料區域;
  • "E2:E8" 是查找區域,其中包含查找的值。
 3.8.2 使用 VLOOKUP 根據多個主鍵列合併兩個對照表

如果您要合併的兩個表格有多個關鍵列,請按照以下步驟根據這些共同列合併表格。
VLOOKUP to merge two tables based on multiple key columns

通用公式為:

=INDEX(查找表格區域, MATCH(1, (查找的值=待檢索值區域) * (查找的值=待檢索值區域),0), 返回查找列數)

備註:

  • "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)

Apply a formula

步驟2:將公式填充到其他單元格

然後,選擇第一個公式單元格,並拖動填充柄以將此公式複製到其他需要的單元格:
Fill the formula to other cells

提示:在 Excel2016 或更高版本中,您也可以使用“Power Query”功能根據關鍵列將兩個或多個表合併為一個。請點擊以逐步了解詳細資訊。

3.9 VLOOKUP 匹配多個工作表中的值

您是否曾經需要在 Excel 中跨多個工作表執行 VLOOKUP?例如,如果您有三個工作表包含資料區域,並且您希望根據這些工作表中的條件檢索特定值,您可以按照逐步教程 VLOOKUP Values Across Multiple Worksheets來完成此任務。

VLOOKUP across multiple worksheets


VLOOKUP 匹配的值保持單元格格式

在查找匹配值時,原始單元格格式如字體顏色、背景色、資料格式等將不會保留。為了保留單元格或數據格式,本節將介紹一些解決此問題的技巧。

4.1 VLOOKUP 匹配值並保留單元格顏色和字體格式

眾所周知,正常的 VLOOKUP 函數只能從另一個資料區域中檢索匹配的值。然而,有時您可能希望獲取對應的值以及單元格格式,例如填充顏色、字體顏色和字體樣式。在本節中,我們將討論如何在 Excel 中檢索匹配值同時保留源格式。
VLOOKUP and keep cell formatting

請按照以下步驟查找並返回其對應的值及單元格格式:

步驟1:將代碼1 複製到工作表代碼模組中

  1. 在包含您要 VLOOKUP 的數據的工作表中,右鍵點擊工作表標籤,然後從上下文選單中選擇「檢視代碼」。請參見截圖:
     right click the sheet tab and select View Code
  2. 在打開的「Microsoft Visual Basic for Applications」視窗中,請將以下的 VBA代碼複製到代碼視窗中。
  3. VBA 程式碼1:使用 VLOOKUP 查找值並獲取單元格格式
  4. 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
    
  5. copy and paste the code1 into the module

步驟2:將代碼2 複製到模組視窗中

  1. 仍然在 "Microsoft Visual Basic for Applications" 視窗中,點擊 "插入" > "模組",然後將以下 VBA代碼2 複製到 "模組" 視窗中。
  2. VBA代碼2:使用 VLOOKUP 查找值並獲取單元格格式
  3. 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
    
  4. copy and paste the code2 into the module

步驟3:選擇 VBAproject 的選項

  1. 插入上述代碼後,請在 "Microsoft Visual Basic for Applications" 視窗中點擊 "工具" > "參考"。然後在 "參考 – VBAProject" 對話框中勾選 "Microsoft Scripting Runtime" 選項。請參閱截圖:
    click Tools > References arrow right check the Microsoft Scripting Runtime checkbox in the dialog box
  2. 然後,點擊「確定」以關閉對話框,然後保存並關閉代碼窗口。

步驟4:輸入公式以獲得結果

  1. 現在,返回工作表,應用以下公式。然後,向下拖動填充柄以獲取所有結果及其格式。請參閱截圖:
    =LookupKeepFormat(E2,$A$1:$C$10,3)

    type a formula for getting the result

備註:在上述公式中:

  • "E2" 是您要查找的值;
  • "A1:C10" 是表格區域;
  • "3" 是您要從中檢索匹配值的表格的列號。

4.2 保持 VLOOKUP 返回值的日期格式

當使用 VLOOKUP 函數查找並返回具有日期格式的值時,返回的結果可能顯示為數字。要保持返回結果的日期格式,您應該將 VLOOKUP 函數包含在 TEXT 函數中。
vlookup keep date format

步驟1:應用以下公式

請將以下公式應用到一個空白單元格中。然後,拖動填充柄以將此公式複製到其他單元格。

=TEXT(VLOOKUP(E2,$A$2:$C$9,3,FALSE),"mm/dd/yyyy")

結果:

所有匹配的日期已返回,如下截圖所示:
Apply and fill a formula

備註:在上述公式中:

  • "E2" 是查找的值;
  • "A2:C9" 是查找區域;
  • "3" 是您希望返回值的列號;
  • "否" 表示獲得精確匹配;
  • "mm/dd/yyyy" 是您想要保留的日期格式。

4.3 從 VLOOKUP 返回單元格批註

您是否曾經需要使用 VLOOKUP 在 Excel 中同時檢索匹配的單元格數據及其相關批註,如下圖所示?如果是這樣,下面提供的使用者自訂函數可以幫助您完成此任務。

步驟1:將代碼複製到模組中

  1. 按住 "ALT" + "F11" 鍵以開啟 "Microsoft Visual Basic for Applications" 視窗。
  2. 點擊「插入」>「模組」,然後在「模組」視窗中複製並粘貼以下代碼。
    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
  3. 然後儲存並關閉程式碼視窗。

步驟2:輸入公式以獲得結果

  1. 現在,輸入以下公式,然後拖曳填充柄以將此公式複製到其他單元格。它將同時返回匹配的值和批註,請參見截圖:
    =vlookupcomment(D2,$A$2:$B$9,2,FALSE)

    Type the formula to get the result with comment

備註:在上述公式中:

  • "D2" 是您想要返回其對應值的查找的值;
  • "A2:B9" 是您想要使用的資料表;
  • "2" 是包含您要返回的匹配值的列號;
  • "FALSE" 表示獲取精確匹配。

4.4 VLOOKUP 數字以文字形式儲存

例如,我有一個資料區域,其中原始表格中的ID號碼是數字格式,而查找單元格中的ID號碼則以文字形式儲存,使用普通的VLOOKUP函數時可能會遇到#N/A錯誤。在這種情況下,為了獲取正確的信息,您可以在VLOOKUP函數中包裹TEXT和VALUE函數。以下是實現此目的的公式:
VLOOKUP numbers stored as text

步驟1:應用並填入以下公式

請在空白單元格中應用以下公式,然後向下拖動填充柄以複製此公式。

=IFERROR(VLOOKUP(VALUE(D2),$A$2:$B$8,2,0),VLOOKUP(TEXT(D2,0),$A$2:$B$8,2,0))

結果:

現在,您將獲得正確的結果,如下圖所示:
Apply and fill a formula

備註:

  • 在上述公式中:
    • "D2" 是您要返回其對應值的查找的值;
    • "A2:B8" 是您要使用的資料表;
    • "2" 是包含您想要返回的匹配值的列號;
    • "0" 表示獲得精確匹配。
  • 如果您不確定數字和文字的位置,這個公式也能很好地運作。