本文介紹Excel的萬能函數LOOKUP的18種用法大全，配合VLOOKUP、INDIRECT、MATCH、IF、LEFT、RIGHT、MID等各種函數的嵌套使用，再加上函數公式逐條超細緻解析，詳細程度堪比教科書。

▍先了解LOOKUP的二分法查找原理，可以理解為一分為二，一直分到不能再分為止。三個案例圖講述二分法查找原理：圖1、圖2、圖3

圖1

圖2

圖3

▍“二分法”的2個小規律，可以讓我們快速口算出LOOKUP的結果。

① 規律1：當查找值足夠大，比查找區域的數都大時，匹配的都是最後一個數。比如查找值是20,查找區域是{10,8,16,17,19}，LOOKUP匹配的是最後一個數19；當查找值是100，查找區域是{20,30,50,88,66,32}，匹配的是最後一個數32。

② 規律2：當查找區域是升序排列時，LOOKUP會從下往上找，第一個等于或小于查找值的數就是最終匹配的數。比如當查找值是100，查找區域是{20,30,50,80,100,100}，最終匹配的是最後一個數100；當查找值是100，查找區域是{20,30,50,98,99,101}，最終匹配的數是99。

▶所以下面的案例會用到查找值“座”和“9E 307”來匹配最後一個文本和數字。“座”這個字代表超級大的文本，找最後一個文本就用“座”；“9E 307”這個數字代表超級大的數字，找最後一個數字就用“9E 307”。

▍LOOKUP函數和VLOOKUP函數不一樣，它不用區分逆向還是正向查詢。

說了這麼多，LOOKUP的二分法查找可以完成哪些功能？

▼一、利用模糊查找對多級區間快速判定結果，教 IF函數和VLOOKUP函數怎麼低調做函數。

