10個(gè)示例讓你的VLOOKUP函數(shù)應(yīng)用從入門到精通

1、10個(gè)示例讓你的VLOOKUP數(shù)應(yīng)用從入門到精通VLOOKlffl數(shù)是眾多的Excel用戶最喜歡和最常用的函數(shù)之一，因此介紹VLOOKUP 函數(shù)使用技巧的文章也特別多。在Excel函數(shù)學(xué)習(xí)4: VLOOKUP數(shù)中，我 們學(xué)習(xí)了 VLOOKUP數(shù)的語法及應(yīng)用，在Excel公式與函數(shù)之美前面的系列文章 中，我們又詳細(xì)探討了 VLOOKUP數(shù)的4個(gè)參數(shù)。熟練掌握VLOOKUP數(shù)的使用，是Excel必備技能之一。下面我們通過10個(gè)示 例，進(jìn)一步鞏固VLOOKUP數(shù)的使用技能。一鍵直達(dá)>> Excel函數(shù)學(xué)習(xí)4: VLOOKUP數(shù)一鍵直達(dá)>> Excel公式與函數(shù)之美15: VL

2、OOKUP數(shù)的秘密一鍵直達(dá)>> Excel公式與函數(shù)之美19:理解VLOOKUP數(shù)第4個(gè)參數(shù)的真實(shí)含 義一鍵直達(dá)>> Excel公式與函數(shù)之美20: MATCIa數(shù)使VLOOKUP數(shù)更靈活高效一鍵直達(dá)>> Excel公式與函數(shù)之美21: INDIRECT函數(shù)使VLOOKUP數(shù)實(shí)現(xiàn)多表 查詢一鍵直達(dá)>>Excel公式與函數(shù)之美22: VLOOKUP數(shù)查找技巧概述VLOOKUP數(shù)最擅長在列中查找相匹配的數(shù)據(jù)，若找到匹配的數(shù)據(jù)，則在找到的 數(shù)據(jù)所在行的右邊從指定的列中獲取數(shù)據(jù)。示例 1：查找郭靖的數(shù)學(xué)成績?nèi)鐖D1所示，在最左邊的列中是學(xué)生的姓名，在列B至

3、列E中是不同科目的成績ECDEF科目2姓名數(shù)學(xué)物理優(yōu)學(xué)生物3張無忌385866494周芷若88927490p557779191謝遜82564595郭造555565758黃蓉44698090§楊康7551578410程念慈3837515611.亍j號號：三式上以打花二12圖1現(xiàn)在，我需要從上面的數(shù)據(jù)中找到郭靖的數(shù)學(xué)成績。公式為：=VLOOKUP鄱靖",$A$3:$E$10,2,0)公式有4個(gè)參數(shù)：“郭靖” 要查找的值。$A$3:$E$10-查找的單元格區(qū)域。注意，Excel在最左列搜索要查找的 值，本例中在A3:A10中查找姓名郭靖。2一旦找到了郭靖，將定位到區(qū)域的第 2歹I

4、，返回郭靖所在行相同行 的值。數(shù)值2指定從區(qū)域中的第2列查找成績。0告訴VLOOKU函數(shù)僅查找完全匹配的值。以上面的示例來演示VLOOKU函數(shù)是如何工作的。首先，在區(qū)域的最左列查找郭靖，從頂部到底部查找并發(fā)現(xiàn)在單元格A7中存儲著這個(gè)值。;1A. BC 1 口 科目2鞋名數(shù)學(xué)物理化學(xué)生物3張無忌"385866494周芷若88927490567趙敏57779191謝遜1182564595郭靖555565758.電答446980909楊康7551579410穆念慈3837515G111 9'；七1三字：士/匚弓1。*走!，1!：一旦找到該值，就會到右邊第2歹I，獲取其中的值IU-口

5、 IAECDE1科目2鞋名數(shù)學(xué)物理化學(xué)生物3張無忌1385866494周芷君889274905趙敬577791916謝遜 j825645957郭靖 L5565758黃容6980909楊康7551573410穆念慈3837515G11先宣號：一延營5欣立1 7圖3可以使用相同結(jié)構(gòu)的公式來獲取任意學(xué)生任一科目的成績例如，查找楊康的化學(xué)成績，公式為：=VLOOKUP楊康”,$A$3:$E$10,4,0)ACDE1科自2姓名數(shù)學(xué)物理化學(xué)生物I3張無忌，38586649周芷若S3927490趙敬57779191謝遜825645究7郭靖555565758黃應(yīng)4469909析康一8410穆念慈3837561

6、1圖4在上面的示例中，查找值（學(xué)生姓名）在公式中是包含在引號中的，也可以使用 包含查找值的單元格引用。使用單元格引用可以創(chuàng)建動態(tài)公式。例如，如果在某單元格中放置要查找的學(xué)生姓名， 使用公式來查找該學(xué)生的數(shù)學(xué) 成績，那么當(dāng)修改學(xué)生姓名時(shí)，查找的結(jié)果將自動更新。12ABCD科目Ef姓名數(shù)學(xué)物理化學(xué)生物3張無忌335866494周芷君8S9274905趙敏577791916謝遜825645957B郭靖55556575黃蓉r姓r 6980909楊康7551578410程念慈3S37515611 12_ 1314151郭靖 155圖5如果在最左邊的列中沒有找到查找值，那么返回錯(cuò)誤值 #N/A,示例2:雙

