VLOOKUP函數(shù)的使用方法-vloolup函數(shù)

1、VLOOKUP函數(shù)的使用方法（入門級）一、入門級VLOOKUP是一個查找函數(shù)，給定一個查找的目標，它就能從指定的查找區(qū)域中查找返回想要查找到的值。它的基本語法為：VLOOKUP（查找目標，查找范圍，返回值的列數(shù)，精確OR模糊查找）下面以一個實例來介紹一下這四個參數(shù)的使用例1：如下圖所示，要求根據(jù)表二中的姓名，查找姓名所對應的年齡。B13.氏=VLOOKUP(A13J0)ABDE1表j廣編號性別號1男204?南星男1S-53L玄30E4張新遠男75男4526509101112姓名年齡113南星1乩14李玉成451匚公式：B13二VLOOKUP（A13,$B$2:$D$8,3,0）參數(shù)說明：1查找

2、目標：就是你指定的查找的內(nèi)容或單元格引用。本例中表二A列的姓名就是查找目標。我們要根據(jù)表二的“姓名”在表一中A列進行查找。公式：B13二VLOOKUP(A13,$B$2:$D$&3,0)2查找范圍(VLOOKUP(A13,$B$2:$D$8,3,0)指定了查找目標，如果沒有說從哪里查找，EXCEL肯定會很為難。所以下一步我們就要指定從哪個范圍中進行查找。VLOOKUP的這第二個參數(shù)可以從一個單元格區(qū)域中查找，也可以從一個常量數(shù)組或內(nèi)存數(shù)組中查找。本例中要從表一中進行查找，那么范圍我們要怎么指定呢？這里也是極易出錯的地方。大家一定要注意，給定的第二個參數(shù)查找范圍要符合以下條件才不會出錯：A查找目

3、標一定要在該區(qū)域的第一列。本例中查找表二的姓名，那么姓名所對應的表一的姓名列，那么表一的姓名列（列）一定要是查找區(qū)域的第一列。象本例中，給定的區(qū)域要從第二列開始，即$B$2:$D$8,而不能是$人$2:$D$8。因為查找的“姓名”不在$A$2:$D$8區(qū)域的第一列。B該區(qū)域中一定要包含要返回值所在的列，本例中要返回的值是年齡。年齡列（表一的D列）一定要包括在這個范圍內(nèi)，即：$B$2:$D$8，如果寫成$B$2:$C$8就是錯的。3返回值的列數(shù)（B13二VL00KUP（A13,$B$2:$D$8,3,0）。這是VLOOKUP第3個參數(shù)。它是一個整數(shù)值。它怎么得來的呢。它是“返回值”在第二個參數(shù)給

4、定的區(qū)域中的列數(shù)。本例中我們要返回的是“年齡”，它是第二個參數(shù)查找范圍$B$2:$D$8的第3列。這里一定要注意，列數(shù)不是在工作表中的列數(shù)（不是第4列），而是在查找范圍區(qū)域的第幾列。如果本例中要是查找姓名所對應的性別，第3個參數(shù)的值應該設置為多少呢。答案是2。因為性別在$B$2:$D$8的第2列中。4精確OR模糊查找（VLOOKUP（A13,$B$2:$D$8,3,），最后一個參數(shù)是決定函數(shù)精確和模糊查找的關鍵。精確即完全一樣，模糊即包含的意思。第4個參數(shù)如果指定值是0或FALSE就表示精確查找，而值為1或TRUE時則表示模糊。這里蘭色提醒大家切記切記，在使用VLOOKUP時千萬不要把這個參數(shù)

5、給漏掉了，如果缺少這個參數(shù)默為值為模糊查找，我們就無法精確查找到結果了。VLOOKUP函數(shù)的使用方法（初級篇）一、VLOOKUP多行査找時復制公式的問題VLOOKUP函數(shù)的第三個參數(shù)是查找返回值所在的列數(shù)，如果我們需要查找返回多列時，這個列數(shù)值需要一個個的更改，比如返回第2列的，參數(shù)設置為2，如果需要返回第3列的，就需要把值改為3。如果有十幾列會很麻煩的。那么能不能讓第3個參數(shù)自動變呢？向后復制時自動變?yōu)?，3，4，5。在EXCEL中有一個函數(shù)COLUMN,它可以返回指定單元格的列數(shù)，比如=COLUMNS（A1）返回值1二COLUMNS(Bl)返回值2而單元格引用復制時會自動發(fā)生變化，即Al隨

