Lookup的用法詳解(含實例和動畫)

Lookup的用法詳解(含實例和動畫)收集整理：山野雪人VLOOKUP函數(shù)的用法“Lookup”的漢語意思是“查找”，在Excel中與“Lookup”相關(guān)的函數(shù)有三個：VLOOKUP、HLOOKUO和LOOKUP。下面介紹VLOOKUP函數(shù)的用法。一、功能在表格的首列查找指定的數(shù)據(jù)，并返回指定的數(shù)據(jù)所在行中的指定列處的數(shù)據(jù)。二、語法標(biāo)準(zhǔn)格式：VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)三、語法解釋VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)可以寫為：VLOOKUP(需在第一列中查找的數(shù)據(jù),需要在其中查找數(shù)據(jù)的數(shù)據(jù)表,需返回某列值的列號,邏輯值True或False)1.Lookup_value為“需在數(shù)據(jù)表第一列中查找的數(shù)據(jù)”，可以是數(shù)值、文本字符串或引用。2.Table_array為“需要在其中查找數(shù)據(jù)的數(shù)據(jù)表”，可以使用單元格區(qū)域或區(qū)域名稱等。⑴如果range_lookup為TRUE或省略，則table_array的第一列中的數(shù)值必須按升序排列，否則，函數(shù)VLOOKUP不能返回正確的數(shù)值。如果range_lookup為FALSE，table_array不必進行排序。⑵Table_array的第一列中的數(shù)值可以為文本、數(shù)字或邏輯值。若為文本時，不區(qū)分文本的大小寫。3.Col_index_num為table_array中待返回的匹配值的列序號。Col_index_num為1時，返回table_array第一列中的數(shù)值；Col_index_num為2時，返回table_array第二列中的數(shù)值，以此類推。如果Col_index_num小于1，函數(shù)VLOOKUP返回錯誤值#VALUE!；如果Col_index_num大于table_array的列數(shù)，函數(shù)VLOOKUP返回錯誤值#REF!。4.Range_lookup為一邏輯值，指明函數(shù)VLOOKUP返回時是精確匹配還是近似匹配。如果為TRUE或省略，則返回近似匹配值，也就是說，如果找不到精確匹配值，則返回小于lookup_value的最大數(shù)值；如果range_value為FALSE，函數(shù)VLOOKUP將返回精確匹配值。如果找不到，則返回錯誤值#N/A。四、應(yīng)用例子ABCD1編號姓名工資科室22005001周杰倫2870辦公室32005002蕭亞軒2750人事科42005006鄭智化2680供應(yīng)科52005010屠洪剛2980銷售科62005019孫楠2530財務(wù)科72005036孟庭葦2200工會A列已排序（第四個參數(shù)缺省或用TRUE）VLOOKUP(2005001,A1:D7,2,TRUE)等于“周杰倫”VLOOKUP(2005001,A1:D7,3,TRUE)等于“2870”VLOOKUP(2005001,A1:D7,4,TRUE)等于“辦公室”VLOOKUP(2005019,A1:D7,2,TRUE)等于“孫楠”VLOOKUP(2005036,A1:D7,3,TRUE)等于“2200”VLOOKUP(2005036,A1:D7,4,TRUE)等于“工會”VLOOKUP(2005036,A1:D7,4)等于“工會”若A列沒有排序，要得出正確的結(jié)果，第四個參數(shù)必須用FALAEVLOOKUP(2005001,A1:D7,2,FALSE)等于“周杰倫”VLOOKUP(2005001,A1:D7,3,FALSE)等于“2870”VLOOKUP(2005001,A1:D7,4,FALSE)等于“辦公室”VLOOKUP(2005019,A1:D7,2,FALSE)等于“孫楠”VLOOKUP(2005036,A1:D7,3,FALSE)等于“2200”VLOOKUP(2005036,A1:D7,4,FALSE)等于“工會”五、關(guān)于TRUE和FALSE的應(yīng)用先舉個例子，假如讓你在數(shù)萬條記錄的表格中查找給定編號的某個人，假如編號已按由小到大的順序排序，你會很輕松地找到這個人；假如編號沒有排序，你只好從上到下一條一條地查找，很費事。用VLOOKUP查找數(shù)據(jù)也是這樣，當(dāng)?shù)谝涣幸雅判颍谒膫€參數(shù)用TRUE（或確?。珽xcel會很輕松地找到數(shù)據(jù)，效率較高。當(dāng)?shù)谝涣袥]有排序，第四個參數(shù)用FALSE，Excel會從上到下一條一條地查找，效率較低。筆者覺得，若要精確查找數(shù)據(jù)，由于計算機運算速度很快，可省略排序操作，直接用第四個參數(shù)用FALSE即可。關(guān)于Lookup的實例示例1下面的下示例是以指定的名字和月份為基礎(chǔ)查找一個數(shù)值.

