10 個適用於初學者和高級用戶的 VLOOKUP 示例

10 個適用於初學者和高級用戶的 VLOOKUP 示例

VLOOKUP 函數是基準。

如果您知道如何使用 VLOOKUP 函數,您就知道 Excel 中的一些內容。

如果你不這樣做,你最好不要在簡歷中將 Excel 列為你的強項之一。

我參加了小組面試,一旦候選人提到 Excel 作為他的專業領域,首先被問到的是 – 你明白了 – VLOOKUP 功能。

既然我們知道了這個 Excel 函數的重要性,那麼完全掌握它以便能夠自豪地說——“我在 Excel 中知道一兩件事”是有道理的。

這將是一個龐大的 VLOOKUP 教程(按照我的標準)。

我將介紹有關它的所有知識,然後向您展示有用且實用的 VLOOKUP 示例。

何時在 Excel 中使用 VLOOKUP 函數?

VLOOKUP 函數最適合您在列中查找匹配數據點的情況,當找到匹配數據點時,您會在該行的右側並從指定數量的單元格中獲取值右側的列。

讓我們在這裡舉一個簡單的例子來了解何時在 Excel 中使用 Vlookup。

請記住,當考試成績單出來並粘貼在佈告欄上時,每個人都曾經瘋狂地尋找自己的名字和分數(至少那是我在學校時經常發生的事情)。

這是它的工作原理:

  • 您走到佈告欄並開始尋找您的姓名或註冊號(在列表中從上到下移動您的手指)。
  • 一旦你發現你的名字,你就會把眼睛移到名字/註冊號的右邊來查看你的分數。

這正是 Excel VLOOKUP 功能為您所做的(請在下一次採訪中隨意使用此示例)。

VLOOKUP 函數在列中查找指定值(在上面的示例中,它是您的名字),當它找到指定的匹配項時,它會在同一行中返回一個值(您獲得的標記)。

句法

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

輸入參數

  • lookup_value –這是您試圖在表的最左列中查找的查找值。它可以是一個值、一個單元格引用或一個文本字符串。在記分錶示例中,這將是您的姓名。
  • table_array –這是您要在其中查找值的表數組。這可以是對單元格範圍或命名範圍的引用。在分數表示例中,這將是包含每個主題的每個人的分數的整個表
  • col_index –這是您要從中獲取匹配值的列索引號。在分數表示例中,如果您想要數學的分數(這是包含分數的表中的第一列),您可以查看第 1 列。如果您想要物理的分數,您可以查看第 1 列2.
  • [range_lookup] –在這裡您可以指定您想要精確匹配還是近似匹配。如果省略,則默認為 TRUE – 近似匹配(請參閱下面的附加說明)。

附加說明(無聊,但重要的是要知道)

  • 匹配可以是精確的(FALSE 或 range_lookup 中的 0)或近似的(TRUE 或 1)。
  • 在近似查找中,確保列表按升序(從上到下)排序,否則結果可能不准確。
  • 當 range_lookup 為 TRUE(近似查找)並且數據按升序排序時:
    • 如果 VLOOKUP 函數找不到該值,則返回小於 lookup_value 的最大值。
    • 如果lookup_value 小於最小值,則返回#N/A 錯誤。
    • 如果lookup_value 是文本,可以使用通配符(參考下面的示例)。

現在,希望你對 VLOOKUP 函數能做什麼有一個基本的了解,讓我們來剝洋蔥,看看 VLOOKUP 函數的一些實際例子。

10 個 Excel VLOOKUP 示例(基礎和高級)

這裡有 10 個使用 Excel Vlookup 的有用示例,將向您展示如何在日常工作中使用它。

相關問題  Excel WEEKDAY 函數的使用方法

示例 1 – 查找 Brad 的數學分數

在下面的 VLOOKUP 示例中,我有一個列表,最左邊的列中有學生姓名,B 到 E 列中有不同科目的分數。

VLOOKUP 示例 - 使用 VLOOKUP 函數查找布拉德標記的數據集

現在讓我們開始工作並使用 VLOOKUP 函數來做它最擅長的事情。從上面的數據中,我需要知道布拉德在數學上的得分是多少。

從上面的數據中,我需要知道布拉德在數學上的得分是多少。

這是返回 Brad 數學分數的 VLOOKUP 公式:

=VLOOKUP("Brad",$A$3:$E$10,2,0)

