本文介紹Excel的萬能函數(shù)LOOKUP的18種用法大全，配合VLOOKUP、INDIRECT、MATCH、IF、LEFT、RIGHT、MID等各種函數(shù)的嵌套使用，再加上函數(shù)公式逐條超細(xì)致解析，詳細(xì)程度堪比教科書。
▍先了解LOOKUP的二分法查找原理，可以理解為一分為二，一直分到不能再分為止。三個(gè)案例圖講述二分法查找原理：圖1、圖2、圖3
圖1
圖2
圖3
▍“二分法”的2個(gè)小規(guī)律，可以讓我們快速口算出LOOKUP的結(jié)果。
①規(guī)律1：當(dāng)查找值足夠大，比查找區(qū)域的數(shù)都大時(shí)，匹配的都是最后一個(gè)數(shù)。比如查找值是20,查找區(qū)域是{10,8,16,17,19}，LOOKUP匹配的是最后一個(gè)數(shù)19；當(dāng)查找值是100，查找區(qū)域是{20,30,50,88,66,32}，匹配的是最后一個(gè)數(shù)32。
②規(guī)律2：當(dāng)查找區(qū)域是升序排列時(shí)，LOOKUP會(huì)從下往上找，第一個(gè)等于或小于查找值的數(shù)就是最終匹配的數(shù)。比如當(dāng)查找值是100，查找區(qū)域是{20,30,50,80,100,100}，最終匹配的是最后一個(gè)數(shù)100；當(dāng)查找值是100，查找區(qū)域是{20,30,50,98,99,101}，最終匹配的數(shù)是99。
?所以下面的案例會(huì)用到查找值“座”和“9E+307”來匹配最后一個(gè)文本和數(shù)字。“座”這個(gè)字代表超級(jí)大的文本，找最后一個(gè)文本就用“座”；“9E+307”這個(gè)數(shù)字代表超級(jí)大的數(shù)字，找最后一個(gè)數(shù)字就用“9E+307”。
▍LOOKUP函數(shù)和VLOOKUP函數(shù)不一樣，它不用區(qū)分逆向還是正向查詢。
說了這么多，LOOKUP的二分法查找可以完成哪些功能？
▼一、利用模糊查找對(duì)多級(jí)區(qū)間快速判定結(jié)果，教 IF函數(shù)和VLOOKUP函數(shù)怎么低調(diào)做函數(shù)。
公式模板：=LOOKUP(查找值，1行或1列的查找區(qū)域，1行或1列的結(jié)果區(qū)域）。查找區(qū)域要升序排列。
◆如圖4：求銷售額的提成區(qū)間，對(duì)員工評(píng)級(jí)，LOOKUP信手捏來。
圖4：銷售區(qū)間員工提成判定，等級(jí)評(píng)級(jí)
如果沒有右邊的比例表格，公式可以直接寫成數(shù)組形式，比如求提成比例：D2單元格=LOOKUP(C3,{0;2000;5000;8000;10000},{0.01;0.03;0.05;0.1;0.12})，單元格格式設(shè)置成百分比格式就可以了。
◆如圖5：對(duì)這些員工的身高進(jìn)行判定，得出他們適合穿什么尺碼的衣服，170到174歸到170這一檔，175到179歸到175這一檔，依次類推。在D3單元格里輸入公式=LOOKUP(C3,$G$3:$M$3,$G$2:$M$2) 。
圖5：LOOKUP多區(qū)間判定
PK環(huán)節(jié)：
如果用VLOOKUP函數(shù)來完成，只能用VLOOKUP函數(shù)的模糊查找功能，而且右邊的尺碼表還得轉(zhuǎn)置，而且查找區(qū)域也要升序排列，這里VLOOKUP完敗。
如果是用IF函數(shù)來完成，就得輸入這么長(zhǎng)的公式，7個(gè)IF函數(shù)嵌套，=IF(C3<165,'XS',IF(C3<170,'S',IF(C3<175,'M',IF(C3<180,'L',IF(C3<185,'XL',IF(C3<190,'2XL',IF(C3>=190,'3XL',''))))))) 。IF函數(shù)完敗。
圖6：用IF函數(shù)奔潰了
▼二 、單條件精確查找
公式模板：=LOOKUP（1，0 / (查找值=1行或1列查找區(qū)域)，1行或1列結(jié)果區(qū)域） 。
如圖7：通過人名求部門和工號(hào)，G2單元格輸入公式=LOOKUP(1,0/($B$2:$B$5=$F2),$A$2:$A$5) 。其中 0/($B$2:$B$5=$F2)為查找區(qū)域，$A$2:$A$5為結(jié)果區(qū)域。公式詳解看下圖
圖7：?jiǎn)螚l件精確查找
▼三 、多條件精確查找
公式模板：=LOOKUP（1，0 / ((查找值1=查找區(qū)域1)*(查找值2=查找區(qū)域2)*……），1行或1列結(jié)果區(qū)域） 。
如圖8：求薪資，但是有同名的人，所以“部門*人名”就變成唯一性了。H2單元格輸入公式 =LOOKUP(1,0/(($F2=$B$2:$B$5)*($G2=$A$2:$A$5)),$D$2:$D$5) 。
其中 ($F2=$B$2:$B$5)*($G2=$A$2:$A$5)乘號(hào)*相當(dāng)于AND函數(shù)，要兩條件同時(shí)滿足。$D$2:$D$5為結(jié)果區(qū)域。公式詳解看下圖
圖8：多條件精確查找
▼四 、制作查詢表，可以分類別查找最后一條記錄
公式模板：=LOOKUP（1，0 / (查找值=1行或1列查找區(qū)域)，1行或1列結(jié)果區(qū)域） 。
如圖9：可以在E3單元格輸入不同的“產(chǎn)品名稱”，就會(huì)自動(dòng)出現(xiàn)對(duì)應(yīng)的“最后出庫時(shí)間和出庫人”。
圖9：按要求查最后一條記錄
在日期列F2單元格輸入公式=LOOKUP(1,0/($E3=$B:$B),$A:$A)，公式解析：E3人名在B列出現(xiàn)一次就會(huì)顯示一個(gè)TRUE，不出現(xiàn)就是FALSE；最后查找區(qū)域就只剩{0；0；0……}，查找值1在一堆的0里找最終結(jié)果，因?yàn)橐欢?可以理解為升序狀態(tài)，從下往上找第一個(gè)小于或等于自身查找值的數(shù)就是要的值，所以1模糊匹配最后一個(gè)0，最后一個(gè)0對(duì)應(yīng)的結(jié)果日期就是要的值。
在出庫人列G3單元格輸入公式=LOOKUP(1,0/($E3=$B:$B),$C:$C)，也是同理。公式可以下拉，同時(shí)查多個(gè)產(chǎn)品的最后出庫時(shí)間。
▼五 、合并單元格不用取消，正向查找引用，VLOOKUP配合LOOKUP就可做到
公式模板：=VLOOKUP(LOOKUP('座',1列或1行查找區(qū)域)，多行多列查找區(qū)域，結(jié)果在第幾列，精確查找0） 。
如圖10：A列的合并單元格不取消，照樣可以引用A10:B13的單價(jià)，C3單元格輸入公式=VLOOKUP(LOOKUP('座',$A$3:A3),$A$11:$B$13,2,0)，再鼠標(biāo)下拉單元格。公式詳解看下圖
圖10：合并單元格不用取消，照樣用公式引用成功
▼六 、合并單元格不用取消，逆向查找引用
公式模板：=LOOKUP('座',INDIRECT('$列或行$數(shù)：$列或行'&MATCH(查找值,$列或行$1：$列或行數(shù),0))) 。
如圖11：A列合并單元格不用取消，在B3單元格輸入公式=LOOKUP('座',INDIRECT('$A$8:$A'&MATCH($A3,$B$1:$B$13,0)))，引用成功。公式詳解看下圖
圖11：用LOOKUP和INDIRECT、MATCH函數(shù)組合
▼七 、拆分合并單元格并自動(dòng)填充內(nèi)容
① 拆分行方向合并單元格并自動(dòng)填充內(nèi)容
公式模板：=LOOKUP('座',查找區(qū)域) 。
如圖12：第3行有合并單元格，是“一月”和“二月”。現(xiàn)在想插入一行，快速將第三行的內(nèi)容復(fù)制下來，同時(shí)拆分合并單元格并自動(dòng)填充原來合并單元格的內(nèi)容。在A4單元格輸入公式=LOOKUP('座',$A$3:A$3)。
圖12：LOOKUP行方向自動(dòng)拆分單元格并復(fù)制內(nèi)容
② 拆分列方向合并單元格并自動(dòng)填充內(nèi)容
公式模板：=LOOKUP('座',查找區(qū)域) 。
如圖13：A列有合并單元格，是“地方名”和“合計(jì)”?，F(xiàn)在想插入一列，快速將A列的內(nèi)容復(fù)制下來，同時(shí)拆分合并單元格并自動(dòng)填充原來合并單元格的內(nèi)容。在A4單元格輸入公式=LOOKUP('座',$B$4:$B4)。
圖13：LOOKUP列方向自動(dòng)拆分單元格并復(fù)制內(nèi)容
▼八 、通過全稱查簡(jiǎn)稱
公式模板：=LOOKUP(9E+307,FIND(簡(jiǎn)稱的查找值,全稱的查找區(qū)域),結(jié)果區(qū)域)。
FIND函數(shù)的第一參數(shù)必須是簡(jiǎn)稱內(nèi)容，第二參數(shù)必須是全稱內(nèi)容。不然會(huì)錯(cuò)誤。
如圖14：在B10單元格輸入公式：=LOOKUP(9E+307,FIND(A10,$A$2:$A$6),$B$2:$B$6)。公式詳解看下圖
圖14：通過全稱查簡(jiǎn)稱
▼九 、通過簡(jiǎn)稱查全稱
公式模板：=LOOKUP(9E+307,FIND(簡(jiǎn)稱的查找區(qū)域,全稱的查找值),結(jié)果區(qū)域)。
FIND函數(shù)的第一參數(shù)必須是簡(jiǎn)稱內(nèi)容，第二參數(shù)必須是全稱內(nèi)容。不然會(huì)錯(cuò)誤。
如圖15：在B3單元格輸入公式=LOOKUP(9E+307,FIND($A$10:$A$14,A3),$B$10:$B$14)。公式詳解看下圖
圖15：通過簡(jiǎn)稱查全稱
▼十 、查找引用一行或一列的最后一個(gè)數(shù)字、最后一個(gè)文本、最后一個(gè)非空內(nèi)容
公式模板：公式中的$A$1:$F$1可以換成任何需要的區(qū)域 。如圖16
圖16：查最后一個(gè)數(shù)字、文本、非空內(nèi)容
▼十一 、提取左邊數(shù)字
公式模板：= -LOOKUP(1,-LEFT(查找值,ROW($1:$足夠大的數(shù)字)))，最后一定要按CTRL+Shift+回車，三鍵，不然會(huì)出錯(cuò)。公式詳解看下圖17：
圖17：提取左邊數(shù)字
▼十二 、提取右邊數(shù)字
公式模板：= -LOOKUP(1,-RIGHT(查找值,ROW($1:$足夠大的數(shù)字)))，最后一定要按CTRL+Shift+回車，三鍵，不然會(huì)出錯(cuò)。公式詳解看下圖18：
圖18：提取右邊數(shù)字
▼十三 、提取中間數(shù)字
公式模板：= -LOOKUP(1,-MIDB(查找值,SEARCHB('?',查找值),ROW($1:$足夠大的數(shù)字)))，最后一定要按CTRL+Shift+回車，三鍵，不然會(huì)出錯(cuò)。公式詳解看下圖19：
圖20：提取中間數(shù)字
▼十四 、提取任意位置的數(shù)字
公式模板：= -LOOKUP(1,-MID(查找值,MIN(FIND(ROW($1:$10)-1,查找值&1/17)),ROW($1:$足夠大的數(shù)字)))，最后一定要按CTRL+Shift+回車，三鍵，不然會(huì)出錯(cuò)。公式詳解看下圖20：
圖20：提取任意位置的數(shù)字
▼十五 、提取排名前幾的人員信息
假如要提取排名前3的人員信息，公式模板：=IF(ROW($A1)>3,'',LOOKUP(1,0/(LARGE($B$3:$B$8,ROW($A1))=$B$3:$B$8),A$3:A$8))。$B$3:$B$8是查找區(qū)域，A$3:A$8是結(jié)果區(qū)域，這兩個(gè)區(qū)域以實(shí)際要求的內(nèi)容來定。如圖21：詳見公式解析圖
圖21：提取排名前3的人員信息
函數(shù)LARGE（數(shù)據(jù)區(qū)域，第幾大值），比如第1大值，返回“數(shù)據(jù)區(qū)域中最大的數(shù)值”；比如第3大值，返回“數(shù)據(jù)區(qū)域中第3大的數(shù)值”。ROW($A1)下拉單元格會(huì)變成ROW($A2)、ROW($A3)。
以上就是統(tǒng)計(jì)的LOOKUP函數(shù)的18種用法及詳細(xì)的函數(shù)分析，喜歡的朋友請(qǐng)支持下，點(diǎn)個(gè)關(guān)注、轉(zhuǎn)發(fā)、收藏、點(diǎn)贊，謝謝！
聯(lián)系客服
