Excel:史上最全的VLOOKUP應用教程

VLOOKUP語法格式:

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

VLOOKUP(要查找的值,查找區域,要返回的結果在查找區域的第幾列,精確匹配或近似匹配)

相信大傢都知道Excel不僅僅是辦公必備軟件,它還是數據分析當中非常重要的工具。如果你能非常熟練的掌握Excel,那麼數據分析就非常容易入門瞭。為瞭讓大傢更深入的瞭解數據分析,這裡推薦一個數據分析入門課程,知乎知學堂官方出品的,個人覺得是入門數據分析非常全面的課程瞭。感興趣的小夥伴,抓緊領取瞭,僅需0.1元!

一、精確查找

根據姓名查找對應部門:

輸入公式:=VLOOKUP(G2,A:C,3,0)

G2:要查找的內容

A:C:查找區域,註意查找區域的首列要包含查找的內容

3:要返回的結果在查找區域的第3列

0:精確查找

二、近似查找

根據分數查找對應等級:

輸入公式:=VLOOKUP(B2,E:F,2,1)

B2:要查找的內容

E:F:查找區域,註意查找區域的首列要包含查找的內容

2:要返回的結果在查找區域的第2列

1:近似查找

註意查找區域中的首列內容必須以升序排序。

三、格式不一致的查找

查找數據為4的數量:

輸入公式:=VLOOKUP(D2,A:B,2,0)

D2:要查找的內容

A:B:查找區域,註意查找區域的首列要包含查找的內容

2:要返回的結果在查找區域的第2列

0:精確查找

這都沒錯啊,為什麼結果會返回錯誤值#N/A呢?

細看之下你就會發現格式不一致

查找值數值型(D2單元格內容4是數值型)

查找區域文本型(A列的數據是文本型)

遇到這樣的問題該怎麼解決呢?

格式一致

一是可以利用分列功能將A列分列成常規,與D2單元格格式一致

二是可以將D2單元格內容設成文本格式,與A列格式一致

三是變公式

公式:=VLOOKUP(D2&"",A:B,2,0)

將查找值連接空(&"")變為文本

接下來順便說下另一種格式不一致問題:

查找值文本型,查找區域數值型

查找值文本型(D2單元格內容4是文本型)

查找區域數值型(A列的數據是數值型)

輸入公式:

=VLOOKUP(D2^1,A:B,2,0)

^1是將查找值轉換成和查找區域一致的格式

轉換方法多種:–、+0、-0、*1、/1…等等

四、通配符查找

根據簡稱查找對應應收賬款:

輸入公式:

=VLOOKUP("*"&D2&"*",A:B,2,0)

星號(*)匹配任意一串字符。

五、帶“~”的查找

根據姓名查找對應部門:

公式沒有錯,結果為什麼會返回錯誤值#N/A呢?

因為查找內容帶波形符(~)

輸入公式:

=VLOOKUP(SUBSTITUTE(G2,"~","~~"),A:C,3,0)

在查找包含通配符其本身內容時,需在通配符前鍵入“~”

用函數SUBSTITUTE將“~”替換成“~~”。

六、取消合並單元格

內容為數值,取消合並單元格:

輸入公式:

=VLOOKUP(9E+307,A$2:A2,1,1)

9E+307是科學記數,表示9*10^307,是Excel允許鍵入的最大數值。

內容為文本,取消合並單元格:

輸入公式:

=VLOOKUP("座",E$2:E2,1,1)

七、查找第一次價格

根據物料名稱查找對應第一次價格:

輸入公式:

=VLOOKUP(F2,B:D,3,0)

當查找區域首列出現有兩個或更多值與查找值匹配時,函數VLOOKUP返回第一次出現的對應值。

八、交叉查詢

根據產品和地區查找對應銷量:

輸入公式:

=VLOOKUP(A12,A2:G8,MATCH(B12,A1:G1,0),0)

MATCH(B12,A1:G1,0)部分找到B12單元格內容“華北地區”在區域A1:G1中的位置5,把它作為VLOOKUP函數的第3參數;

公式就是:=VLOOKUP(A12,A2:G8,5,0)

查找A12單元格內容“產品D”

返回值在區域A2:G8中的第5列,即E列

即E5單元格中的值6945

九、反向查找

根據工號查找對應姓名:

函數VLOOKUP可以借助IF{1,0}與IF{0,1}、CHOOSE{1,2}與CHOOSE{2,1}等等結構將逆序轉換為順序,從而實現查找。

函數VLOOKUP+ IF{1,0}結構:

輸入公式:

=VLOOKUP(D2,IF({1,0},B2:B11,A2:A11),2,0)

IF({1,0},B2:B11,A2:A11)部分

當為1時條件成立返回B2:B11

當為0時條件不成立返回A2:A11

可以將IF({1,0},B2:B11,A2:A11)部分抹黑按F9鍵查看