上面的公式有四個參數:

  • “Brad”: ——這是查找值。
  • $A$3:$E$10 – 這是我們正在查看的單元格範圍。請記住,Excel 在最左側的列中查找查找值。在此示例中,它將在 A3:A10 (這是指定數組的最左側列)中查找名稱 Brad。
  • 2 – 一旦函數發現 Brad 的名字,它將轉到數組的第二列,並返回與 Brad 相同的行中的值。這裡的值 2 表示我們正在從指定數組的第二列中查找分數。
  • – 這告訴 VLOOKUP 函數只查找完全匹配。

以下是上述示例中 VLOOKUP 公式的工作原理。

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

VLOOKUP 函數從上到下掃描列表

一旦找到該值,它就會在第二列中向右移動並獲取其中的值。

找到匹配項後,VLOOKUP 將向右移動到指定列

您可以使用相同的公式構造來獲得任何人在任何科目中的分數。

例如,要查找 Maria 在化學中的分數,請使用以下 VLOOKUP 公式:

=VLOOKUP("Maria",$A$3:$E$10,4,0)

Excel Vlookup 示例 1a 瑪麗亞化學

在上面的示例中,查找值(學生姓名)用雙引號輸入。您還可以使用包含查找值的單元格引用。

使用單元格引用的好處是它使公式動態化。

例如,如果您有一個包含學生姓名的單元格,並且您正在獲取數學的分數,那麼當您更改學生姓名時,結果會自動更新(如下所示):

VLOOKUP 示例展示瞭如何使用下拉菜單的功能

如果您輸入的查找值在最左側列中找不到,則會返回 #N/A 錯誤。

示例 2 – 雙向查找

在上面的示例 1 中,我們對列值進行了硬編碼。因此,公式將始終返回 Math 的分數,因為我們使用 2 作為列索引號。

但是,如果您想讓 VLOOKUP 值和列索引號都動態化怎麼辦。例如,如下所示,您可以更改學生姓名或科目名稱,VLOOKUP 公式會獲取正確的分數。這是一個雙向 VLOOKUP 公式的示例。

這是一個雙向 VLOOKUP 函數的示例。

VLOOKUP 示例 - Excel 中的兩種方式查找

要製作這個雙向查找公式,您還需要使列動態化。因此,當用戶更改主題時,公式會自動選擇正確的列(數學為 2,物理為 3,依此類推..)。

為此,您需要使用 MATCH 函數作為列參數。

這是 VLOOKUP 公式,將執行此操作:

=VLOOKUP(G4,$A$3:$E$10,MATCH(H3,$A$2:$E$2,0),0)

上面的公式使用 MATCH(H3,$A$2:$E$2,0) 作為列號。MATCH 函數將主題名稱作為查找值(在 H3 中)並返回其在 A2:E2 中的位置。因此,如果您使用 Math,它將返回 2,因為在 B2 中找到了 Math(這是指定數組範圍中的第二個單元格)。

示例 3 – 使用下拉列表作為查找值

在上面的例子中,我們必須手動輸入數據​​。這可能既耗時又容易出錯,尤其是當您有大量查找值時。

在這種情況下,一個好主意是創建一個查找值的下拉列表(在這種情況下,它可以是學生姓名和科目),然後簡單地從列表中進行選擇。

根據選擇,公式將自動更新結果。

如下圖所示:

使用下拉列表作為查找值

這是一個很好的儀表板組件,因為您可以在後端擁有一個包含數百名學生的龐大數據集,但最終用戶(比如說老師)可以通過簡單地從下拉。

如何做到這一點:

本例中使用的 VLOOKUP 公式與示例 2 中使用的相同。

=VLOOKUP(G4,$A$3:$E$10,MATCH(H3,$A$2:$E$2,0),0)

查找值已轉換為下拉列表。

以下是創建下拉列表的步驟:

  • 選擇需要下拉列表的單元格。在此示例中,在 G4 中,我們需要學生姓名。
  • 轉到數據 -> 數據工具 -> 數據驗證。
  • 在數據驗證對話框的設置選項卡中,從允許下拉列表中選擇列表。
  • 在源中,選擇 $A$3:$A$10
  • 單擊確定。

現在您將在單元格 G4 中看到下拉列表。同樣,您可以在 H3 中為主題創建一個。

示例 4 – 三向查找

什麼是三向查找?

在示例 2 中,我們使用了一個查找表,其中包含不同科目學生的分數。這是一個雙向查找的示例,因為我們使用兩個變量來獲取分數(學生姓名和受試者姓名)。