=VLOOKUP()是用于沿第一列向下查找指定的名字.

難點是如何向右查找指定的月份.

解決這個難題的方法是使用=MATCH()函數(shù).

函數(shù)=MATCH()通過使用找到名字的列表查找對應(yīng)月份.并推算該月份在列表中的位置.

不巧的是，因為月份列表的搜索范圍與查找數(shù)值的范圍不等寬.

函數(shù)=MATCH()函數(shù)返回的數(shù)字比我們需要的數(shù)字少1,因此在公式中用了+1進行調(diào)整.

函數(shù)=VLOOKUP()現(xiàn)在使用函數(shù)=MATCH()得到的調(diào)整的數(shù)字n,在對應(yīng)名字所在行向右查找到該行第n列對應(yīng)單元格的輸入內(nèi)容.函數(shù)=VLOOKUP()中最后使用了FALSE，因此左側(cè)標(biāo)題行不用排序。

元月二月三月

程香宙108097

劉冰209069

程龍3010045

程坤4011051

chengxiang5012077

輸入要查找的名稱:程龍

輸入要查找的月份:三月

結(jié)果是:45

=VLOOKUP(F60,C54:F58,MATCH(F61,D53:F53,0)+1,FALSE)

3=MATCH(F61,D53:F53,0)

示例2這個示例使用函數(shù)=VLOOKUP()查找不同小車生產(chǎn)廠商不同配件的價值。

函數(shù)=VLOOKUP()向下掃描F列的標(biāo)題行并查找對應(yīng)的位于C列的配件名稱.

找到配件后，函數(shù)VLOOKUP根據(jù)函數(shù)MATCH找到的位置查找到對應(yīng)配件的價格。

公式中使用了絕對引用，為的是確保公式復(fù)制移動時函數(shù)=HLOOKUP()和=MATCH()引用的范圍不發(fā)生變化。

廠商配件價值

查找表格

日本豐田火花塞￡50

日本豐田福特奔馳

奔馳變速箱￡600

變速箱500450600

福特引擎￡1,200

引擎10001200800

奔馳方向盤￡275

方向盤250350275

福特火花塞￡70

火花塞507045

福特剎車片￡290

剎車片300290310

日本豐田變速箱￡500

福特引擎￡1,200

=VLOOKUP(C80,F74:I78,MATCH(B80,G73:I73,0)+1,FALSE)

示例3下面的示例是一個建材經(jīng)銷商提供的不同采購數(shù)量的折扣率

價格表中顯示了磚,木材和玻璃的單價.

折扣表提供了不同產(chǎn)品不同采購數(shù)量的折扣率.

采購表是采購預(yù)算.

所有的預(yù)算結(jié)果顯示在采購表中.

產(chǎn)品名稱列表在C列.

單價是從價格表中獲得的.

FALSE選項表示產(chǎn)品名稱在價格表中沒有排序整理.

使用FALSE強迫搜索精確匹配.如果沒有找到，則函數(shù)顯示錯誤.

折扣是從折扣表中獲得的

如果采購數(shù)量與折扣表中某個值匹配，函數(shù)=VLOOKUP將在折扣表中查找正確的匹配折扣.

