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

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

概述

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

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

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

圖1

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

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

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

• “郭靖”——要查找的值。

• $A$3:$E$10——查找的單元格區(qū)域。注意，Excel在最左列搜索要查找的值，本例中在A3:A10中查找姓名郭靖。

• 2——一旦找到了郭靖，將定位到區(qū)域的第2列，返回郭靖所在行相同行的值。數(shù)值2指定從區(qū)域中的第2列查找成績(jī)。

• 0——告訴VLOOKUP函數(shù)僅查找完全匹配的值。

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

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

圖2

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

圖3

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

例如，查找楊康的化學(xué)成績(jī)，公式為：

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

圖4

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

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

圖5

如果在最左邊的列中沒(méi)有找到查找值，那么返回錯(cuò)誤值#N/A。

示例2：雙向查找

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

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

圖6

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

此時(shí)，需要使用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ù)學(xué)，則返回2。

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

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

一種好的方法是創(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ù)有效性”對(duì)話框中設(shè)置為“序列”，來(lái)源選擇單元格區(qū)域A3:A10。同樣的方法設(shè)置單元格B13的下拉列表。

示例4：三向查找

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

現(xiàn)在，假設(shè)一年中，學(xué)生有三種不同的測(cè)試：?jiǎn)卧獪y(cè)試、期中測(cè)試和期末測(cè)試。那么，三向查找就是從指定測(cè)試中獲取學(xué)生指定科目的成績(jī)。如下圖8所示。

圖8

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

在單元格H4中的公式為：

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

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

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

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

示例5：獲取位于列表最后的值

可以創(chuàng)建VLOOKUP公式來(lái)獲取位于列表最后一個(gè)位置的數(shù)字值。

在Excel中可以使用的最大的正數(shù)是9.99999999999999E 307，這意味著在VLOOKUP函數(shù)中最大的查找數(shù)也是這個(gè)數(shù)。幾乎不會(huì)涉及到如此大的一個(gè)數(shù)的計(jì)算，但可以使用來(lái)獲取列表中最后一個(gè)數(shù)字。

如圖9所示，在單元格區(qū)域A1:A14中有一組數(shù)，想要獲取列表中最后一個(gè)數(shù)，即1514。

圖9

公式為：
=VLOOKUP(9.99999999999999E 307,$A$1:$A$14,1,TRUE)

注意到，公式使用了近似匹配，并且列表也沒(méi)有排序。

下面是使用了近似匹配的VLOOKUP函數(shù)的工作原理。VLOOKUP函數(shù)從頂?shù)降姿阉髯钭髠?cè)的列：

• 如果發(fā)現(xiàn)一個(gè)精確匹配的值，則返回該值。

• 如果發(fā)現(xiàn)一個(gè)高于查找值的值，則返回該值所在單元格上方單元格中的值。

• 如果查找值大于列表中所有的值，則返回最后一個(gè)值。

由于9.99999999999999E 307是Excel中可以使用的最大數(shù)，將該數(shù)用作查找值時(shí)，從列表中返回最后一個(gè)數(shù)字。

同樣的原理也可以用于返回列表中最后一個(gè)文本項(xiàng)。如圖10所示。

圖10

公式為：

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

Excel查找所有的名字，由于zzz比任何文本都大，因此返回列表中最后一個(gè)文本項(xiàng)。