錦子老師

VLOOKUP函數是Excel最常用的尋找函數，VLOOKUP函數有很多關於該函數的各種使用方法的教程，但還是有很多同學提問與該函數有關的問題。本篇文章將常見的VLOOKUP函數相關問題總結一下，主要為VLOOKUP函數怎麼使用？如何使用VLOOKUP函數進行反向逆向尋找、模糊尋找、區間尋找、多條件尋找、多項尋找。

一、VLOOKUP函數怎麼用?

VLOOKUP是一個尋找函數，給定一個尋找的目標，它就能從指定的尋找範圍中傳回想要尋找到的值。它的基本語法為：

VLOOKUP(尋找目標，尋找範圍，傳回值的欄數，精確OR模糊尋找)

下面以一個實例來介紹一下這四個參數的使用

例：如下圖所示，要求根據表二中的姓名，尋找姓名所對應的年齡。

G3儲存格公式：

=VLOOKUP(F3,$B$2:$D$8,3,0)

再將公式複製到G4儲存格。

參數說明：

1、尋找目標：就是你指定的尋找的內容或儲存格參照。本例中表二F欄的姓名就是尋找目標。我們要根據表二的姓名在表一中B欄進行尋找。

2、尋找範圍(VLOOKUP(F3,$B$2:$D$8,3,0)：指定了尋找目標，如果沒有說從哪裡尋找，Excel肯定會很為難。所以下一步我們就要指定從哪個範圍中進行尋找。VLOOKUP的這第二個參數可以從一個儲存格範圍中尋找，也可以從一個常量陣列或記憶體陣列中尋找。本例中要從表一中進行尋找，那麼範圍我們要怎麼指定呢?這裡也是極易出錯的地方。大家一定要注意，給定的第二個參數尋找範圍要符合以下條件才不會出錯：

A 尋找目標一定要在該範圍的第一欄。本例中查閱資料表二的姓名，那麼姓名所對應的表一的姓名欄，那麼表一的姓名欄一定要是尋找範圍的第一欄。像本例中，給定的範圍要從第二欄開始，即$B$2:$D$8，而不能是$A$2:$D$8。因為尋找的姓名不在$A$2:$D$8範圍中的第一欄。

B 該範圍中一定要包含要傳回值所在的欄，本例中要傳回的值是年齡。年齡欄(表一的D欄)一定要包括在這個範圍內，如果寫成$B$2:$C$8就是錯的。

3、傳回值的欄數(VLOOKUP(F3,$B$2:$D$8,3,0)。這是VLOOKUP第3個參數。它是一個整數值。它怎麼得來的呢。它是傳回值在第二個參數給定的範圍中的欄數。本例中我們要傳回的是年齡，它是第二個參數尋找範圍$B$2:$D$8的第3欄。這裡一定要注意，欄數不是在工作表中的欄數(不是第4欄)，而是在尋找範圍的第幾欄。如果本例中要是尋找姓名所對應的性別，第3個參數的值應該設置為多少呢。答案是2。因為性別在$B$2:$D$8的第2欄中。

4、精確OR模糊尋找(VLOOKUP(F3,$B$2:$D$8,3,0)，最後一個參數是決定函數精確和模糊尋找的關鍵。精確即完全一樣，模糊即包含的意思。第4個參數如果指定值是0或FALSE就表示精確尋找，而值為1或TRUE時則表示模糊。這裡錦子老師提醒大家切記切記，在使用VLOOKUP時千萬不要把這個參數給漏掉了，如果缺少這個參數默為值為模糊尋找，我們就無法精確尋找到結果了。

二、VLOOKUP的反向尋找

VLOOKUP的反向尋找，需要用IF函數把資料來源倒置一下。

一般情況下，VLOOKUP函數只能從左向右尋找。但如果需要從右向右尋找，則需要把範圍進行乾坤大挪移，把列的位置用陣列互換一下。

例：要求在如下圖所示表中的姓名反查工號。

G8儲存格公式：

=VLOOKUP(F8,IF({1,0},B2:B8,A2:A8),2,0)

公式剖析：

1、這裡其實不是VLOOKUP可以實現從右至右的尋找，而是利用IF函數的陣列效應把兩欄互換位元重新組合後，再按正常的從左至右尋找。

2、IF({1,0},B2:B8,A2:A8)這是本公式中最重要的組成部分。在Excel函數中使用陣列時(前提時該函數的參數支援陣列)，傳回的結果也會是一個陣列。這裡1和0不是實際意義上的數字，而是1相關於TRUE，0相當於FALSE，當為1時，它會傳回IF的第二個參數(B欄)，為0時傳回第二個參數(A欄)。根據陣列運算傳回陣列，所以使用IF後的結果傳回一個陣列(非儲存格範圍)：{"劉備","1";"張飛","2";"關羽","3";"曹操","4"}

三、VLOOKUP的模糊尋找

模糊尋找就是匹配尋找。

例：在A欄我們要知道如何尋找型號為LED的產品所對應的B欄價格，如果需要尋找包含LED的產品名稱怎麼表示呢?如下圖表中所示。

B13儲存格公式：

=VLOOKUP("*"&A13&"*",A2:B10,2,0)

公式說明：VLOOKUP的第一個參數允許使用萬用字元*來表示包含的意思，把*放在字元的兩邊，即"*"&字元&"*"。

四、VLOOKUP的區間尋找

數值的區間尋找即給定多個區間，指定一個數就可以尋找出它在哪個區間並傳回這個區間所對應的值。

VLOOKUP的第4個參數，如果為0或FALSE是精確尋找，如果是1或TRUE或省略則為模糊尋找，那麼實現區間尋找正是第4個參數的模糊尋找應用。

首先需要瞭解一下VLOOKUP函數模糊尋找的兩個重要規則：

1、參照的數值範圍一定要從小到大排序。雜亂的數字是無法準確尋找到的。如下面A欄符合模糊尋找的前題，B欄則不符合。

2、模糊尋找的原理是：給一個數值，它會找到和它最接近，但比它小的那個數。詳見下圖說明。

最後看一個實例：

例：如下圖所示，要求根據上面的年所得稅率表，在年所得計算表中計算每個所得額的稅率和稅金。

B11儲存格公式：

=VLOOKUP(A11,$A$3:$B$7,2)

再將公式複製到B12:B14儲存格。

公式說明：

1、上述公式省略了VLOOKUP最後一個參數，相當於把第四個參數設置成1或TRUE。這表示VLOOKUP要進行數字的區間尋找。

2、圖中公式中在尋找374,761時傳回稅率表所對應的比率0%，原因是0和480,000與374,761最接近，但VLOOKUP只選比尋找值小的那一個，所以公式會傳回0所對應的稅率0%。

五、VLOOKUP的多條件尋找

VLOOKUP函數需要借用陣列才能實現多條件尋找。

例：要求根據部門和姓名尋找C欄的加班時間。

分析：不是讓VLOOKUP本身實現多條件尋找，而是想辦法重構一個陣列。多個條件可以用&連接在一起，同樣兩欄也可以連接成一欄資料，然後用IF函數進行組合。

C9儲存格公式：

{=VLOOKUP(A9&B9,IF({1,0},A2: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欄資料合併成一個兩欄的記憶體陣列。按F9後可以查看的結果為：

{"行政張飛",48;"業務劉備",46;"行政關羽",30;"業務曹操",36}

4、完成了陣列的重構後，接下來就是VLOOKUP的基本尋找功能了，另外公式中含有多個資料與多個資料運算(A2:A5&B2:B5),，所以必須以陣列形式輸入，即按Ctrl+Shift+Enter完成輸入。

六、VLOOKUP的多項尋找

VLOOKUP一般情況下只能尋找一個，那麼多項該怎麼尋找呢?

例：要求把如圖表中所有關羽的消費金額全列出來

分析：經過前面的學習，我們也有這樣一個思路，我們在實現複雜的尋找時，努力的方向是怎麼重構一個尋找內容和尋找的範圍。要想實現多項尋找，我們可以對尋找的內容進行編號，第一個出現的是後面連接1，第二個出現的連接2。。。

公式：{=IFERROR(VLOOKUP(E$2&ROW(A1),IF({1,0},$B$2:$B$9&COUNTIF(INDIRECT("b2:b"&ROW($2:$9)),E$2),$C$2:$C$9),2,),"")}

公式剖析：

1、E$2&ROW(A1) 連接序號，公式向下複製時會變成E$2連接1，2，3

2、給所有的關羽進行編號。要想生成編號，就需要生成一個不斷擴充的範圍(INDIRECT("b2:b" &ROW($2:$9))，然後在這個逐列擴充的範圍內統計關羽的個數，在連接上$B$2:$B$9後就可以對所有的關羽進行編號了。

3、IF({1，0}把編號後的B欄和C欄重組構成一個兩欄陣列

通過以上的講解，需要知道，VLOOKUP函數的基本用法是固定的，要實現高級尋找，就需要借助其他函數來重構尋找內容和尋找陣列。

以上就是今天主要學習的知識點，希望對大家有所幫助~~有什麼問題歡迎留言，我會儘量及時的給大家答覆~~

部落格相關範例