TRUE選項表示采購數(shù)量在折扣表中經(jīng)過了升序排列整理.

使用TRUE允許模糊匹配.如果采購數(shù)量在折扣表中沒有找到匹配的值，則它下面較小的值將被使用.

比如采購數(shù)量為125將向下與100匹配,并且使用100對應(yīng)列的折扣率.

折扣表

價格表

磚木材玻璃

磚￡2

10%0%0%

木材￡1

1006%3%12%

玻璃￡3

3008%5%15%

采購表

項目采購數(shù)量單價折扣合計

磚125￡26%￡235

木材200￡13%￡194

玻璃150￡312%￡396

磚225￡26%￡423

木材50￡10%￡50

玻璃500￡315%￡1,275

公式為:

單價E118:

=VLOOKUP(C118,C106:D108,2,FALSE)

折扣F118:

=VLOOKUP(D118,F106:I108,MATCH(C118,G105:I105,0)+1,TRUE)

合計G118:

=(D118*E118)-(D118*E118*F118)

示例4

該示例使用1個大氣壓的空氣值。密度粘度溫度500400300250200150100500公式說明（結(jié)果）

在A列中查找1，并從相同行的B列中返回值(2.17)=VLOOKUP(1,B128:D136,2)100在A列中查找1，并從相同行的C列中返回值(100)=VLOOKUP(1,B128:D136,3,TRUE)#N/A在A列中查找0.746。因為A列中沒有精確地匹配，所以返回了一個錯誤值(#N/A)=VLOOKUP(0.7,B128:D136,3,FALSE)#N/A在A列中查找0.1。因為0.1小于A列的最小值，所以返回了一個錯誤值(#N/A)=VLOOKUP(0.1,B128:D136,2,TRUE)在A列中查找2，并從相同行的B列中返回值(1.71)=VLOOKUP(2,B128:D136,2,TRUE)Excel查詢函數(shù)Lookup和Vlookup區(qū)別電腦愛好者