現在,假設在一年內,一個學生要參加三個不同級別的考試,單元考試、期中考試和期末考試(這是我學生時代的考試)。

三向查找將能夠從指定的考試級別獲取學生對指定科目的分數。

如下圖所示:

使用 VLOOKUP 函數進行 3 路查找的示例

在上面的示例中,VLOOKUP 函數可以在三個不同的表(單元測試、期中和期末考試)中查找並返回指定學生在指定科目中的分數。

這是單元格 H4 中使用的公式:

=VLOOKUP(G4,CHOOSE(IF(H2="Unit Test",1,IF(H2="Midterm",2,3)),$A$3:$E$7,$A$11:$E$15,$A$19:$E$23),MATCH(H3,$A$2:$E$2,0),0)

此公式使用 CHOOSE 函數來確保引用正確的表。我們來分析一下公式的CHOOSE部分:

CHOOSE(IF(H2=”Unit Test”,1,IF(H2=”Midterm”,2,3)),$A$3:$E$7,$A$11:$E$15,$A$19:$E$23)

公式的第一個參數是 IF(H2=”Unit Test”,1,IF(H2=”Midterm”,2,3)),它檢查單元格 H2 並查看所引用的考試級別。如果是單元測試,則返回 $A$3:$E$7,其中包含單元測試的分數。如果是期中考試,則返回 $A$11:$E$15,否則返回 $A$19:$E$23。

這樣做會使 VLOOKUP 表數組動態化,從而使其成為三向查找。

相關問題  Windows 10 藍牙無法打開 [已經修好了]

示例 5 – 從列表中獲取最後一個值

您可以創建一個 VLOOKUP 公式來獲取列表中的最後一個數值。

您可以在 Excel 中使用的最大正數是 9.99999999999999E+307 。 這也意味著VLOOKUP號中最大的查找號也是一樣的。

我認為您永遠不需要任何涉及如此大數字的計算。這正是我們可以用來獲取列表中最後一個數字的方法。

假設您有一個如下所示的數據集(在 A1:A14 中),並且您想要獲取列表中的最後一個數字。

在 Excel 中使用 VLOOKUP 函數從列表中查找最後一個值

這是您可以使用的公式:

=VLOOKUP(9.99999999999999E+307,$A$1:$A$14,TRUE)

請注意,上面的公式使用近似匹配 VLOOKUP  (注意公式末尾的 TRUE,而不是 FALSE 或 0)。另外,請注意,此 VLOOKUP 公式無需對列表進行排序即可工作。

以下是近似 VLOOKUP 函數的工作原理。它從上到下掃描最左邊的列。

  • 如果找到完全匹配,則返回該值。
  • 如果它找到一個高於查找值的值,則返回其上方單元格中的值。
  • 如果查找值大於列表中的所有值,則返回最後一個值。

在上面的例子中,第三種情況在起作用。

由於9.99999999999999E+307是 Excel 中可以使用的最大數字,因此當它用作查找值時,它會返回列表中的最後一個數字。

同樣,您也可以使用它返回列表中的最後一個文本項。這是可以做到這一點的公式:

=VLOOKUP("zzz",$A$1:$A$8,1, TRUE )

Excel Vlookup 示例最後一個值名稱

遵循同樣的邏輯。Excel 會查看所有名稱,並且由於 zzz 被認為比任何以 zzz 之前的字母開頭的名稱/文本都大,因此它將返回列表中的最後一項。

示例 6 – 使用通配符和 VLOOKUP 進行部分查找

Excel 通配符在許多情況下都非常有用。

正是這種神奇的藥水賦予了你的配方超能力。

當您必須在列表中查找值並且沒有完全匹配時,需要進行部分查找。

例如,假設您有一個如下所示的數據集,並且您想在列表中查找公司 ABC,但列表中有 ABC Ltd 而不是 ABC。

Excel 中的通配符 - 部分查找

您不能使用 ABC 作為查找值,因為 A 列中沒有完全匹配。近似匹配也會導致錯誤的結果,並且需要按升序對列表進行排序。

但是,您可以在 VLOOKUP 函數中使用通配符來獲取匹配項。

在單元格 D2 中輸入以下公式並將其拖動到其他單元格:

=VLOOKUP("*"&C2&"*",$A$2:$A$8,1,FALSE)

使用帶有通配符的 VLOOKUP 查找部分匹配項

