函數VLOOKUP與LOOKUP在工作中的應用是非常廣泛的,下面從八個例子中介紹他們的用法,讓我們在實際工作中能找到更適合的方法去解決問題。
函數VLOOKUP(查找值,查找區域,要返回的結果在查找區域的第幾列,查找方式)
函數LOOKUP套路公式:LOOKUP(1,0/(條件1)* …*(條件n),返回區域)
一、從左往右查找
查找業務員為“小魚兒”的業績:
1、函數VLOOKUP
輸入公式:
=VLOOKUP(D2,A2:B8,2,0)
查找值:“小魚兒”,單元格D2
查找區域:A2:B8(查找區域的首列要包含查找值)
要返回的結果在B列,即在查找區域的第2列
查找方式:精確查找(0或FALSE)
2、函數LOOKUP
輸入公式:
=LOOKUP(1,0/(D2=A2:A8),B2:B8)
(D2=A2:A8)部分條件成立返回TRUE,不成立返回FALSE;
發生四則運算時TRUE相當於1,FALSE相當於0;
0/任何數=0,0/0=#DIV/0!,0/(D2=A2:A8)部分構成了一個由0和#DIV/0!組成的數組,即{#DIV/0!;#DIV/0!;#DIV/0!;0;#DIV/0!;#DIV/0!;#DIV/0!};
用大於第二參數所有數值的1作為查找值;
返回區域:返回業績在B列,即區域B2:B8
二、區間等級查找
1、函數VLOOKUP
輸入公式:
=VLOOKUP(B11,{0,"差";5000,"中";10000,"良";15000,"優"},2)
2、函數LOOKUP
輸入公式:
=LOOKUP(B11,{0,"差";5000,"中";10000,"良";15000,"優"})
三、通配符查找
根據客戶簡稱查找對應金額:
1、函數VLOOKUP
輸入公式:
=VLOOKUP("*"&D20&"*",A$20:B$26,2,)
星號(*)匹配任意一串字符。
2、函數LOOKUP
輸入公式:
=LOOKUP(1,0/FIND(D20,A$20:A$26),B$20:B$26)
函數LOOKUP不支持通配符,可以與函數FIND組合實現通配符查找。
四、查找通配符本身
1、函數VLOOKUP
輸入公式:
=VLOOKUP(SUBSTITUTE(D29,"~","~~"),A29:B35,2,0)
“~”作為通配符,在查找通配符本身時,需要在其前輸入“~”,該公式中用函數SUBSTITUTE將“~”替換成“~~”。
2、函數LOOKUP
輸入公式:
=LOOKUP(1,0/(D29=A29:A35),B29:B35)
五、反向查找
根據業績查找業務員:
1、函數VLOOKUP
輸入公式:
=VLOOKUP(D38,CHOOSE({1,2},B38:B44,A38:A44),2,0)
CHOOSE({1,2},B38:B44,A38:A44)部分將逆向轉為順項,返回{2560,"仰望";6740,"張三";12500,"雨夜";7486,"小魚兒";10560,"小玉";15000,"李四";3890,"星空"}
2、函數LOOKUP
輸入公式:
=LOOKUP(,0/(D38=B38:B44),A38:A44)
六、交叉查找
1、函數VLOOKUP
輸入公式:
=VLOOKUP(F47,A47:D53,MATCH(G46,A46:D46,0),0)
MATCH(G46,A46:D46,0)部分在單元格區域A46:D46中找到G46單元格2月的位置3;
用函數VLOOKUP查找業務員為小魚兒時,其返回區域A47:D53中對應的第3列中的值6749。
2、函數LOOKUP
輸入公式:
=LOOKUP(1,0/(A47:A53=F47),OFFSET(A47:A53,,MATCH(G46,B46:D46,0)))
七、查找返回多列數據
1、函數VLOOKUP
輸入公式:
=VLOOKUP($F56,$A56:$D61,COLUMN(B1),0)
性別在區域A56:D61中的第2列,戶籍在第3列,手機號碼在第4列,恰好構成一個2、3、4的序列;
函數COLUMN返回一引用的列號, COLUMN(B1)=2,公式右拉變成COLUMN(C1)、COLUMN(D1),恰好是序列2、3、4,不需要手動去更改數字。
2、函數LOOKUP
輸入公式:
=LOOKUP(,0/($F56=$A56:$A61),B56:B61)
八、多條件查找
按區域和部門兩個條件查找對應業績:
1、函數VLOOKUP
輸入公式:
=VLOOKUP(E64&F64,CHOOSE({1,2},A64:A69&B64:B69,C64:C69),2,0)
數組公式,按三鍵結束。
2、函數LOOKUP
輸入公式:
=LOOKUP(1,0/((A64:A69=E64)*(B64:B69=F64)),C64:C69)
通過以上例子,你是更喜歡用函數VLOOKUP還是函數LOOKUP呢?
看完別忘收藏轉發哦~