張劍悅Excel查詢函數(shù)中，Lookup和Vlookup有哪些區(qū)別？它們在應(yīng)用中應(yīng)該如何把握？請看本文講解?！風(fēng)ookup——數(shù)與行列比Lookup的工作職責(zé)是什么呢?用一個數(shù)與一行或一列數(shù)據(jù)依次進行比較，發(fā)現(xiàn)匹配的數(shù)值后，將另一組數(shù)據(jù)中對應(yīng)的數(shù)值提取出來。·工資稅率表：用數(shù)值比較根據(jù)不同的工資進行不同的稅率計算是一個常見的應(yīng)用。我們來看這張“工資稅率查詢”表(見圖1)?，F(xiàn)在要在右側(cè)根據(jù)“收入”(F列)，直接得到對應(yīng)的“稅率”(G列)。在計算第1個“稅率”時，輸入函數(shù)公式“=LOOKUP(F4,$B$3:$B$8,$D$3:$D$8)”，回車，便可得到“36.00%”。這個結(jié)果是怎么來的?用F4中的第1個收入數(shù)“$123,409”，與左側(cè)表的“收入最低”各檔數(shù)據(jù)(“$B$3:$B$8”)進行對比，雖然“$123,409”在“收入最低”各檔數(shù)中沒有完全一致的數(shù)據(jù)與之匹配，但是會與其中小于它的最大數(shù)“$58,501”相匹配。這樣，同一行對應(yīng)的“36.00%”就提取出來了?！D書銷售表：用文本比較Lookup函數(shù)的對比數(shù)還可以是文本。在這張圖書銷售查詢表中(見圖2)，用下表輸入的“編號”(A15單元格)文本當(dāng)作查詢數(shù)，與上表的“編號”一列($A$3:$A$11)進行對比，查詢到了匹配的文本后，將“教材名稱”一列($B$3:$B$11)對應(yīng)的數(shù)據(jù)提取出來。公式是“=LOOKUP(A15,$A$3:$A$11,$B$3:$B$11)”?！颲lookup——數(shù)與表格比Lookup有一個大哥——Vlookup函數(shù)。兩兄弟有很多相似之處，但大哥本領(lǐng)更大。Vlookup用對比數(shù)與一個“表”進行對比，而不是Lookup函數(shù)的某1列或1行，并且Vlookup可以選擇采用精確查詢或是模糊查詢方式，而Lookup只有模糊查詢?！つ：ヅ溆肰lookup函數(shù)進行模糊查詢時，幾乎與Lookup的作用完全一致。我們用Vlookup函數(shù)來提取第1個例子中的工資稅率結(jié)果。函數(shù)公式為“=VLOOKUP(F4,$B$3:$D$8,3,TRUE)”。在這個函數(shù)中，用第1個收入“$123,409”(F4單元格)當(dāng)作對比數(shù)，用它與左側(cè)表(“$B$3:$D$8”)的第1列數(shù)進行對比，雖然“$123,409”在“收入最低”各檔數(shù)中沒有完全一致的數(shù)據(jù)與之匹配，但是函數(shù)的最后一個參數(shù)是“TURE”(“TURE”就是模糊查詢)，所以它會與其中小于它的最大數(shù)“$58,501”相匹配。并將表中第3列(函數(shù)的第3個參數(shù)為“3”)對應(yīng)的數(shù)據(jù)提取出來，所以結(jié)果同樣是“36.00%”?！び唵蚊骷?xì)表：精確匹配有時候，我們需要精益求精。在下面這個“訂單明細(xì)表”(見圖3)中，最后一列“貨運費用”中的數(shù)據(jù)要通過“交貨方式”從左側(cè)“配送公司收費表”中進行匹配查詢。這是一個典型的精確查詢的例子，計算第1個數(shù)據(jù)的函數(shù)公式是“=VLOOKUP(H3,$B$2:$D$6,3,FALSE)”。小提示：把最后一個參數(shù)從“TRUE”變更成“FLASE”，就是精確匹配。而精確查詢，就是查詢數(shù)要與查詢表第1列中的數(shù)據(jù)完全一致才能匹配提取，否則結(jié)果返回錯誤值“#N/A”。點評：Excel為我們提供了近20個有關(guān)“查找和引用”的函數(shù)，除了最常用的Lookup、Vlookup，還有Choos、Row、Colum、Index和Match等，大家可以通過函數(shù)的幫助查看具體的功能。這些函數(shù)往往不是單獨使用，可以與其他函數(shù)和Excel中的一些功能進行配合。EXCEL函數(shù)VLOOKUP高級應(yīng)用VLOOKUP是處理根據(jù)條件查找對象的函數(shù)

這里有幾個數(shù)組公式可以達到二個條件（在兩列）或多個條件查找的功能

=VLOOKUP(A2&B2,IF(COLUMN(A1:B1)=COLUMN(A1:A1),sheet1!$A$2:$A$110&sheet1!$B$2:$B$110,sheet1!$C$2:$C$110),2,)

=INDEX(sheet1!C$2:C$110,MATCH(A2&B2,sheet1!A$2:A$110&sheet1!B$2:B$110,0))

=LOOKUP(2,1/((sheet1!$A$2:$A$110=A2)*(sheet1!$B$2:$B$110=B2)),sheet1!$C$2:$C$110)

=SUMPRODUCT((sheet1!$A$2:$A$110=A2)*(sheet1!$B$2:$B$110=B2),sheet1!$C$2:$C$110)

前兩個需按數(shù)組公式組合鍵。符合兩列或兩列以上數(shù)據(jù)查找對應(yīng)結(jié)果{=VLOOKUP(A3&C3&E3,CHOOSE({1,2},基礎(chǔ)表!$A$3:$A$123&基礎(chǔ)表!$C$3:$C$123&基礎(chǔ)表!$E$3:$E$123,基礎(chǔ)表!$D$3:$D$123),2,0)}

相當(dāng)于ACE列合并形成數(shù)組：基礎(chǔ)表!$A$3:$A$123&基礎(chǔ)表!$C$3:$C$123&基礎(chǔ)表!$E$3:$E$123和基礎(chǔ)表!$D$3:$D$123中查找取第2個數(shù)組如果使用vlookup(XXXXX,choose({1,2,3,4...n},數(shù)組1,數(shù)組2...數(shù)組n),K)，還可以解決許多比這復(fù)雜的問題即可取第N個數(shù)組

只有兩個數(shù)組時CHOOSE和IF相同返回有重復(fù)列數(shù)據(jù)中所有有重復(fù)的內(nèi)容

=IF(ROW(A1)>COUNT(IF((COUNTIF($A$2:$A$7,$A$2:$A$7)>1)*(MATCH($A$2:$A$7,$A$2:$A$7,0)=ROW($A$2:$A$7)-1),ROW($A$2:$A$7))),"",INDEX(A:A,SMALL(IF((COUNTIF($A$2:$A$7,$A$2:$A$7)>1)*(MATCH($A$2:$A$7,$A$2:$A$7,0)=ROW($A$2:$A$7)-1),ROW($A$2:$A$7)),ROW(A1))))

返回有重復(fù)列數(shù)據(jù)中所有內(nèi)容，達到不重復(fù)。

=IF(SUM(1/COUNTIF(A$2:A$7,A$2:A$7))>=ROW(A1),INDEX(A$2:A$7,SMALL(IF(ROW(A$2:A$7)-1=MATCH(A$2:A$7,A$2:A$7,0),ROW(A$2:A$7)-1,"0"),ROW(A1))),"")

另敬請注意看貼的網(wǎng)友，此樓的貼子如不注明為數(shù)組，即默認(rèn)為數(shù)組公式。頂一下,不能讓他沉下去在B列中找出與A列重復(fù)的，{=OFFSET($B$1,SMALL(MATCH($A$1:$A$8,$B$1:$B$19,0),ROW(1:1))-1,0)}

解釋SMALL({10,20,30},1)中，得到的結(jié)果是“10”，SMALL函數(shù)是取得數(shù)據(jù)區(qū)域中指定的第幾個最小值。在這個公式中的“1”是指第一小值，也就是最小值。公式...SMALL(MATCH($A$1:$A$8,$B$1:$B$19,0),ROW(1:1))...是先用MATCH()函數(shù)取得$A$1:$A$8的數(shù)據(jù)在$B$1:$B$19中的行數(shù)值，設(shè)A1在第12行，A2在第14行A3在15,A4在16,A5在3,A6在6行......，最后的結(jié)果是｛12,14,15,16,3,6｝，然后再套上SMALL(），后面的ROW(1:1)等于1，也就是取得｛12,14,15,16,3,6｝中的最小值是“3”，“3”對應(yīng)在B列第3行內(nèi)容，即公式得到的第一個重復(fù)內(nèi)容,向下填充,即可相應(yīng)得到第二個第..個頂一下,不能讓他沉下去在B列中找出與A列重復(fù)的，{=OFFSET($B$1,SMALL(MATCH($A$1:$A$8,$B$1:$B$19,0),ROW(1:1))-1,0)}

解釋SMALL({10,20,30},1)中，得到的結(jié)果是“10”，SMALL函數(shù)是取得數(shù)據(jù)區(qū)域中指定的第幾個最小值。在這個公式中的“1”是指第一小值，也就是最小值。公式...SMALL(MATCH($A$1:$A$8,$B$1:$B$19,0),ROW(1:1))...是先用MATCH()函數(shù)取得$A$1:$A$8的數(shù)據(jù)在$B$1:$B$19中的行數(shù)值，設(shè)A1在第12行，A2在第14行A3在15,A4在16,A5在3,A6在6行......，最后的結(jié)果是｛12,14,15,16,3,6｝，然后再套上SMALL(），后面的ROW(1:1)等于1，也就是取得｛12,14,15,16,3,6｝中的最小值是“3”，“3”對應(yīng)在B列第3行內(nèi)容，即公式得到的第一個重復(fù)內(nèi)