vlookup函數的設置案例(vlookup函數的運用辦法有哪些)

vlookup函數的操作實例(vlookup函數的使用方法有哪些)

平時工作中經常需要對Excel表格中數據進行查詢調用，VLOOKUP函數是工作中使用頻率超高的查詢函數之一。本文完整詳盡的介紹了vlookup函數的使用方法有哪些?VLOOKUP函數的使用方法，并結合實例深入的講解了Vlookup函數的應用技巧。

1、VLOOKUP函數語法解析

VLOOKUP 基礎用法是搜索某個單元格區域的第一列，然后返回該區域相同行上任何單元格中的值。

VLOOKUP 中的 V 表示垂直方向。當比較值位于所需查找的數據的左邊一列時，可以使用VLOOKUP

語法結構：

VLOOKUP(lookup_value,table_array, col_index_num, [range_lookup])

解讀：

VLOOKUP(找什么，在哪找，找到后返回其右側對應的第幾列數據，精確還是模糊查找)

這樣一看是不是清晰多了?

具體的語法說明大家按F1看幫助文檔吧，本文就不再詳細解讀了。

需要說明的一點是，Excel中的幫助信息也有錯誤，比如在插入函數功能中VLOOKUP第四參數的說明就是錯的，大家注意不要被誤導哦!如下圖：

紅框部分幫助錯誤，應改為：如果為FALSE或0，精確匹配，如果為TRUE或忽略，大致匹配。

2、VLOOKUP函數單條件查找

根據單條件進行數據查找是最基礎也是最常見的需求了。

看如下案例，工作中的數據源為A:B兩列，分別放置業務員姓名和對應的銷售額，當需要按照業務員查找其對應的銷售額時，就要用到VLOOKUP函數了。

表中黃色區域為公式所在位置，以E2單元格公式為例

=VLOOKUP(D2,$A$2:$B$12,2,0)

這個公式是標準的VLOOKUP函數的基礎應用方法，每個參數都沒有變形，所以很方便初學者獲悉這個函數最原始的含義和作用。

第一參數：找什么(或者說按什么查找)，按業務員查找，所以輸入D2

第二參數：在哪找，數據源區域在A:B列，所以輸入$A$2:$B$12

第三參數：找到后返回第幾列，我們要查找的是銷售額，銷售額位于B列，即第二參數中的第二列，所以輸入2

第四參數：這里要精確查找，所以輸入0

翻譯過來就是

=VLOOKUP(要查找的業務員,包含業務員和其銷售額的數據源區域,找到后返回第2列,精確查找)

3、 VLOOKUP函數多條件查找

如果有多個條件要同時滿足怎么辦?

其實很簡單，可以在數據源左側創建一個輔助列，將多個條件用&符號連接起來作為條件查找列。

如果數據源左側不允許插入列，或者想直接用一個公式搞定多條件查找，自然也有辦法啦，下面結合一個案例來介紹這種方法。

看如下案例，工作中的數據源為A:C兩列，分別放置水果、產地和對應的銷售額，當需要同時按照水果和產地查找其對應的銷售額時，就要用到VLOOKUP函數的多條件查找技巧了。

表中黃色區域為公式所在位置，以G2單元格公式為例

輸入以下數組公式，按<Ctrl+Shfit+Enter>組合鍵結束輸入。

=VLOOKUP(E2&F2,IF({1,0},$A$2:$A$12&$B$2:$B$12,$C$2:$C$12),2,0)

注意：這個公式是數組公式，如果直接按Enter鍵輸入會返回#N/A錯誤值。

新人一定搞不懂啥叫數組公式呢?這里科普一下吧。

Excel中的公式分普通公式和數組公式。

普通公式不多說啦就是大家最常用的，輸入公式后直接按Enter結束輸入。

數組公式是為了應對一些比較復雜的計算，需要對一組或者多組數據執行多項計算，返回一個值或者一組值的公式，這樣的公式輸入完成后需要按<Ctrl+Shfit+Enter>組合鍵結束輸入，公式兩側會自動出現一對大括號{}，標識這個公式是要按照數組運算模式來計算的。

當鼠標定位在公式所在單元格進入編輯狀態時，大括號就消失了，當我們按ESC鍵退出編輯狀態時大括號就又重新出現了。

詳細解讀：

4、VLOOKUP函數查找返回多列數據

