VLOOKUP函數(shù)最常用的10種用法

VLOOKUP函數(shù)最常用用法VLOOKUP函數(shù)是工作中最常用的一種查找函數(shù)，掌握好VLOOKUP函數(shù)能夠極大提高工作的效率。VLOOKUP函數(shù)用于首列查找并返回指定列的值，字母“V”表示垂直方向。VLOOKUP函數(shù)的語法如下：VLOOKUP（lookup_value，table_array，col_index_num，[range_lookup]）其中，第1參數(shù)lookup_value為要搜索的值，第2參數(shù)table_array為首列可能包含查找值的單元格區(qū)域或數(shù)組，第3參數(shù)col_index_num為需要從table_array中返回的匹配值的列號，第4參數(shù)range_lookup用于指定精確匹配或近似匹配模式。當range_lookup為TRUE、被省略或使用非零數(shù)值時，表示近似匹配模式，要求table_array第一列中的值必須按升序排列，并返回小于等于lookup_value的最大值對應列的數(shù)據(jù)。當參數(shù)為FALSE時（常用數(shù)字0或保留參數(shù)前的逗號代替），表示只查找精確匹配值，返回table_array的第一列中第一個找到的值，精確匹配模式不必對table_array第一列中的值進行排序。如果使用精確匹配模式且第1參數(shù)為文本，則可以在第1參數(shù)中使用通配符問號（？）和星號（*）。VLOOKUP函數(shù)不區(qū)分字母大小寫。案例一A3:B7單元格區(qū)域為字母等級查詢表，表示60分以下為E級、60~69分為D級、70~79分為C級、80~89分為B級、90分以上為A級。D:G列為初二年級1班語文測驗成績表,如何根據(jù)語文成績返回其字母等級？在H3:H13單元格區(qū)域中輸入=VLOOKUP（G3，$A$3:$B$7,2）開發(fā)工提卷茅闌注恐房國切承音司就行S合并后盾平"眼復制-123456.7S9101112開發(fā)工提卷茅闌注恐房國切承音司就行S合并后盾平"眼復制-123456.7S910111213姓名109110111101?q初二年級1班語文測輯成績表直詢表男女女女緩等別性王小麗102王寶勤103揚玉萍W4田東會105陳雪蛟106薪/豐107黎梅佳10S張興馬道春癡?一王冰妍女翼3DS5女49907342E女A女奧CE79C91k4SE100A64D案例二在Sheetl里面如何查找折舊明細表中對應編號下的月折舊額？（跨表查詢）在Sheet1里面的C2:C4單元格輸入=VLOOKUP（A2,折舊明細表!A$2:$G$12,7,0）ABC D E.F G固定資產(chǎn)折舊明細表編導 費之名利..購第日期: 一原值一力/值東使用年底月折舊兼電動門2010/03/05IQ&j。如的6b端/Q1mo2費軍[201^02^3151.0Q0.QC飛52.433.33mo3息普電腦12007/05/105.025.003132.47mo4交聯(lián)機組2010/01/16i酒仇02.00洋10L璃［蠹33TTO05桑塔納轎車20ifl/D5/30115,W.005W8L147.$2mo6春蘭空調(diào)2010/03/0511,￡23.50郎4230.05"荷7播房2010/02/.W2, 000.00淵萬843軋門vrooe打印機2010/01/112,600.009%451.46mos算印機2005/12/10_3±450,00_5%L由25拘，公稟2010/04/091,2S9.00珊333.普ABC編號..演產(chǎn)名稱月折舊額［幺YT001電動門1的工0073YT005桑塔納轎與1147-9174YT008打印機51.45過案例三如何實現(xiàn)通配符查找？在B2:B7區(qū)域中輸入公式=VLOOKUP（A2&"*",折舊明細表!$B$2:$G$12,6,0）A B C 口 E. F G1 固定資產(chǎn)折舊明細表編導一聾晝密林一胞囂日朗: …原溫一讖a值率使用年眠月折應款VW1電動門2010/03/05IQOj0362打h3鈍.QIYT902費車[2010/02/0815-1,000.005Z,438,33VTW3感普電腦2007/05/105.025.003132.47mo4交聯(lián)機殂2010/01/16L淞曲d00101比13工33TTO05桑塔船轎車2Cifl/D3/30115,W.00崩sL147.$2mo6春蘭空遇2010/03/05111623.50郭4230.05E>07樓房&C10/02/.W22M⑩施淵20a,喇以17YTOOS打印機2010/01/112heoo.oo5*451.46VT況9復印機2005/12^10%450.009%L阻國YTMO辦公桌FZO10704/091r2S9,OO珊333.普資產(chǎn)名稱月折舊額資產(chǎn)名稱月折舊額Z電動

支貨車

4惠普1399.0072438.333.132.47225交聯(lián)Z電動

支貨車

4惠普6桑塔納1147.917

7:春蘭：230.04S4案例四如何實現(xiàn)模糊查找？在F1:F9區(qū)域中輸入公式=VLOOKUP(E2,$A$2:$B$7,2,1)A E C D ￡等紐如分提成比例■■悄售人員 匯W提威比例提成金藪2其口。。0400a00060000008gmQQ10000W等紐如分提成比例■■悄售人員 匯W提威比例提成金藪2其口。。0400a00060000008gmQQ10000W0 5,00%10.QOH15.00%20,00%2dm30.OOM3683S55,§g132^432.CQ1&409190.005677803.CO0.252419451813C83S5.50.3翁加63。0,34^22757招網(wǎng)93&09 0.252QS273501320.CO 525227533021S4S537.C0 0,3/5465皿227QC90.C0 51 227009案例五如何通過數(shù)值查找文本數(shù)據(jù)、通過文本查找數(shù)值數(shù)據(jù)、同時實現(xiàn)數(shù)值與文本數(shù)據(jù)混合查找?通過數(shù)值查找文本數(shù)據(jù)：在F3:F6區(qū)域中輸入公式=VLOOKUP(E3&””,$A$2:$C$6,3,0)通過文本查找數(shù)值數(shù)據(jù)：在F11:F13區(qū)域中輸入公式=VLOOKUP(--E11,$A$10:$C$14,3,0)同時實現(xiàn)數(shù)值與文本數(shù)據(jù)混合查找：在F19:F21區(qū)域中輸入公式=IF(ISNA(VLOOKUP(E19*1,$A$18:$C$22,3,0)),VLOOKUP(E19&””,$A$18:$C$22,3,0),VLOOKUP(E19*1,$A$18:$C$22,3,0))

AECDE1編號發(fā)貨地點出貨數(shù)量根據(jù)編號查找出貨量21001上海95編號出貨量3^002上海64,100^10^4100S上海10.31004115rlW4上海111M59610：05上再97B9編號發(fā)貨地點出貨數(shù)量根據(jù)編號查找出貨量10上看編罟出貨蚩1110.02上海64^00310312100S上海103*1004 111131004上看1110059141?：05上海9151617編號發(fā)貨地點出貨數(shù)量根據(jù)編號查找出貨量18疝上海95編號|出貨量1191002上海64iooa-10J泣_1003上海10310041121100.4上海111005 1221005上海g案例六在Excel中錄入數(shù)據(jù)信息時，為了提高工作效率，用戶希望通過輸入數(shù)據(jù)的關(guān)鍵字后，自動顯示該記錄的其余信息，例如，輸入員工工號自動顯示該員工的信命，輸入物料號就能自動顯示該物料的品名、單價等。如圖所示為某單位所有員工基本信息的數(shù)據(jù)源表，在“2010年3月員工請假統(tǒng)計表”工作表中，當在A列輸入員工工號時，如何實現(xiàn)對應員工的姓名、身份證號、部門、職務、入職日期等信息的自動錄入？解決方案1：使用VLOOKUP+MATCH函數(shù)在“2010年3月員工請假統(tǒng)計表”工作表中選擇B3:F8單元格區(qū)域，輸入下列公式，按【Ctrl+Enter】組合鍵結(jié)束。=IF($A3="","",VLOOKUP($A3,員工基本信息!$A:$H,MATCH(B$2,員工基本信息!$2:$2,0),0))解決方案2:HLOOKUP+MATCH函數(shù)。在“2010年3月員工請假統(tǒng)計表”工作表中選擇B3:F8單元格區(qū)域，輸入下列公式，按【Ctrl+Enter】組合鍵結(jié)束=IF($A3="","",HLOOKUP(B$2,員工基本信息!$A$2:$H$20,MATCH($A3,員工基本信B!$A$2:$A$20,0),0))

ABC0F二1員工整本箱知表2「工號便別號爆證號人施白明部「1學歷3A0001女ME1弱197M9必。州12Q0?/6/l建秘書本科A0002風里55212375090200312003/4/1X?會亭而土AGO?迎_Jr蚪Mimag丁如前如的120^7/2/16行就食員本日4A0004蓮夢羯女0422021^7-409020t>312006/1L/2C后前壬蜜大專7AgJS聲展女尸噎能笈1982120田。穌0卻曲九，14珊發(fā)工程仲大寺9A&O&b苣圍強口6421201972090200^22^7/3711.市場-林弘本科9AW(JT王簫叟F552120197U102WK?3朗⑹人事還理中專10A0008王適噫女/6931201973081200312007/1/7錦文員碩士11麗麗累XWZ聰LW71MWW因前低為幾工提坤本科12ASOLO任理克4522231^9U21fiOOSO2006/11/11市場大寺13aooli李曲^9312019^lt;]7/3/17iii文員大專14張麗玲一女Fh42205.lS73O402.OOJl2的川口,1日A?王宜本科15AtOLS臨天明雪45222機鈍312王砧220<3S/ll/6工程工程毋甲專16A0014王璞玉64212O19760905O&323006/0730后勤期總破土17JL001.5韓小小55212C1975110200332010/3726問發(fā)制笠器本科ISAma張君寶男6422021^79100200(31ZW7/S/14市場工程師本科1勺「帕lT秦至1畀45腔髓1找觀力就品?！?66/3/6人不副經(jīng)理大專20W1S強市■更F啟9312Q19『加勵2009/1L/2S而瑜工垂說大雪，212223■94員工謝,思?啦配霖| ?島 后 GD E JG H1加1Q年3月員工講低旎計表21工號姓名 身驚證號部門杷免人昭舊期?圖1假天被13A0001變夢娟0?202L5740902C03J后勤主管2006/11/20公差34面漪包繪Q931E01野號。呂。加守氫行就文景2007^2/K事假15A皿5,海452223LM212M0030酬置工程師2009/1/Ua*【工之工至畫王維555：501&7111020033堂理2006/7/24書隰13A00L6牛國場工程師2007/8/U年1傀5A0017垂羽4522291&8412160030出用1刷燙理2003/3/6樸保2案例七在使用Excel查詢和引用數(shù)據(jù)時，經(jīng)常需要將文本形式的單元格地址轉(zhuǎn)換成對應應用，。如下圖所示為某超市的商品采購清單，其中又兩個供貨商提供了報價表(如供貨商A、供貨商B工作表)，如何根據(jù)品名和供貨商自動查詢對應的商品單價？選擇D3:D13單元格區(qū)域，輸入下列公式，按【Ctrl+Enter】組合鍵結(jié)束。=VLOOKUP(B3,INDIRECT(C3&”!a:b"),2,0)ABCDEF1采購清單2序號品名供貨商單價金額31荔枝俱貨商A2.4589218.042冬蟲夏草供貨商E61.76412,532.153人參供貨商A12.001131,356.064西瓜供貨商B1.423853.975葡萄供貨商A3.2152L66.986哈密瓜供貨向A4.24104440.997火龍果俱貨商B5.0322110.6108柚子俱貨向B6.24118736.3119梨俱貨商B1.792239.31210李子俱貨商A2.10111233.11311人參俱貨商E13.0622287.314合計7326,174.81516171819202122234襦清單 供貨商A|供貨商B| ①AB1商品單價表2O勺口口.口單價（元/斤）3蘋果2.114梨1.755荔枝2.456冬蟲夏草56.007人參12.008靈芝21.459橘子0.8610西瓜1.2311葡萄3.2112哈密瓜4.2413火龍果5.2114香蕉1.1215李子2.1016柚子6.3017榴蓮2.65181920212223采購清單供貨商A供貨商B案例八用VLOOKUP函數(shù)實現(xiàn)反向查找，如下圖，如何實現(xiàn)通過工號來查找姓名?有三種實現(xiàn)方法：方法一：在B8單元格輸入=VLOOKUP(A8,CHOOSE({1,2},B1:B5,A1:A5),2,0)，按ENTER鍵結(jié)束。方法二：在B8單元格輸入=VLOOKUP(A8,IF({1,0},B1:B5,A1:A5),2,0),按ENTER鍵結(jié)束。方法三：在B8單元格輸入=2口￡乂供195,乂人丁0川人8萬1:85,))，按ENTER鍵結(jié)束?！拦庾?/p>

名三四期六

姓泰李王起

12345KT004