這個公式是如何工作的?

在上面的公式中,不是按原樣使用查找值,而是在兩側用通配符星號 (*) – “*”&C2&”*”

星號是 Excel 中的通配符,可以表示任意數量的字符。

在查找值的兩側使用星號告訴 Excel 它需要查找包含 C2 中的單詞的任何文本。它可以在 C2 中的文本之前或之後有任意數量的字符。

例如,單元格 C2 包含 ABC,因此 VLOOKUP 函數會查看 A2:A8 中的名稱並蒐索 ABC。它在單元格 A2 中找到匹配項,因為它包含 ABC Ltd 中的 ABC。ABC 的左側或右側是否有任何字符都沒有關係。在文本字符串中有 ABC 之前,它將被視為匹配項。

注意:VLOOKUP 函數總是返回第一個匹配值並停止進一步查找。因此,如果列表中有 ABC Ltd.和 ABC Corporation,它將返回第一個而忽略其餘的。

示例 7 – 儘管查找值匹配,但 VLOOKUP 返回錯誤

當您看到有匹配的查找值並且 VLOOKUP 函數返回錯誤時,它會讓您發瘋。

例如,在下面的情況下,有一個匹配項 (Matt),但 VLOOKUP 函數仍然返回錯誤。

使用 VLOOKUP 時多餘空格導致錯誤的示例

現在雖然我們可以看到有匹配,但我們用肉眼看不到的是可能有前導或尾隨空格。如果您在查找值之前、之後或之間有這些額外的空格,則它不是完全匹配。

當您從數據庫導入數據或從其他人那裡獲取數據時,通常會出現這種情況。這些前導/尾隨空間有潛入的趨勢。

這裡的解決方案是 TRIM 功能。它刪除任何前導或尾隨空格或單詞之間的額外空格。

這是可以為您提供正確結果的公式。

=VLOOKUP("Matt",TRIM($A$2:$A$9),1,0)

由於這是一個數組公式,請使用 Control + Shift + Enter 而不是 Enter。

另一種方法可能是首先使用 TRIM 函數處理您的查找數組,以確保所有額外的空格都消失了,然後像往常一樣使用 VLOOKUP 函數。

示例 8 – 進行區分大小寫的查找

默認情況下,VLOOKUP 函數中的查找值不區分大小寫。例如,如果您的查找值是 MATT、matt 或 Matt,則對於 VLOOKUP 函數來說都是一樣的。無論大小寫如何,它都會返回第一個匹配值。

但是如果要進行區分大小寫的查找,則需要使用 EXACT 函數和 VLOOKUP 函數。

這是一個例子:

進行區分大小寫的查找

如您所見,三個單元格名稱相同(在 A2、A4 和 A5 中)但字母大小寫不同。在右邊,我們有三個名字(Matt、MATT 和 matt)以及他們在數學中的分數。

現在沒有配備 VLOOKUP 函數來處理區分大小寫的查找值。在上面的例子中,它總是返回 38,這是 Matt 在 A2 中的分數。

相關問題  如何使用 Excel DATEDIF 函數(附示例)

為了使其區分大小寫,我們需要使用輔助列(如下所示):

Excel Vlookup 示例 - 區分大小寫的輔助行

要獲取幫助列中的值,請使用 =ROW() 函數。它只會獲取單元格中的行號。

一旦你有了幫助列,這裡就是給出區分大小寫的查找結果的公式。

=VLOOKUP(MAX(EXACT(E2,$A$2:$A$9)*(ROW($A$2:$A$9))),$B$2:$C$9,2,0)