7、向查找在示例1中，列數(shù)值采用了 “硬編碼”，使用2作為列索引信，因此公式總是返 回?cái)?shù)學(xué)成績。如果想要查找值和列索引值都是動態(tài)的， 如下圖6所示，修改學(xué)生姓名或者科目 時(shí)，VLOOKUP數(shù)獲取相應(yīng)的成績。AEGDEf12科目姓名數(shù)學(xué)物理化學(xué)生物3張無忌335366494周芷君889274905越敏577791916謝遜82564595TB郭靖55556575由客44r 6980909楊康7551578410程念慈3S375156111213生物14am7515圖6要創(chuàng)建雙向查找公式，需要使列也是動態(tài)的。這樣，當(dāng)用戶修改科目時(shí)，公式自 動獲取正確的列，例如數(shù)學(xué)是第 2歹I，物理是第3歹I。此時(shí)，需

8、要使用MATCI®數(shù)作為列參數(shù)，公式為：=VLOOKUP(A14,$A$3:$E$10,MATCH(B13,$A$2:$E$2,0),0)公式中使用MATCH(B13,$A$2:$E$2,0淮為歹1白數(shù)值。MATCI®數(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,$

9、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中，使用了一個(gè)包含不同學(xué)科學(xué)生成績的查找表，是一個(gè)使用兩個(gè)變量(學(xué)生姓名和學(xué)科名稱)雙向查找學(xué)生成績的示例?，F(xiàn)在，假設(shè)一年中，學(xué)生有三種不同的測試：單元測試、期中測試和期末測試。那么，三向查找就是從指定測試中獲取學(xué)生指定科目的成績。如下圖 8所示。AECDEFGHI1單元熟忒一科B2鑫君數(shù)學(xué)物理優(yōu)學(xué)生物|單元測試

10、I丁3張無忌38586649物理 _卜4周芷若88927490周芷若925趙敏P 577791916謝遜825645957郭靖55556575S9期中勰、試科目10姓冬數(shù)學(xué)物理化學(xué)生物11張無忌714150891213周足若86914174趙敏4166855514謝遜4553769515郭靖637368721617期末勰試-科目10程名數(shù)學(xué)物理化學(xué)生物19張無忌5183805020周芷巖7146585121趙敏8964595322謝遜4773485323郭靖5064507524圖8在圖8的示例中，VLOOKUP數(shù)可以查找三個(gè)不同的表(單元測試、期中測試和 期末測試)，返回其中某學(xué)生的某學(xué)科的成

11、績。在單元格H4中的公式為：=VLOOKUP(G4,CHOOSE(IF(H21元測試",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ù)來確定要引用的表。公式中的 CHOOS函數(shù)為：CHOOSE(IF(H2=單元測試",1,IF(H2="期中測試”,2,3),$A$3:$E$7,$A$11:$E$15,$A$19:$E$23)第1個(gè)參數(shù)是IF(H2="單元測試”,1,IF(H2="期中測試”,2,3),檢

12、查單元格H2中的值，返回要選擇各類測試表所對應(yīng)的數(shù)值。如果是“單元測試”，則返回1,CHOOSE數(shù)返回單元格區(qū)域$A$3:$E$7;如果是“期中測試”，則返回 2,否則 返回3,分別對應(yīng)著單元格區(qū)域$人$11:$£$15和$人$19:$£$23。示例5:獲取位于列表最后的值可以創(chuàng)建VLOOKUP式來獲取位于列表最后一個(gè)位置的數(shù)字值。在Excel中可以使用的最大的正數(shù)是 9.99999999999999E+307這意味著在VLOOKU畫數(shù)中最大的查找數(shù)也是這個(gè)數(shù)。幾乎不會涉及到如此大的一個(gè)數(shù)的計(jì) 算，但可以使用來獲取列表中最后一個(gè)數(shù)字。如圖9所示，在單元格區(qū)域A1:A14中有一組數(shù)，想要獲取列表中最后一個(gè)數(shù)， 即 1514。ABCD1594682理 5723432524194M2587678665258公式為：=VLOOKUP(9.99999999999999E+307,$A$1:$A$14,1,TRUE)注意到，公式使用了近似匹配，并且列表也沒有排序。下面是使用了近似匹配的VLOOKU P數(shù)的工作原理。VLOOKU函數(shù)從頂?shù)降姿阉?最左側(cè)的列：如果發(fā)現(xiàn)一個(gè)精確匹配的值，則返回該值。如果發(fā)現(xiàn)一個(gè)高于查找值的值，則返回該值所在單元格上方單元格中的 值。如果查找值大于