跳到主要內容

20 多個適用於 Excel 初學者和高級用戶的 VLOOKUP 示例

VLOOKUP 函數是 Excel 中最常用的函數之一。 本教程將通過數十個基礎和高級示例逐步介紹如何在 Excel 中使用 VLOOKUP 函數。


下載VLOOKUP示例文件

 基本 Vlookup 示例   |    高級 Vlookup 示例   |    Vlookup 保持單元格格式


VLOOKUP函數的介紹–語法和參數

在 Excel 中,VLOOKUP 函數對於大多數 Excel 用戶來說是一個強大的功能,它允許您在數據區域的最左側查找一個值,並從您指定的列返回同一行中的匹配值,如下圖所示.

VLOOKUP函數的語法:

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

參數:

查找值 (必需):您要搜索的值。 它可以是值(數字、日期或文本)或單元格引用。 它必須在 table_array 範圍的第一列中。 

表格數組 (必填):查找值列和結果值列所在的數據范圍或表。

列索引號 (必需):包含返回值的列號。 它從表數組中最左邊的列開始,從 1 開始。

範圍查找 (可選):一個邏輯值,用於確定此 VLOOKUP 函數將返回完全匹配還是近似匹配。

  • 近似匹配 - 1 / TRUE / 省略 (默認):如果未找到完全匹配項,則公式會搜索最接近的匹配項 - 小於查找值的最大值。
    注意:在這種情況下,您必須將查找列(數據范圍最左邊的列)按升序排序,否則將返回錯誤或#N/A 錯誤結果。
  • 完全匹配 – 0 / 假:用於搜索與查找值完全相等的值。 如果找不到完全匹配的內容,則將返回錯誤值#N / A。

功能說明:

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

VLOOKUP基本示例

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

2.1 精確匹配和近似匹配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 相同行中的值。)
  • 指完全匹配。

VLOOKUP 公式如何工作?

首先,它在表的最左邊的列中查找 ID - C1005。 它從上到下查找單元格 A6 中的值。

一旦找到該值,它就會轉到第三列的右側並提取其中的值。

因此,您將得到如下圖所示的結果:

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

Kutools for Excel 擁有超過 300 種功能, 確保只需點擊一下即可獲得您所需要的...

 
 2.1.2 做近似匹配VLOOKUP

近似匹配對於搜索數據范圍之間的值很有用。 如果未找到完全匹配,則近似 VLOOKUP 將返回小於查找值的最大值。

比如你有如下範圍的數據,而指定的訂單不在Orders列中,那麼如何在B列中獲取與其最接近的Discount呢?

第 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 函數總是在數據區域的最左邊的列中搜索一個值,並從右邊的列中返回相應的值。 如果要執行反向 VLOOKUP,這意味著在右列中查找特定值並在左列中返回其對應的值,如下圖所示:

單擊以逐步了解有關此任務的詳細信息…


2.4 VLOOKUP Excel中的第二個、第n個或最後一個匹配值

通常情況下,如果在使用Vlookup 函數時找到多個匹配值,則只返回第一個匹配的記錄。 在本節中,我將討論如何獲取數據范圍內的第二個、第 n 個或最後一個匹配值。

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

假設您在 A 列中有一個姓名列表,他們在 B 列中購買了培訓課程。現在,您要查找給定客戶購買的第 2 個或第 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 並返回最後一個匹配值 教程可以幫助您詳細獲取最後一個匹配值。


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 匹配兩個給定值或日期之間的值,具有方便的功能

如果你覺得難以記住和理解上面的公式,那麼在這裡,我將介紹一個簡單的工具—— Excel的Kutools,其 在兩個值之間查找 功能,您可以輕鬆地根據兩個值或日期之間的特定值或日期返回相應的項目。

  1. 點擊 庫工具 > 超級查找 > 在兩個值之間查找 啟用此功能。
  2. 然後根據您的數據從對話框中指定操作。
