Excel公式與函數(shù)之美23：10個示例讓你的VLOOKUP函數(shù)應用從入門到精通（上）

L羅樂 2018-04-07

展開全文

VLOOKUP函數(shù)是眾多的Excel用戶最喜歡和最常用的函數(shù)之一,，因此介紹VLOOKUP函數(shù)使用技巧的文章也特別多。在《Excel函數(shù)學習4：VLOOKUP函數(shù)》中,，我們學習了VLOOKUP函數(shù)的語法及應用,，在Excel公式與函數(shù)之美前面的系列文章中，我們又詳細探討了VLOOKUP函數(shù)的4個參數(shù),。

熟練掌握VLOOKUP函數(shù)的使用,，是Excel必備技能之一。下面我們通過10個示例,，進一步鞏固VLOOKUP函數(shù)的使用技能,。

概述

VLOOKUP函數(shù)最擅長在列中查找相匹配的數(shù)據(jù)，若找到匹配的數(shù)據(jù),，則在找到的數(shù)據(jù)所在行的右邊從指定的列中獲取數(shù)據(jù),。

示例1：查找郭靖的數(shù)學成績

如圖1所示，在最左邊的列中是學生的姓名,，在列B至列E中是不同科目的成績,。

圖1

現(xiàn)在，我需要從上面的數(shù)據(jù)中找到郭靖的數(shù)學成績,。公式為：

=VLOOKUP('郭靖',$A$3:$E$10,2,0)

公式有4個參數(shù)：

“郭靖”——要查找的值,。
$A$3:$E$10——查找的單元格區(qū)域。注意,，Excel在最左列搜索要查找的值,，本例中在A3:A10中查找姓名郭靖。
2——一旦找到了郭靖,，將定位到區(qū)域的第2列,，返回郭靖所在行相同行的值。數(shù)值2指定從區(qū)域中的第2列查找成績,。
0——告訴VLOOKUP函數(shù)僅查找完全匹配的值,。

以上面的示例來演示VLOOKUP函數(shù)是如何工作的。

首先,，在區(qū)域的最左列查找郭靖,，從頂部到底部查找并發(fā)現(xiàn)在單元格A7中存儲著這個值。

圖2

一旦找到該值,，就會到右邊第2列,，獲取其中的值。

圖3

可以使用相同結(jié)構(gòu)的公式來獲取任意學生任一科目的成績,。

例如,，查找楊康的化學成績，公式為：

=VLOOKUP('楊康',$A$3:$E$10,4,0)

圖4

在上面的示例中，查找值（學生姓名）在公式中是包含在引號中的,，也可以使用包含查找值的單元格引用,。使用單元格引用可以創(chuàng)建動態(tài)公式。

例如,，如果在某單元格中放置要查找的學生姓名，使用公式來查找該學生的數(shù)學成績,，那么當修改學生姓名時,，查找的結(jié)果將自動更新。

圖5

如果在最左邊的列中沒有找到查找值,，那么返回錯誤值#N/A,。

示例2：雙向查找

在示例1中，列數(shù)值采用了“硬編碼”,，使用2作為列索引值,，因此公式總是返回數(shù)學成績。

如果想要查找值和列索引值都是動態(tài)的,，如下圖6所示,，修改學生姓名或者科目時，VLOOKUP函數(shù)獲取相應的成績,。

圖6

要創(chuàng)建雙向查找公式,，需要使列也是動態(tài)的。這樣,，當用戶修改科目時,，公式自動獲取正確的列，例如數(shù)學是第2列,，物理是第3列,。

此時，需要使用MATCH函數(shù)作為列參數(shù),，公式為：

=VLOOKUP(A14,$A$3:$E$10,MATCH(B13,$A$2:$E$2,0),0)

公式中使用MATCH(B13,$A$2:$E$2,0)作為列的數(shù)值,。MATCH函數(shù)接受科目作為查找值（單元格B13），返回該值在A2:E2中的位置,。因此,，如果查找數(shù)學，則返回2,。

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

在上面的示例中,，我們手工輸入數(shù)據(jù)，耗時且易出錯,，特別是有許多查找值時,。

一種好的方法是創(chuàng)建查找值列表，然后只需從列表中選擇即可,。

圖7

在單元格B14中的公式仍然為：

=VLOOKUP(A14,$A$3:$E$10,MATCH(B13,$A$2:$E$2,0),0)

查找值在下拉列表中,，這些下拉列表是使用Excel的數(shù)據(jù)有效性功能創(chuàng)建的,。選擇單元格A14，單擊“數(shù)據(jù)——數(shù)據(jù)有效性”,，在“數(shù)據(jù)有效性”對話框中設(shè)置為“序列”,，來源選擇單元格區(qū)域A3:A10。同樣的方法設(shè)置單元格B13的下拉列表,。

示例4：三向查找

在示例2中,，使用了一個包含不同學科學生成績的查找表，是一個使用兩個變量（學生姓名和學科名稱）雙向查找學生成績的示例,。

現(xiàn)在,，假設(shè)一年中，學生有三種不同的測試：單元測試,、期中測試和期末測試,。那么，三向查找就是從指定測試中獲取學生指定科目的成績,。如下圖8所示,。

圖8

在圖8的示例中，VLOOKUP函數(shù)可以查找三個不同的表（單元測試,、期中測試和期末測試）,，返回其中某學生的某學科的成績。

在單元格H4中的公式為：

=VLOOKUP(G4,CHOOSE(IF(H2='單元測試',1,IF(H2='期中測試',2,3)),$A$3:$E$7,$A$11:$E$15,$A$19:$E$23),MATCH(H3,$A$2:$E$2,0),0)

公式使用CHOOSE函數(shù)來確定要引用的表,。公式中的CHOOSE函數(shù)為：

CHOOSE(IF(H2='單元測試',1,IF(H2='期中測試',2,3)),$A$3:$E$7,$A$11:$E$15,$A$19:$E$23)

第1個參數(shù)是IF(H2='單元測試',1,IF(H2='期中測試',2,3)),，檢查單元格H2中的值，返回要選擇各類測試表所對應的數(shù)值,。如果是“單元測試”,，則返回1，CHOOSE函數(shù)返回單元格區(qū)域$A$3:$E$7,；如果是“期中測試”,，則返回2，否則返回3,，分別對應著單元格區(qū)域$A$11:$E$15和$A$19:$E$23,。