現在讓我們分解並了解它的作用:

  • EXACT(E2,$A$2:$A$9) – 這部分會將 E2 中的查找值與 A2:A9 中的所有值進行比較。它返回一個 TRUE/FALSE 數組,其中 TRUE 在完全匹配的情況下返回。在這種情況下,它將返回以下數組:{TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}。
  • EXACT(E2,$A$2:$A$9)*(ROW($A$2:$A$9) – 這部分將 TRUE/FALSE 數組與行號相乘。只要有 TRUE,它就會給出行號,否則它給出 0。在這種情況下,它將返回 {2;0;0;0;0;0;0;0}。
  • MAX(EXACT(E2,$A$2:$A$9)*(ROW($A$2:$A$9))) – 這部分返回數字數組中的最大值。在這種情況下,它將返回 2(這是完全匹配的行號)。
  • 現在我們只需將此數字用作查找值並將查找數組用作 B2:C9

注意:由於這是一個數組公式,因此請使用 Control + Shift + Enter 而不是只輸入。

示例 9 – 將 VLOOKUP 與多個條件一起使用

Excel VLOOKUP 函數,在其基本形式中,可以查找一個查找值並從指定的行返回相應的值。

但通常需要在具有多個條件的 Excel 中使用 VLOOKUP。

假設您有一個包含學生姓名、考試類型和數學分數的數據(如下所示):

Excel Vlookup 示例 - 第二次查找

使用 VLOOKUP 函數獲取每個學生在各自考試級別的數學分數可能是一個挑戰。

例如,如果您嘗試將 VLOOKUP 與 Matt 作為查找值一起使用,它將始終返回 91,這是列表中第一次出現 Matt 的分數。要獲得每種考試類型(單元測試、期中考試和期末考試)的 Matt 分數,您需要創建一個唯一的查找值。

這可以使用輔助列來完成。第一步是在分數的左側插入一個輔助列。

Excel Vlookup 示例 - 第二個查找助手

現在,要為每個名稱實例創建一個唯一限定符,請在 C2 中使用以下公式:=A2&”|”&B2

將此公式複製到輔助列中的所有單元格。這將為名稱的每個實例創建唯一的查找值(如下所示):

Excel VLOOKUP-函數示例助手

現在,雖然有重複的名字,但當名字與考試水平結合起來時,就沒有重複了。

這很容易,因為現在您可以使用幫助列值作為查找值。

這是在 G3:I8 中為您提供結果的公式。

=VLOOKUP($F3&"|"&G$2,$C$2:$D$19,2,0)

這裡我們結合了學生姓名和考試級別得到了lookup值,我們使用這個lookup值在helper列中檢查得到匹配的記錄。

Excel VLOOKUP 函數示例公式唯一查找

注意:在上面的例子中,我們使用了 | 在輔助列中加入文本時作為分隔符。在一些非常罕見(但可能)的情況下,您可能有兩個不同的標準,但結合起來最終會給出相同的結果。這是一個例子:

具有多個條件的 VLOOKUP - 為什麼使用分隔符

請注意,雖然 A2 和 A3 不同,B2 和 B3 不同,但組合最終是相同的。但是,如果您使用分隔符,那麼即使組合也會不同(D2 和 D3)。

這是一個關於如何在不使用輔助列的情況下使用具有多個條件的 VLOOKUP 的教程。

示例 10 – 在使用 VLOOKUP 函數時處理錯誤

Excel VLOOKUP 函數在找不到指定的查找值時返回錯誤。如果 VLOOKUP 找不到值,您可能不希望醜陋的錯誤值干擾數據的美觀。

您可以輕鬆刪除帶有任何含義全文的錯誤值,例如“不可用”或“未找到”。

例如,在下面的示例中,當您嘗試在列表中查找 Brad 的分數時,它會返回錯誤,因為 Brad 的名字不在列表中。

Excel Vlookup 示例 - 處理錯誤

要刪除此錯誤並將其替換為有意義的內容,請將 VLOOKUP 函數包裝在 IFERROR 函數中。

這是公式:

=IFERROR(VLOOKUP(D2,$A$2:$B$7,2,0),"Not Found")

IFERROR 函數檢查第一個參數(在本例中為 VLOOKUP 函數)返回的值是否為錯誤。如果不是錯誤,則通過 VLOOKUP 函數返回值,否則返回 Not Found。

Excel Vlookup 示例 - 未找到處理錯誤

IFERROR 函數從 Excel 2007 開始提供。如果您使用的是之前的版本,請使用以下功能:

=IF(ISERROR(VLOOKUP(D2,$A$2:$B$7,2,0)),"Not Found",VLOOKUP(D2,$A$2:$B$7,2,0))

這就是本 VLOOKUP 教程中的內容。

我試圖介紹在 Excel 中使用 Vlookup 函數的主要示例。如果您想查看添加到此列表中的更多示例,請在評論部分告訴我。

相關Excel函數:

  • Excel HLOOKUP 函數。
  • Excel XLOOKUP 函數
  • Excel 索引函數。
  • Excel 間接函數。
  • Excel 匹配函數。
  • Excel 偏移函數。

哦,您好 👋很高興認識你。

訂閱我們的電子報,定期發送很棒的科技內到您的郵

Post Comment