單條件查找會了，多條件查找也學了，都是輸入一個公式然后向下復制填充一列的形式，如果有多列數據需要根據查找值調取對應數據，那么如何輸入一個公式就能搞定一個區域的數據調用呢?

這個案例告訴你答案。

下圖中左側表格是數據源區域，需要在右側區域根據業務員姓名調取對應的科目成績，黃色區域是需要填寫公式的區域。

這個案例中，觀察到右側的科目順序和數據源一致，都是從數學到體育，如果用最笨的方法一列一列寫公式固然可以實現目的，但當需要查找的列很多時無疑是一項大工程。

這里給出一個簡單實用的公式，選中H2:K5單元格區域，輸入以下公式后按<Ctrl+Enter>組合鍵。

注意是組合鍵同時按下，而不要只按Enter鍵哦!

=VLOOKUP($G2,$A$2:$E$12,COLUMN(B1),0)

5、VLOOKUP函數從右向左查找

工作中免不了遇到數據源中要調取的數據在查找值所在列的左側，知道VLOOKUP函數的常規用法是從左往右找，那么遇到這種需要從右往左查找的問題如何解決呢?

下面的表格中，需要按照給出的編號查找對應的業務員姓名，黃色區域輸入公式。

看到這個場景，有的小伙伴或許說，雖然VLOOKUP函數只能從左往右找，那干脆把數據源里面的編號列改到左邊不就行了嗎?

想的沒錯，這樣是可以實現的，但以下幾種情況下還是學會一個公式一步到位的比較好：

1、當數據源格式不允許改動時

2、當這項工作出現的頻率較高，你懶得每次都要重新改數據源，想一勞永逸時

3、當你想除了基礎用法之外，多學點高逼格用法時，哈哈

所以說技多不壓身，多學一手，何樂而不為呢?

F2單元格輸入以下公式后向下復制填充。

=VLOOKUP(E2,IF({1,0},$B$2:$B$12,$A$2:$A$12),2,0)

6、VLOOKUP函數按數據所處區間劃分等級

工作中除了精確查找外，模糊匹配也經常遇到。結合下面這個案例來學習模糊匹配技巧。

下面表格中左側是數據源區域，需要按照等級劃分規則，將成績劃分到其對應的等級中。

等級劃分規則如下：

[0,60): D

[60,80): C

[80,90): B

[90,100]: A

看到這里，很多小伙伴一定想到用IF多條件嵌套來解決，沒錯，使用IF是可以實現的，比如這兩個公式都能實現我們的需求。

=IF(B2>=90,"A",IF(B2>=80,"B",IF(B2>=60,"C","D")))

=IF(B2<60,"D",IF(B2<80,"C",IF(B2<90,"B","A")))

但是當劃分規則更多時，編輯公式的時候需要一層一層的嵌套，用IF書寫公式簡直變成了體力活。

有沒有更簡便的辦法呢?當然，只要學會VLOOKUP模糊匹配技巧就可以了。

用以下公式就可以實現IF多層條件嵌套同樣的結果了。

=VLOOKUP(B2,{0,"D";60,"C";80,"B";90,"A"},2)

7、VLOOKUP函數使用通配符模糊查找

當在工作中遇到需要只根據查找值的一部分進行查找時，記得可以利用通配符的特性來實現。

下面結合一個案例來介紹。

表格中左側是數據源，需要查找業務員名字中帶“強”的人的銷售額。

通配符星號*通配任意個字符，問號?通配單一字符，這個案例中模糊查找的規則是只要名字中帶“強”就可以，所以我們需要使用“*強*”這種形式，支持“強”字出現在任意位置。

E2公式為：

=VLOOKUP("*強*",$A$2:$B$12,2,0)

8、VLOOKUP函數多層級條件嵌套查找

遇到多層級條件嵌套查找，很多人第一時間想到的是IF多條件嵌套，還有些高手想到的是LOOKUP函數查找，其實VLOOKUP函數也可以搞定。

比如下面這個案例，要根據會員的消費金額查找其所處的會員等級。

當消費金額處在兩級會員等級之間時，按較低一級的等級算，比如消費金額3333，處于會員等級三級和四級之間，那么該會員屬于三級會員，只有達到5000消費金額后才算四級會員。

E2輸入以下公式，向下填充。

=VLOOKUP(D2,$A$2:$B$8,2)

9、VLOOKUP函數按指定次數重復數據

工作中一些復雜場景會遇到按指定次數重復數據的需求，如下圖所示。