備註: 要應用此功能,您應該下載 Kutools for Excel 30天免費試用 首先。


2.6 在 VLOOKUP 函數中使用通配符進行部分匹配

在 Excel 中,可以在 VLOOKUP 函數中使用通配符,它​​允許您對查找值執行部分匹配。 例如,您可以使用 VLOOKUP 根據查找值的一部分從表中返回匹配值。

假設,我有一系列數據,如下圖所示,現在,我想根據名字(不是全名)提取分數。 如何在Excel中解決此任務?

第 1 步:將公式應用並填充到其他單元格

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

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

結果:

並且所有匹配的分數都已返回,如下圖所示:

注意: 在上面的公式中:

  • E2&“ *” 是部分數學的標準。 這意味著您要查找以單元格 E2 中的值開頭的任何值。 (通配符“*”表示任意一個字符或任意字符)
  • A2:C11 是要搜索匹配值的數據范圍;
  • 3 表示從數據范圍的第3列返回匹配值;
  • 表示精確的數學。 (使用通配符時,您必須將函數中的最後一個參數設置為 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 代表查詢值;
  • '數據表'!A2:C15 表示在名為“數據表”的工作表中搜索 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中名為Sheet2的工作表A6:B1範圍內搜索; (對工作簿的引用用方括號括起來,整個工作簿 + 工作表用單引號括起來。)
    • 2 是包含您要從中返回的匹配數據的列號;
    • 0 表示返回完全匹配。
  • 如果查找工作簿已關閉,查找工作簿的完整文件路徑將顯示在公式中,如下圖所示:

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

通常,當你使用VLOOKUP函數返回對應的值時,如果匹配的單元格為空,則返回0。如果沒有找到匹配的值,則會得到#N/A的錯誤值,如下所示下面的截圖。 如果要顯示空白單元格或特定值而不是 0 或 #N/A,這 VLOOKUP 返回空白或特定值而不是 0 或 N/A 教程可能會對你有幫助。


高級VLOOKUP示例

3.1 雙向查找(VLOOKUP in row and column)

有時,您可能需要執行二維查找,這意味著同時在行和列中搜索一個值。 例如,如果您有以下數據范圍,您可能需要獲取特定產品在指定季度的值。 本節將介紹在 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 是列標題的單元格;
  • 表示獲得精確匹配。

3.2 基於兩個或多個條件的VLOOKUP匹配值

你很容易根據一個標準來查找匹配值,但是如果你有兩個或更多的標準,你能做什麼呢?

 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 表示在A1:A2範圍內搜索G12的條件;
    • B2:B12 = G2 表示在B2:B2範圍內搜索G12的條件;
    • D2:D12 is 您要從中返回相應值的範圍。
  • 如果你有兩個以上的條件,你只需要將其他條件加入到公式中,例如:
    =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 VLOOKUP 基於兩個或多個條件的匹配值具有智能特徵

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

  1. 點擊 庫工具 > 超級查找 > 多條件查找 啟用此功能。
  2. 然後根據您的數據從對話框中指定操作。
備註: 要應用此功能,您應該下載 Kutools for Excel 30天免費試用 首先。


3.3 VLOOKUP 以一個或多個條件返回多個值

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

 3.3.1 VLOOKUP基於一個或多個條件橫向匹配的所有值

假設您有一個數據表,其中包含 A1:C14 範圍內的國家/地區、城市和名稱,現在您想要水平返回所有來自“US”的名稱,如下圖所示。 要解決這個任務,請 單擊此處逐步獲取結果.

 3.3.2 VLOOKUP 所有基於一個或多個條件的垂直匹配值

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

 3.3.3 VLOOKUP 所有基於一個或多個條件的匹配值到單個單元格中

如果要 Vlookup 並將多個匹配值返回到具有指定分隔符的單個單元格中, TEXTJOIN 的新功能可以幫助您快速輕鬆地解決這個問題.

筆記:


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

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

第 1 步:應用並填寫以下公式

請將以下公式複製或鍵入到要輸出結果的空白單元格中,然後按 Enter 獲取第一個值的鍵。 然後,將公式單元格向右拖動,直到顯示整行數據。

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

結果:

現在,您可以看到返回了整行數據。 看截圖:
文檔 vlookup 函數 50 1

注意: 在上面的公式中:

  • F2 是您要根據其返回整行的查找值;
  • A1:D12 是您要從中搜索查找值的數據范圍;
  • A1 表示數據范圍內的第一列編號;
  • 表示精確查找。

提示:

  • 如果根據匹配值找到多行,要返回所有對應的行,請應用以下公式,然後按 Ctrl + Shift + Enter 組合鍵以獲得第一個結果。 然後向右拖動填充手柄。 然後,繼續向下拖動填充柄穿過單元格以獲取所有匹配的行。 請參閱下面的演示:
    =IFERROR(INDEX(A:A,SMALL(IF(ISNUMBER(SEARCH($F$2,$A$2:$A$12)),ROW($A$2:$A$12),""),ROW()-1)),"")
    文檔 vlookup 函數 51 2

3.5 Excel 中的嵌套 VLOOKUP

有時,您可能需要查找跨多個表相互關聯的值。 在這種情況下,您可以將多個 VLOOKUP 函數嵌套在一起以獲得最終值。

例如,我有一個包含兩個單獨表格的工作表。 第一個表格列出了所有產品名稱及其相應的銷售人員。 第二張表列出了每個推銷員的總銷售額。 現在,如果您想要查找每個產品的銷售額,如下面的屏幕截圖所示,您可以嵌套 VLOOKUP 函數來完成此任務。
文檔 vlookup 函數 53 1

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

=VLOOKUP(VLOOKUP(lookup_value, table_array1, col_index_num1, 0), table_array2, col_index_num2, 0)

注意:

  • Lookup_Array中 是您正在尋找的價值;
  • 表_array1, 表_array2 是查找值和返回值存在的表;
  • 列索引號1 表示查找中間公共數據在第一個表中的列號;
  • 列索引號2 表示要返回匹配值的第二個表中的列號;
  • 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 列中找到或找不到該名稱,則只返回 Yes 或 No,如下圖所示。
文檔 vlookup 函數 56 1

第 1 步:應用並填寫以下公式

請將以下公式應用於空白單元格,然後將填充柄向下拖動到要填充此公式的單元格。

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

結果:

你會得到你需要的結果,看截圖:

注意: 在上面的公式中:

  • C2 是您要檢查的查找值;
  • A2:A10 是檢查是否找到查找值的範圍列表;
  • 表示獲得精確匹配。

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

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

 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 VLOOKUP 對第一個匹配的或者所有匹配的值求和,一個強大的功能

可能上面的公式你很難記住,既然如此,我就推荐一個強大的功能—— 查找和總和 of Excel的Kutools,通過這個功能,您可以盡可能輕鬆地對行或列中的第一個匹配值或所有匹配值進行 Vlookup 和求和。

  1. 點擊 庫工具 > 超級查找 > 查找和總和 啟用此功能。
  2. 然後根據需要從對話框中指定操作。
備註: 要應用此功能,您應該下載 Kutools for Excel 30天免費試用 首先。
 3.7.4 VLOOKUP 並對行和列中的所有匹配值求和

例如,如果您想在需要同時匹配列和行時對值求和,以獲取XNUMX月月份產品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根據多個鍵列合併兩張表

如果要聯接的兩個表有多個鍵列,要根據這些公共列合併表,請按照以下步驟操作。

通用公式為:

=INDEX(lookup_table, MATCH(1, (lookup_value1=lookup_range1) * (lookup_value2=lookup_range2), 0), return_column_number)

注意:

  • 查找表 是包含查找數據和匹配記錄的數據范圍;
  • 查找值1 是您要尋找的第一個標準;
  • 查找範圍1 數據列表是否包含第一個條件;
  • 查找值2 是您正在尋找的第二個標準;
  • 查找範圍2 數據列表是否包含第二個條件;
  • 返回列編號 表示要返回匹配值的lookup_table中的列號。

第 1 步:應用以下公式

請將下面的公式應用到要放置結果的空白單元格中,然後按 Ctrl + Shift + Enter 鍵一起獲得第一個匹配的值,請參見屏幕截圖:

=INDEX($E$2:$G$9, MATCH(1, ($A2=$E$2:$E$9) * ($B2=$F$2:$F$9), 0), 3)

第二步:將公式填充到其他單元格

然後,選擇第一個公式單元格,並拖動填充柄以根據需要將此公式複製到其他單元格:

保養竅門: 在 Excel 2016 或更高版本中,您還可以使用 Power Query 該功能可根據鍵列將兩個或多個表合併為一個表。 請點擊以了解詳細信息.

3.9 VLOOKUP 跨多個工作表匹配值

您是否需要在 Excel 中跨多個工作表執行 VLOOKUP? 例如,如果您有三個包含數據范圍的工作表,並且您想要根據這些工作表中的條件檢索特定值,則可以按照分步教程進行操作 跨多個工作表的 VLOOKUP 值 完成這項任務。


VLOOKUP匹配值保持單元格格式

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

4.1 VLOOKUP匹配值並保持單元格顏色、字體格式

眾所周知,普通的 VLOOKUP 函數只能從另一個數據范圍中檢索匹配值。 但是,在某些情況下,您可能希望獲得相應的值以及單元格格式,例如填充顏色、字體顏色和字體樣式。 在本節中,我們將討論如何在 Excel 中保留源格式的同時檢索匹配值。

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

第一步:將代碼1複製到Sheet Code Module

  1. 在工作表中包含要VLOOKUP的數據,右擊工作表標籤,選擇 查看代碼 從上下文菜單中。 看截圖:
  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
    

第二步:將代碼2複製到Module窗口

  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
    

第 3 步:選擇 VBAproject 的選項

  1. 輸入以上代碼後,點擊 工具 > 參考 ,在 Microsoft Visual Basic for Applications 窗口。 然後檢查 Microsoft腳本運行時 中的複選框 參考– VBAProject 對話框。 查看屏幕截圖:
  2. 然後,單擊 OK 關閉對話框,然後保存並關閉代碼窗口。

第 4 步:鍵入獲取結果的公式

  1. 現在,返回工作表,應用以下公式。 然後,向下拖動填充柄以獲取所有結果及其格式。 看截圖:
    =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/yyy 是您要保留的日期格式。

4.3 從 VLOOKUP 返回單元格註釋

您是否曾經需要在 Excel 中使用 VLOOKUP 檢索匹配的單元格數據及其關聯的註釋,如以下屏幕截圖所示? 如果是這樣,下面提供的用戶定義函數可以幫助您完成此任務。

第 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)

注意: 在上面的公式中:

  • D2 是要返回其對應值的查找值;
  • A2:B9 是你要使用的數據表;
  • 2 是包含要返回的匹配值的列號;
  • 表示獲得精確匹配。

4.4 VLOOKUP 數字存儲為文本

例如,我有一個數據范圍,其中原始表中的 ID 號為數字格式,而查找單元格中的 ID 號存儲為文本,使用普通 VLOOKUP 函數時可能會遇到 #N/A 錯誤。 在這種情況下,要檢索正確的信息,您可以將 TEXT 和 VALUE 函數包裝在 VLOOKUP 函數中。下面是實現此目的的公式:

第 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 表示獲得精確匹配。
  • 如果您不確定數字和文本的位置,此公式也很有效。
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations