在EXCEL中VLOOKUP函數(shù)的使用方法大全.

1、VLOOKUP是一個查找函數(shù)，給定一個查找的目標，它就能從指定的查找區(qū)域中查找返回想要查找到的值。它的基本語法為 :VLOOKUP(查找目標 ，查找范圍 ，返回值的列數(shù) ，精確 OR模糊查找 )下面以一個 實例來介紹一下這四個參數(shù)的使用例 1: 如下圖所示，要求根據(jù)表二中的姓名，查找姓名所對應的年齡。公式 :B13 =VLOOKUP(A13, $B$2:$D$8, 3,0)參數(shù)說明 :1 查找目標 : 就是你指定的查找的內容或單元格引用。本例中表二 A 列的姓名就是查找目標。 我們要根據(jù)表二的 “姓名”在表一中 A 列進行查找。公式 :B13 =VLOOKUP(A13,$B$2:$D$8,3,

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

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

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

5、值為模糊查找，我們就無法精確查找到結果了。一、 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(B1) 返回值 2而單元格引用復制時會自動發(fā)生變化，即A1 隨公式向右復制時會變成B1， C1，

6、D1 。這樣我們用COLUMN 函數(shù)就可以轉換成數(shù)字1， 2， 3， 4 。例 : 下例中需要同時查找性別，年齡，身高，體重。公式 :=VLOOKUP($A13,$B$2:$F$8,COLUMN(B1),0)公式說明 : 這里就是使用COLUMN(B1) 轉化成可以自動遞增的數(shù)字。二、 VLOOKUP查找出現(xiàn)錯誤值的問題。1 、如何避免出現(xiàn)錯誤值。EXCEL2003 在 VLOOKUP 查找不到，就 #N/A 的錯誤值，我們可以利用錯誤處理函數(shù)把錯誤值轉換成 0 或空值。即 :=IF(ISERROR(VLOOKUP( 參數(shù)略 ),"",VLOOKUP( 參數(shù)略 )EXCEL

7、2007 ， EXCEL2010 中提供了一個新函數(shù) IFERROR，處理起來比 EXCEL2003 簡單多了。IFERROR(VLOOKUP() ， "")2 、 VLOOKUP 函數(shù)查找時出現(xiàn)錯誤值的幾個原因A、實在是沒有所要查找到的值B 、查找的字符串或被查找的字符中含有空格或看不見的空字符，驗證方法是用= 號對比一下，如果結果是FALSE，就表示兩個單元格看上去相同，其實結果不同。C 、參數(shù)設置錯誤。VLOOKUP 的最后一個參數(shù)沒有設置成1 或者是沒有設置掉。第二個參數(shù)數(shù)據(jù)源區(qū)域，查找的值不是區(qū)域的第一列，或者需要反回的字段不在區(qū)域里，參數(shù)設置在入門講里已注明，請

8、參閱。D 、數(shù)值格式不同，如果查找值是文本，被查找的是數(shù)字類型，就會查找不到。解決方法是把查找的轉換成文本或數(shù)值，轉換方法如下:文本轉換成數(shù)值:*1 或 - 或 /1數(shù)值轉抱成文本:&""三、字符的模糊查找在 A 列我們知道如何查找型號為“ AAA的”產(chǎn)品所對應的B 列價格，即 :=VLOOKUP(C1,A:B,2,0)如果我們需要查找包含“ AAA的”產(chǎn)品名稱怎么表示呢?如下圖表中所示。公式 =VLOOKUP("*"&A10&"*",A2:B6,2,0)公式說明的兩邊，即:VLOOKUP"*&quo

9、t; &字符的第一個參數(shù)允許使用通配符&"*" ?！?*來”表示包含的意思，把* 放在字符四、數(shù)字的區(qū)間查找數(shù)字的區(qū)間查找即給定多個區(qū)間， 指定一個數(shù)就可以查找出它在哪個區(qū)間并返回這個區(qū)間所對應的值。在 VLOOKUP 入門中我們提示VLOOKUP 的第 4 個參數(shù)， 如果為 0 或 FALSE 是精確查找，如果是 1 或 TRUE 或省略則為模糊查找， 那么實現(xiàn)區(qū)間查找正是第4 個參數(shù)的模糊查找應用。首先我們需要了解一下VLOOKUP 函數(shù)模糊查找的兩個重要規(guī)則:1 、引用的數(shù)字區(qū)域一定要從小到大排序。雜亂的數(shù)字是無法準確查找到的。如下面A列符合模糊查找的

10、前題，B 列則不符合 。2 、模糊查找的原理是 :給一定個數(shù)，它會找到 和它最接近，但比它小的那個數(shù) 。詳見下圖說明。最后看一個實例:例 : 如下圖所示，要求根據(jù)上面的提成比率表，在提成表計算表中計算每個銷售額的提成比率和提成額。公式 :=VLOOKUP(A11,$A$3:$B$7,2)公式說明 :1、上述公式省略了VLOOKUP 最后一個參數(shù)，相當于把第四個參數(shù)設置成1 或 TRUE。這表示 VLOOKUP 要進行數(shù)字的區(qū)間查找。2、圖中公式中在查找5000 時返回比率表0 所對應的比率1% ，原因是 0和 10000 與5000 最接近，但 VLOOKUP 只選比查找值小的那一個，所以公式

11、會返回0 所對應的比率1% 。五、 VLOOKUP的反向查找。一般情況下， VLOOKUP 函數(shù)只能從左向右查找。 但如果需要從右向右查找， 則需要把區(qū)域進行 “乾坤大挪移 ”，把列的位置用數(shù)組互換一下。例 1: 要求在如下圖所示表中的姓名反查工號。公式 :=VLOOKUP(A9,IF(1,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ù)支

12、持數(shù)組)，返回的結果也會是一個數(shù)組。這里 1和 0 不是實際意義上的數(shù)字，而是 1 相關于 TRUE，0 相當于 FALSE，當為 1 時，它會返回IF 的第二個參數(shù)(B 列 )，為 0 時返回第二個參數(shù) (A 列 ) 。根據(jù)數(shù)組運算返回數(shù)組，所以使用IF 后的結果返回一個數(shù)組 (非單元格區(qū)域 ):" 張一 ","A001""趙三 ","A002"" 楊五 ","A003""孫二 ","A004"六、 VLOOKUP函數(shù)的多條件查找。V

13、LOOKUP 函數(shù)需要借用數(shù)組才能實現(xiàn)多條件查找。例 2: 要求根據(jù)部門和姓名查找C 列的加班時間。分析 :我們可以延用例1 的思路，我們的努力方向不是讓VLOOKUP 本身實現(xiàn)多條件查找，而是想辦法重構一個數(shù)組。多個條件我們可以用 &連接在一起，同樣兩列我們也可以連接成一列數(shù)據(jù)，然后用 IF 函數(shù)進行組合。公式 :=VLOOKUP(A9&B9,IF(1,0,A2:A5&B2:B5,C2:C5),2,0)公式剖析 :1 、A9&B9把兩個條件連接在一起。把他們做為一個整體進行查找。2 、A2:A5&B2:B5 ，和條件連接相對應，把部分和姓名列也連接在一

14、起，作為一個待查找的整體。3 、IF(1,0,A2:A5&B2:B5,C2:C5)用 IF(1 ， 0 把連接后的兩列與C 列數(shù)據(jù)合并成一個兩列的內存數(shù)組。按F9 后可以查看的結果為:" 銷售張一 ",1;" 銷售趙三 ",5;" 人事楊五 ",3;" 銷售趙三 ",64 、完成了數(shù)組的重構后，接下來就是VLOOKUP 的基本查找功能了，另外公式中含有多個數(shù)據(jù)與多個數(shù)據(jù)運算(A2:A5&B2:B5),，所以必須以數(shù)組形式輸入，即按ctrl+shift后按ENTER 結束輸入。七、 VLOOKUP函數(shù)

15、的批量查找。VLOOKUP 一般情況下只能查找一個，那么多項該怎么查找呢?例 3 要求把如圖表中所有張一的消費金額全列出來我們在實現(xiàn)復雜的查找時，努力的方向是怎么重構一個查找內容和查找的區(qū)域。要想實現(xiàn)多項查找，我們可以對查找的內容進行編號，第一個出現(xiàn)的是后面連接1 ，第二個出現(xiàn)的連接 2 。公式:=VLOOKUP(B$9&ROW(A1),IF(1,0,$B$2:$B$6&COUNTIF(INDIRECT("b2:b"&ROW($2:$6),B$9),$C$2:$C$6),2,)公式剖析 :1、 B$9&ROW(A1)連接序號，公式向下復制時會

16、變成B$9 連接 1， 2 ，32 、給所有的張一進行編號。要想生成編號，就需要生成一個不斷擴充的區(qū)域(INDIRECT("b2:b"&ROW($2:$6)，然后在這個逐行擴充的區(qū)域內統(tǒng)計“張一 ”的個數(shù)，在連接上 $B$2:$B$6 后就可以對所有的張一進行編號了。3 、 IF(1 ， 0 把編號后的 B 列和 C 組重構成一個兩列數(shù)組通過以上的講解，我們需要知道， VLOOKUP 函數(shù)的基本用法是固定的，要實現(xiàn)高級查找，就需要借助其他函數(shù)來重構查找內容和查找數(shù)組。VLOOKUP 函數(shù)查詢只能查詢一個表格， 但如果需要從多個表中查找我們該怎么辦呢 ?其實方法很單，

17、使用多個 IF 就可以了。即 :=IF(iserror(vlookup(查詢表 1),"",vlookup(查詢表 1)&IF(iserror(vlookup(查詢表2),"",vlookup(查詢表 2)&IF(iserror(vlookup(查詢表 3),"",vlookup(查詢表 3)答: 在 excel 中函數(shù)最多只能嵌套七層， IF 函數(shù)也不能例外，遇到需要進行多次判斷的怎么辦呢 ?可以用 VLOOKUP 函數(shù)替代。例如 : 下表中需要根據(jù)提供的銷售額判斷提成比率，這里可能有很多，為了演示方便，只列中三種。這種情況下怎么