6、公式向右復制時會變成B1,Cl,D1。這樣我們用COLUMN函數(shù)就可以轉換成數(shù)字1,2，3,4。例：下例中需要同時查找性別，年齡，身高，體重。B13矗=VLOOKUP(IAl3,$B$2：?F?E,COLW(B10)ABDE.7F百12編號姓名-性別年齡了1剜原男.凱1-781004”南星1.6770531吳胳1.675964張新遠男/.20-1.?邛75男*451；:019086張成女/廠301.59609/10/11表二：/L2姓名性別X年齡身高體垂13南星II男1101.677014李玉戰(zhàn)舟451/3190公式：二VLOOKUP($A13,$B$2:$F$8,0)公式說明：這里就是使用C

7、OLUMN(B1)轉化成可以自動遞增的數(shù)字。二、VLOOKUP查找出現(xiàn)錯誤值的問題。1、如何避免出現(xiàn)錯誤值。EXCEL2003在VLOOKUP查找不到，就#N/A的錯誤值，我們可以利用錯誤處理函數(shù)把錯誤值轉換成0或空值。即:=IF(ISERROR(VLOOKUP(參數(shù)略),VLOOKUP(參數(shù)略)EXCEL2007,EXCEL2010中提供了一個新函數(shù)IFERROR，處理起來比EXCEL2003簡單多了。IFERROR(VLOOKUP()，)2、VLOOKUP函數(shù)查找時出現(xiàn)錯誤值的幾個原因A、實在是沒有所要查找到的值B、查找的字符串或被查找的字符中含有空格或看不見的空字符，驗證方法是用二號對比

8、一下，如果結果是FALSE，就表示兩個單元格看上去相同,其實結果不同。C、參數(shù)設置錯誤。VLOOKUP的最后一個參數(shù)沒有設置成1或者是沒有設置掉。第二個參數(shù)數(shù)據(jù)源區(qū)域，查找的值不是區(qū)域的第一列，或者需要反回的字段不在區(qū)域里，參數(shù)設置在入門講里已注明，請參閱。D、數(shù)值格式不同，如果查找值是文本，被查找的是數(shù)字類型，就會查找不到。解決方法是把查找的轉換成文本或數(shù)值，轉換方法如下：文本轉換成數(shù)值：*1或一或/1數(shù)值轉抱成文本：&VLOOKUP函數(shù)的使用方法（進階篇）一、字符的模糊查找在A列我們知道如何查找型號為“AAA”的產(chǎn)品所對應的B列價格，即：二VLOOKUP(C1,A:B,2,0)如果我們需要

9、查找包含“AAA”的產(chǎn)品名稱怎么表示呢？如下圖表中所示。垂=VLOOKUPABd4DE1點口廠口口價格21/$BRE-5.34SFEW-3245t5AAA-22,6WEWEE-2F178/a.-血1=1,1口口10AAA611公式二VLOOKUP(*&A10&*，A2:B6,2,0)公式說明：VLOOKUP的第一個參數(shù)允許使用通配符“*”來表示包含的意思，把*放在字符的兩邊，即*&字符&*。二、數(shù)字的區(qū)間查找數(shù)字的區(qū)間查找即給定多個區(qū)間，指定一個數(shù)就可以查找出它在哪個區(qū)間并返回這個區(qū)間所對應的值。在VLOOKUP入門中我們提示VLOOKUP的第4個參數(shù)，如果為0或FALSE是精確查找，如果是1

10、或TRUE或省略則為模糊查找，那么實現(xiàn)區(qū)間查找正是第4個參數(shù)的模糊查找應用。首先我們需要了解一下VLOOKUP函數(shù)模糊查找的兩個重要規(guī)則：1、引用的數(shù)字區(qū)域一定要從小到大排序。雜亂的數(shù)字是無法準確查找到的。如下面A列符合模糊查找的前題，B列則不符合。AB12342泌673畀24TS54E99S9備符合條件不符合荼件J-J11111IV口11fa2、模糊查找的原理是：給一定個數(shù)，它會找到和它最接近，但比它小的那個數(shù)。詳見下圖說明。最后看一個實例:例：如下圖所示，要求根據(jù)上面的提成比率表，在提成表計算表中計算每個銷售額的提成比率和提成額。B11-=VLOOKUP(All,陋$囂$開孔力A-B1C聲

11、D1提成比率表2銷售額比率表示區(qū)間30大干等干0小干10000L410000大干籌干ioooob=F30000530030翁大干等于站DDD小干牡時口6400004%大;了等:?-40000小干50000)7.200000535大200000f飛提成計算義10銷售額L提成比率1150001時廣5012565000.041乳1S9000.025014500000：-.D.=JQ5250001R公式：二VLOOKUP(A11,$A$3:$B$7,2)公式說明：1、上述公式省略了VLOOKUP最后一個參數(shù)，相當于把第四個參數(shù)設置成1或TRUE。這表示VLOOKUP要進行數(shù)字的區(qū)間查找。2、圖中公式中

12、在查找5000時返回比率表0所對應的比率1%,原因是0和10000與5000最接近，但VLOOKUP只選比查找值小的那一個，所以公式會返回0所對應的比率1%。VLOOKUP函數(shù)的使用方法（高級篇）一、VLOOKUP的反向查找。一般情況下，VLOOKUP函數(shù)只能從左向右查找。但如果需要從右向右查找，則需要把區(qū)域進行“乾坤大挪移”，把列的位置用數(shù)組互換一下。例1：要求在如下圖所示表中的姓名反查工號。E9矗=VLOOKUP(A9?IF(B2A2:k5)05Ac.#D1工號姓名r2A001張/衣臉傭趙二/4拭03：暢五/5A004孫二/67/盟:姓名工號Z9趙三lAO.OgII1011公式：二VL00

13、KUP（A9,IF（l,0,B2:B5,A2:A5）,2,0）公式剖析：1、這里其實不是VLOOKUP可以實現(xiàn)從右至右的查找，而是利用IF函數(shù)的數(shù)組效應把兩列換位重新組合后，再按正常的從左至右查找。2、IF（1,0,B2:B5,A2:A5）這是本公式中最重要的組成部分。在EXCEL函數(shù)中使用數(shù)組時（前提時該函數(shù)的參數(shù)支持數(shù)組），返回的結果也會是一個數(shù)組。這里1和0不是實際意義上的數(shù)字，而是1相關于TRUE，0相當于FALSE，當為1時，它會返回IF的第二個參數(shù)（B列），為0時返回第二個參數(shù)（A列）。根據(jù)數(shù)組運算返回數(shù)組，所以使用IF后的結果返回一個數(shù)組（非單元格區(qū)域）：張一，A001；趙三，A

14、002；楊五，A003；孫二，A004二、VLOOKUP函數(shù)的多條件查找。VLOOKUP函數(shù)需要借用數(shù)組才能實現(xiàn)多條件查找。例2：要求根據(jù)部門和姓名查找C列的加班時間。C勺干1鼻=VLOOKUP（A9&B9.IF（1,貯戰(zhàn)缸Z:玷2兀5）%Q)ABCDEFG1姓名加班時間2強一1-3.趙二54楊五35銷售趙二6678工號姓名加班時間趙三5.1H分析：我們可以延用例1的思路，我們的努力方向不是讓VLOOKUP本身實現(xiàn)多條件查找，而是想辦法重構一個數(shù)組。多個條件我們可以用&連接在一起，同樣兩列我們也可以連接成一列數(shù)據(jù)，然后用IF函數(shù)進行組合。公式：=VLOOKUP(A9&B9,IF(1,0,A2:

15、A5&B2:B5,C2:C5),2,0)公式剖析：1、A9&B9把兩個條件連接在一起。把他們做為一個整體進行查找。2、A2:A5&B2:B5,和條件連接相對應，把部分和姓名列也連接在一起，作為一個待查找的整體。3、IF(1,0,A2:A5&B2:B5,C2:C5)用IF(1，0把連接后的兩列與C列數(shù)據(jù)合并成一個兩列的內(nèi)存數(shù)組。按F9后可以查看的結果為：銷售張一，1;銷售趙三,5;人事楊五,3;銷售趙三,64、完成了數(shù)組的重構后，接下來就是VLOOKUP的基本查找功能了，另外公式中含有多個數(shù)據(jù)與多個數(shù)據(jù)運算(A2:A5&B2:B5),所以必須以數(shù)組形式輸入，即按ctrl+shift后按ENTER

16、結束輸入。三、VLOOKUP函數(shù)的批量查找。VLOOKUP一般情況下只能查找一個，那么多項該怎么查找呢？例3要求把如圖表中所有張一的消費金額全列出來A1日期姓名E肖費額2.32011-3-23：100二242011-2-3k20052011-Z-4三4462011-2-5張100078.9張一111001110200111100013分析：經(jīng)過前面的學習，我們也有這樣一個思路，我們在實現(xiàn)復雜的查找時，努力的方向是怎么重構一個查找內(nèi)容和查找的區(qū)域。要想實現(xiàn)多項查找，我們可以對查找的內(nèi)容進行編號，第一個出現(xiàn)的是后面連接1,第二個出現(xiàn)的連接2ooo公式：=VL00KUP(B$9&ROW(A1),IF(1,O,$B$2:$B$6&COUNTIF(INDIRECT(b2:b&ROW($2:$6),B$9),$C$2:$C$6),2,)公式剖析：1、B$9&ROW(A1)連接序號，公式向下復制時會變成B$9連接1,2，32、給所有的張一進行編號。要想生成編號，就需要生成一個不斷擴充的區(qū)域(INDIRECT(b2