excel的vlookup函數使用方法如下:
壹、精確查找
根據姓名查找對應部門:
輸入公式:=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的引用。
十三、有合並單元格的查找
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精確查找
即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>;三鍵結束。