公式模闆：=LOOKUP(查找值，1行或1列的查找區域，1行或1列的結果區域）。查找區域要升序排列。

◆如圖4：求銷售額的提成區間，對員工評級，LOOKUP信手捏來。

圖4：銷售區間員工提成判定，等級評級

如果沒有右邊的比例表格，公式可以直接寫成數組形式，比如求提成比例：D2單元格=LOOKUP(C3,{0;2000;5000;8000;10000},{0.01;0.03;0.05;0.1;0.12})，單元格格式設置成百分比格式就可以了。

◆如圖5：對這些員工的身高進行判定，得出他們适合穿什麼尺碼的衣服，170到174歸到170這一檔，175到179歸到175這一檔，依次類推。在D3單元格裡輸入公式=LOOKUP(C3,$G$3:$M$3,$G$2:$M$2) 。

圖5：LOOKUP多區間判定

PK環節：

如果用VLOOKUP函數來完成，隻能用VLOOKUP函數的模糊查找功能，而且右邊的尺碼表還得轉置，而且查找區域也要升序排列，這裡VLOOKUP完敗。

如果是用IF函數來完成，就得輸入這麼長的公式，7個IF函數嵌套，=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函數完敗。

圖6：用IF函數奔潰了

▼二 、單條件精确查找

公式模闆：=LOOKUP（1，0 / (查找值=1行或1列查找區域)，1行或1列結果區域） 。

如圖7：通過人名求部門和工号，G2單元格輸入公式=LOOKUP(1,0/($B$2:$B$5=$F2),$A$2:$A$5) 。其中 0/($B$2:$B$5=$F2)為查找區域，$A$2:$A$5為結果區域。公式詳解看下圖

圖7：單條件精确查找

▼三 、多條件精确查找

公式模闆：=LOOKUP（1，0 / ((查找值1=查找區域1)*(查找值2=查找區域2)*……），1行或1列結果區域） 。

如圖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)乘号*相當于AND函數，要兩條件同時滿足。$D$2:$D$5為結果區域。公式詳解看下圖

圖8：多條件精确查找

▼四 、制作查詢表，可以分類别查找最後一條記錄

公式模闆：=LOOKUP（1，0 / (查找值=1行或1列查找區域)，1行或1列結果區域） 。

如圖9：可以在E3單元格輸入不同的“産品名稱”，就會自動出現對應的“最後出庫時間和出庫人”。

圖9：按要求查最後一條記錄

在日期列F2單元格輸入公式=LOOKUP(1,0/($E3=$B:$B),$A:$A)，公式解析：E3人名在B列出現一次就會顯示一個TRUE，不出現就是FALSE；最後查找區域就隻剩{0；0；0……}，查找值1在一堆的0裡找最終結果，因為一堆0可以理解為升序狀态，從下往上找第一個小于或等于自身查找值的數就是要的值，所以1模糊匹配最後一個0，最後一個0對應的結果日期就是要的值。

在出庫人列G3單元格輸入公式=LOOKUP(1,0/($E3=$B:$B),$C:$C)，也是同理。公式可以下拉，同時查多個産品的最後出庫時間。

▼五 、合并單元格不用取消，正向查找引用，VLOOKUP配合LOOKUP就可做到

公式模闆：=VLOOKUP(LOOKUP("座",1列或1行查找區域)，多行多列查找區域，結果在第幾列，精确查找0） 。

如圖10：A列的合并單元格不取消，照樣可以引用A10:B13的單價，C3單元格輸入公式=VLOOKUP(LOOKUP("座",$A$3:A3),$A$11:$B$13,2,0)，再鼠标下拉單元格。公式詳解看下圖

圖10：合并單元格不用取消，照樣用公式引用成功

▼六 、合并單元格不用取消，逆向查找引用

公式模闆：=LOOKUP("座",INDIRECT("$列或行$數：$列或行"&MATCH(查找值,$列或行$1：$列或行數,0))) 。

如圖11：A列合并單元格不用取消，在B3單元格輸入公式=LOOKUP("座",INDIRECT("$A$8:$A"&MATCH($A3,$B$1:$B$13,0)))，引用成功。公式詳解看下圖

圖11：用LOOKUP和INDIRECT、MATCH函數組合

▼七 、拆分合并單元格并自動填充内容

① 拆分行方向合并單元格并自動填充内容

公式模闆：=LOOKUP("座",查找區域) 。

如圖12：第3行有合并單元格，是“一月”和“二月”。現在想插入一行，快速将第三行的内容複制下來，同時拆分合并單元格并自動填充原來合并單元格的内容。在A4單元格輸入公式=LOOKUP("座",$A$3:A$3)。

圖12：LOOKUP行方向自動拆分單元格并複制内容

② 拆分列方向合并單元格并自動填充内容

公式模闆：=LOOKUP("座",查找區域) 。

如圖13：A列有合并單元格，是“地方名”和“合計”。現在想插入一列，快速将A列的内容複制下來，同時拆分合并單元格并自動填充原來合并單元格的内容。在A4單元格輸入公式=LOOKUP("座",$B$4:$B4)。

圖13：LOOKUP列方向自動拆分單元格并複制内容

▼八 、通過全稱查簡稱

公式模闆：=LOOKUP(9E 307,FIND(簡稱的查找值,全稱的查找區域),結果區域)。

FIND函數的第一參數必須是簡稱内容，第二參數必須是全稱内容。不然會錯誤。

如圖14：在B10單元格輸入公式：=LOOKUP(9E 307,FIND(A10,$A$2:$A$6),$B$2:$B$6)。公式詳解看下圖

圖14：通過全稱查簡稱

▼九 、通過簡稱查全稱

公式模闆：=LOOKUP(9E 307,FIND(簡稱的查找區域,全稱的查找值),結果區域)。

FIND函數的第一參數必須是簡稱内容，第二參數必須是全稱内容。不然會錯誤。

如圖15：在B3單元格輸入公式=LOOKUP(9E 307,FIND($A$10:$A$14,A3),$B$10:$B$14)。公式詳解看下圖

圖15：通過簡稱查全稱

▼十 、查找引用一行或一列的最後一個數字、最後一個文本、最後一個非空内容

公式模闆：公式中的$A$1:$F$1可以換成任何需要的區域 。如圖16

圖16：查最後一個數字、文本、非空内容

▼十一 、提取左邊數字

公式模闆：= -LOOKUP(1,-LEFT(查找值,ROW($1:$足夠大的數字)))，最後一定要按CTRL Shift 回車，三鍵，不然會出錯。公式詳解看下圖17：

圖17：提取左邊數字

▼十二 、提取右邊數字

公式模闆：= -LOOKUP(1,-RIGHT(查找值,ROW($1:$足夠大的數字)))，最後一定要按CTRL Shift 回車，三鍵，不然會出錯。公式詳解看下圖18：

圖18：提取右邊數字

▼十三 、提取中間數字

公式模闆：= -LOOKUP(1,-MIDB(查找值,SEARCHB("?",查找值),ROW($1:$足夠大的數字)))，最後一定要按CTRL Shift 回車，三鍵，不然會出錯。公式詳解看下圖19：

圖20：提取中間數字

▼十四 、提取任意位置的數字

公式模闆：= -LOOKUP(1,-MID(查找值,MIN(FIND(ROW($1:$10)-1,查找值&1/17)),ROW($1:$足夠大的數字)))，最後一定要按CTRL Shift 回車，三鍵，不然會出錯。公式詳解看下圖20：

圖20：提取任意位置的數字

▼十五 、提取排名前幾的人員信息

假如要提取排名前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是查找區域，A$3:A$8是結果區域，這兩個區域以實際要求的内容來定。如圖21：詳見公式解析圖

圖21：提取排名前3的人員信息

函數LARGE（數據區域，第幾大值），比如第1大值，返回“數據區域中最大的數值”；比如第3大值，返回“數據區域中第3大的數值”。ROW($A1)下拉單元格會變成ROW($A2)、ROW($A3)。

以上就是統計的LOOKUP函數的18種用法及詳細的函數分析，喜歡的朋友請支持下，點個關注、轉發、收藏、點贊，謝謝！

更多精彩资讯请关注tft每日頭條，我们将持续为您更新最新资讯!