就是兩列順序對換,將逆序轉換為順序

函數VLOOKUP+ IF{0,1}結構:

輸入公式:

=VLOOKUP(D2,IF({0,1},A2:A11,B2:B11),2,0)

函數VLOOKUP+CHOOSE{1,2}結構:

輸入公式:

=VLOOKUP(D2,CHOOSE({1,2},B2:B11,A2:A11),2,0)

函數CHOOSE:根據給定的索引值,從參數串中選出相應值或操作。

CHOOSE(index_num, value1, [value2], …)

如果第一參數為1,則CHOOSE返回value1;如果第一參數為2,則CHOOSE返回value2。

CHOOSE({1,2},B2:B11,A2:A11)部分

當條件為1時,返回B2:B11

當條件為2時,返回A2:A11

函數VLOOKUP+CHOOSE{2,1}結構:

輸入公式:

=VLOOKUP(D2,CHOOSE({2,1},A2:A11,B2:B11),2,0)

CHOOSE({2,1},A2:A11,B2:B11)部分

當第一參數為2時,則CHOOSE返回對應B2:B11中的值;

當第一參數為1時,則CHOOSE返回對應A2:A11中的值。

把CHOOSE({2,1},A2:A11,B2:B11)部分抹黑按F9鍵查看

AB兩列順序對換,將逆序轉換為順序,再用函數VLOOKUP查找。

十、查找返回多列數據

輸入公式:

=VLOOKUP($F2,$A:$D,COLUMN(B1),0),右拉填充

公式右拉返回結果在第2、3、4列

用函數COLUMN構造

COLUMN(B1)=2,公式右拉變成COLUMN(C1)、COLUMN(D1)得到3、4。

十一、按指定次數重復

輸入公式:

=IFERROR(VLOOKUP(ROW(A1),IF({1,0},SUMIF(OFFSET(B$2,,,ROW($1:$4)),"<>"),A$2:A$5),2,0),E2)&""

按<Ctrl+Shift+Enter>三鍵結束

十二、結果引用合並單元格內容

A列區域為合並單元格,根據業務員查找對應的區域:

輸入公式:

=VLOOKUP("座",OFFSET(A2,,,MATCH(D2,B2:B14,0)),1,1)

MATCH(D2,B2:B14,0)部分找到業務員“阿文”在區域B2:B14中的位置11

OFFSET(基點,偏移行數,偏移列數,行高,列寬)

OFFSET(A2,,,11)是以A2單元格為基點,偏移0行0列,返回行高為11的新區域A2:A12的引用。

OFFSET部分抹黑按F9鍵得到:

用“座”等較大的漢字查找區域中最後一個單元格內容,即返回“華北地區”。

十三、有合並單元格的查找

A列產品為合並單元格,如何查找A列產品對應的單價呢?

輸入公式:

=VLOOKUP(VLOOKUP("座",A$2:A2,1,1),F:G,2,0)

比如D5單元格公式=VLOOKUP(VLOOKUP("座",A$2:A5,1,1),F:G,2,0)

A$2:A5部分返回{"產品1";"產品3";0;0}

VLOOKUP("座",A$2:A5,1,1)部分用"座"查找最後一個單元格內容,即返回“產品3”

外層再套個VLOOKUP精確查找

即D5單元格公式就是=VLOOKUP("產品3",F:G,2,0),返回單價12

十四、與T+IF的組合應用

輸入公式:

=SUM(VLOOKUP(T(IF({1},A2:A8)),D2:E8,2,0)*B2:B8)

數組公式,按<Ctrl+Shift+Enter>三鍵結束

IF({1},A2:A8)部分構成三維內存數組

VLOOKUP函數第一參數不能直接為數組

函數T起降維作用,將三維引用轉換為一維數組,其返回的結果仍為數組,用函數SUM求和。

十五、多條件查找

與反向查找一樣,可以借助IF{1,0}與IF{0,1}、CHOOSE{1,2}與CHOOSE{2,1}等結構

輸入公式:

=VLOOKUP(E2&F2,IF({1,0},A2:A11&B2:B11,C2:C11),2,0)

數組公式,按<Ctrl+Shift+Enter>三鍵結束

十六、一對多查找

輸入公式:

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

數組公式,按<Ctrl+Shift+Enter>三鍵結束

效果圖:

十七、動態圖表

【數據】→【數據驗證】

輸入公式:

=VLOOKUP($A9,$A$2:$G$5,COLUMN(B1),0) ,右拉填充

【插入】→【插入柱形圖】

操作演示:

如果大傢能夠熟練的掌握Excel,那麼入門數據分析就會變得非常簡單瞭。為瞭讓大傢更好的入門數據分析,這裡推薦一個知乎知學堂官方推出的數據分析課程,僅需0.1元,感興趣的小夥伴可以點擊下方卡片瞭解哦!

赞(0)