|
一個人人都愛、使用頻率最高的函數，有關他的用法，你真的掌握了嗎？ 語法格式： VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) VLOOKUP（要查找的值，查找區(qū)域，要返回的結果在查找區(qū)域的第幾列，精確匹配或近似匹配） 1、精確查找 根據姓名查找對應部門： 輸入公式：=VLOOKUP(G2,A:C,3,0) G2：要查找的內容 A:C：查找區(qū)域，注意查找區(qū)域的首列要包含查找的內容 3：要返回的結果在查找區(qū)域的第3列 0：精確查找 2、近似查找 根據分數查找對應等級： 輸入公式：=VLOOKUP(B2,E:F,2,1) B2：要查找的內容 E:F：查找區(qū)域，注意查找區(qū)域的首列要包含查找的內容 2：要返回的結果在查找區(qū)域的第2列 1：近似查找 注意查找區(qū)域中的首列內容必須以升序排序。 3、格式不一致的查找 查找數據為4的數量： 輸入公式：=VLOOKUP(D2,A:B,2,0) D2：要查找的內容 A:B：查找區(qū)域，注意查找區(qū)域的首列要包含查找的內容 2：要返回的結果在查找區(qū)域的第2列 0：精確查找 這都沒錯啊，為什么結果會返回錯誤值#N/A呢？ 細看之下你就會發(fā)現格式不一致 查找值數值型（D2單元格內容4是數值型） 查找區(qū)域文本型（A列的數據是文本型） 遇到這樣的問題該怎么解決呢？ 格式一致 一是可以利用分列功能將A列分列成常規(guī)，與D2單元格格式一致 二是可以將D2單元格內容設成文本格式，與A列格式一致 三是變公式 公式：=VLOOKUP(D2&'',A:B,2,0) 將查找值連接空（&''）變?yōu)槲谋?/p> 接下來順便說下另一種格式不一致問題： 查找值文本型，查找區(qū)域數值型 查找值文本型（D2單元格內容4是文本型） 查找區(qū)域數值型（A列的數據是數值型） 輸入公式： =VLOOKUP(D2^1,A:B,2,0) ^1是將查找值轉換成和查找區(qū)域一致的格式 轉換方法多種：--、+0、-0、*1、/1...等等 4、通配符查找 根據簡稱查找對應應收賬款： 輸入公式： =VLOOKUP('*'&D2&'*',A:B,2,0) 星號（*）匹配任意一串字符。 5、帶“~”的查找 根據姓名查找對應部門：
公式沒有錯，結果為什么會返回錯誤值#N/A呢？ 因為查找內容帶波形符（~）
輸入公式： =VLOOKUP(SUBSTITUTE(G2,'~','~~'),A:C,3,0) 在查找包含通配符其本身內容時，需在通配符前鍵入“~” 用函數SUBSTITUTE將“~”替換成“~~”。 6、取消合并單元格 內容為數值，取消合并單元格：
輸入公式： =VLOOKUP(9E+307,A$2:A2,1,1) 9E+307是科學記數，表示9*10^307，是Excel允許鍵入的最大數值。 內容為文本，取消合并單元格：
輸入公式： =VLOOKUP('座',E$2:E2,1,1) 7、查找第一次價格
根據物料名稱查找對應第一次價格：
輸入公式： =VLOOKUP(F2,B:D,3,0) 當查找區(qū)域首列出現有兩個或更多值與查找值匹配時，函數VLOOKUP返回第一次出現的對應值。 8、交叉查詢 根據產品和地區(qū)查找對應銷量：
輸入公式： =VLOOKUP(A12,A2:G8,MATCH(B12,A1:G1,0),0) MATCH(B12,A1:G1,0)部分找到B12單元格內容“華北地區(qū)”在區(qū)域A1:G1中的位置5，把它作為VLOOKUP函數的第3參數； 公式就是：=VLOOKUP(A12,A2:G8,5,0) 查找A12單元格內容“產品D” 返回值在區(qū)域A2:G8中的第5列，即E列 即E5單元格中的值6945 9、反向查找 根據工號查找對應姓名：
函數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查找。 10、查找返回多列數據
輸入公式： =VLOOKUP($F2,$A:$D,COLUMN(B1),0)，右拉填充 公式右拉返回結果在第2、3、4列 用函數COLUMN構造 COLUMN(B1)=2，公式右拉變成COLUMN(C1)、COLUMN(D1)得到3、4。 11、按指定次數重復
輸入公式： =IFERROR(VLOOKUP(ROW(A1),IF({1,0},SUMIF(OFFSET(B$2,,,ROW($1:$4)),'<>'),A$2:A$5),2,0),E2)&'' 按<Ctrl+Shift+Enter>三鍵結束
12、結果引用合并單元格內容 A列區(qū)域為合并單元格，根據業(yè)務員查找對應的區(qū)域：
輸入公式： =VLOOKUP('座',OFFSET(A2,,,MATCH(D2,B2:B14,0)),1,1) MATCH(D2,B2:B14,0)部分找到業(yè)務員“阿文”在區(qū)域B2:B14中的位置11 OFFSET（基點，偏移行數，偏移列數，行高，列寬） OFFSET(A2,,,11)是以A2單元格為基點，偏移0行0列，返回行高為11的新區(qū)域A2:A12的引用。 OFFSET部分抹黑按F9鍵得到：
用“座”等較大的漢字查找區(qū)域中最后一個單元格內容，即返回“華北地區(qū)”。 13、有合并單元格的查找 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 14、與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求和。 15、多條件查找 與反向查找一樣，可以借助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>三鍵結束 16、一對多查找
輸入公式： =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>三鍵結束 效果圖：
17、動態(tài)圖表 【數據】→【數據驗證】
輸入公式： =VLOOKUP($A9,$A$2:$G$5,COLUMN(B1),0) ，右拉填充
【插入】→【插入柱形圖】 操作演示：
|
|
|