D列黃色區域是由公式自動生成的重復數據，當左側的數據源變動時，D列會按照指定的重復次數自動更新。

這里使用的是一個數組公式，以D2為例，輸入以下數組公式后按<Ctrl+Shfit+Enter>結束輸入。

=IFERROR(VLOOKUP(ROW(A1),IF({1,0},SUBTOTAL(9,OFFSET(A$2,,,ROW($1:$3))),B$2:B$4),2,),D3)&""

這個思路和方法都很贊，轉給朋友們分享一下吧~

10、VLOOKUP函數返回查找到的多個值

都知道VLOOKUP的常規用法下，當有多個查找值滿足條件時，只會返回從上往下找到的第一個值，那么如果需要VLOOKUP函數一對多查找時，返回查找到的多個值，有辦法實現嗎?答案是肯定的。

結合案例來看。

下面表格中左側是數據源，當右側D2單元格選擇不同的著作時，需要黃色區域返回根據D2查找到的多個值。

在這里，先給出遇到這種情況最常用的一個數組公式

E2單元格輸入以下數組公式，按<Ctrl+Shift+Enter>組合鍵結束輸入。

=INDEX(B:B,SMALL(IF(A$2:A$11=D$2,ROW($2:$11),4^8),ROW(A1)))&""

這是經典的一對多查找時使用的INDEX+SMALL+IF組合。

用VLOOKUP函數的公式，我也給出，E2輸入數組公式，按<Ctrl+Shift+Enter>組合鍵結束輸入。

=IF(COUNTIF(A$2:A$11,D$2)<ROW(A1),"",VLOOKUP(D$2&ROW(A1),IF({1,0},A$2:A$11&COUNTIF(INDIRECT("A2:A"&ROW($2:$11)),A$2:A$11),B$2:B$11),2,))

11、VLOOKUP函數在合并單元格中查找

合并單元格，這個東東大家在工作中太常見了吧。

在工作中盡量避免合并單元格，尤其是在數據處理過程中。但這并不能避免跟合并單元格打交道，因為數據源來自的渠道太多了，遇到了合并單元格也不能影響到數據處理和分析過程。

下面結合一個案例，介紹合并單元格中如何使用VLOOKUP函數查找。

注意到左側的班級列包含多個合并單元格且都是3行一合并，右側的查找是根據班級和名次進行雙條件查找。注意是從合并單元格中查找哦。

最簡便的辦法是在數據源左側做個輔助列，將合并單元格拆分并填充，這就回歸到前面介紹過的多條件查找的用法了。這個案例不創建輔助列，也不改動數據源結構，直接使用公式進行數據提取。

G2輸入以下公式

=VLOOKUP(F2,OFFSET(B1:C1,MATCH(E2,A2:A10,),,3),2,)

12、VLOOKUP函數提取字符串中的數值

工作中有時會遇到從一串文本和數值混雜的字符串中提取數值的需求，如果字符串比較多而且經常變動，與其每次都手動提取數值，就不如寫好一個公式實現自動提取。當數據源更新時，公式結果還能自動刷新。

下面的案例中，可以看到字符串中包含的數值各式各樣，有整數也有一位小數、兩位和多位小數，還有百分比數值，使用公式都可以一次性批量提取(百分號提取出來默認按照小數形式顯示，可以設置格式改變顯示方式)。

首先給出數組公式，在B2輸入以下數組，按<Ctrl+Shift+Enter>組合鍵結束輸入。

=VLOOKUP(9E+307,MID(A2,MIN(IF(ISNUMBER(--MID(A2,ROW($1:$99),1)),ROW($1:$99))),ROW($1:$99))*{1,1},2)

13、VLOOKUP函數轉換數據行列結構

VLOOKUP函數不光能查找調用數據，還可以用來轉換數據源的布局，比如將行數據轉換為多行多列的區域數據，如下面案例。

數據源位于第二行，要將這個1行20列的行數據轉換為黃色區域所示的4行5列的布局。

選中P5:T8單元格區域，輸入以下區域數組公式，按<Ctrl+Shift+Enter>組合鍵結束輸入。

=VLOOKUP("*",$A$2:$T$2,((ROW(1:4)-1)*5+COLUMN(A:E)),0)

14、VLOOKUP函數疑難解答提示

在使用VLOOKUP函數的過程中，很容易遭遇公式返回錯誤值的困境，下面這些錯誤值總結了最常見的問題，介紹產生錯誤原因的同時還給出了排除錯誤值的方法。