函數篇：查找函數哪家強，LOOKUP我最強！

06-17

【2】工作實際運用Excel案例，帶你從入門走向技巧帝；
【3】開設excel培訓課程、工作運用工具開發。

都說VLOOKUP是什麼大眾情人、使用頻率最高的，哼，有了我LOOKUP，他VLOOKUP可以退隱山林了，去慢慢感嘆：既生我VLOOKUP，何生他LOOKUP！

函數語法解析

函數LOOKUP有兩種語法形式：向量形式和數組形式

向量形式

1、函數定義：

在單行區域或單列區域（稱為「向量」）中查找值，然後返回第二個單行區域或單列區域中相同位置的值。

2、語法格式：

LOOKUP(lookup_value,lookup_vector,result_vector)

LOOKUP（查找值，查找區域，結果區域）

3、參數說明：

①、lookup_value：必需。LOOKUP在第一個向量中搜索的值。

Lookup_value可以是數字、文本、邏輯值、名稱或對值的引用。

②、lookup_vector：必需。只包含一行或一列的區域。

lookup_vector中的值可以是文本、數字或邏輯值。

③、result_vector：可選。只包含一行或一列的區域。

result_vector參數必須與lookup_vector參數大小相同。其大小必須相同。

4、注意事項：

①、如果LOOKUP函數找不到lookup_value，則該函數會與lookup_vector中小於或等於lookup_value的最大值進行匹配。

②、如果lookup_value小於lookup_vector中的最小值，則LOOKUP會返回#N/A錯誤值。

③、重要:lookup_vector中的值必須按升序排列：...,-2,-1,0,1,2,...,A-Z,FALSE,TRUE；否則，LOOKUP可能無法返回正確的值。文本不區分大小寫。

數組形式

1、函數定義：

在數組的第一行或第一列中查找指定的值，並返回數組最後一行或最後一列中同一位置的值。

當要匹配的值位於數組的第一行或第一列中時，請使用LOOKUP的這種形式。

2、語法格式：

LOOKUP(lookup_value,array)

3、參數說明：

①、lookup_value：必需。LOOKUP在數組中搜索的值。

lookup_value參數可以是數字、文本、邏輯值、名稱或對值的引用。

②、array：必需。包含要與lookup_value進行比較的文本、數字或邏輯值的單元格區域。

4、注意事項：

①、如果LOOKUP找不到lookup_value的值，它會使用數組中小於或等於lookup_value的最大值。

②、如果lookup_value的值小於第一行或第一列中的最小值（取決於數組維度），LOOKUP會返回#N/A錯誤值。

③、LOOKUP的數組形式與HLOOKUP和VLOOKUP函數非常相似。區別在於：HLOOKUP在第一行中搜索lookup_value的值，VLOOKUP在第一列中搜索，而LOOKUP根據數組維度進行搜索。

④、如果數組包含寬度比高度大的區域（列數多於行數）LOOKUP會在第一行中搜索lookup_value的值。

⑤、如果數組是正方的或者高度大於寬度（行數多於列數），LOOKUP會在第一列中進行搜索。

⑥、使用HLOOKUP和VLOOKUP函數，您可以通過索引以向下或遍歷的方式搜索，但是LOOKUP始終選擇行或列中的最後一個值。

⑦、重要：數組中的值必須按升序排列：...,-2,-1,0,1,2, ...,A-Z,FALSE,TRUE；否則，LOOKUP可能無法返回正確的值。文本不區分大小寫。

經典套路（通用公式）：

1、LOOKUP(1,0/((條件1)*(條件2)*…*(條件n)),返回區域)

2、LOOKUP(2,1/((條件1)*(條件2)*…*(條件n)),返回區域)

函數示例

▲

經典用法

向量形式：

公式：

=LOOKUP(F3,A3:A8,D3:D8)

注意：lookup_vector中的值必須按升序排列。

數組形式：

公式：

=LOOKUP(F3,A3:D8)

注意：數組中的值必須按升序排列。

當要匹配的值位於數組的第一行或第一列中時，可以使用數組形式。

套路：

公式：

=LOOKUP(1,0/(N3=I3:I8),L3:L8)

注意：亂序情況也可以使用。

▲

區間查找

向量形式：

公式：

=LOOKUP(B14,E$14:E$17,F$14:F$17)

注意：lookup_vector中的值必須按升序排列。

數組形式：

公式：

=LOOKUP(B14,E$14:F$17)

注意：數組中的值必須按升序排列。

當要匹配的值位於數組的第一行或第一列中時，可以使用數組形式。

▲

格式不一致的查找

分兩種情況：

第一種：查找值文本型，查找區域數值型

向量形式：

公式：

=LOOKUP(D25*1,A25:A30,B25:B30)

解析：查找值和查找區域格式不一致將出現錯誤值#N/A

需將查找值轉換為和查找區域一樣的格式

轉換的方式很多種，比如： 0，-0，--，*1，/1，^1......等等。

注意：lookup_vector中的值必須按升序排列。

數組形式：

公式：

=LOOKUP(D25*1,A25:B30)

注意：數組中的值必須按升序排列。

當要匹配的值位於數組的第一行或第一列中時，可以使用數組形式。

套路：

公式：

=LOOKUP(2,1/(G25:G30=J25*1),H25:H30)

注意：亂序情況也可以使用。

第二種：查找值數值型，查找區域文本型

向量形式：

公式：

=LOOKUP(D36&"",A36:A41,B36:B41)

解析：查找值和查找區域格式不一致將出現錯誤值#N/A

查找值數值型，查找區域文本型，將查找值連接個空（&""）變為文本，格式統一後就能查找出正確結果了。

注意：lookup_vector中的值必須按升序排列。

數組形式：

公式：

=LOOKUP(D36&"",A36:B41)

注意：數組中的值必須按升序排列。

當要匹配的值位於數組的第一行或第一列中時，可以使用數組形式。

套路：

公式：

=LOOKUP(1,0/(G36:G41=J36&""),H36:H41)

注意：亂序情況也可以使用。

▲

查找最後一個文本

公式：

=LOOKUP("座",A47:A58)

解析：用「座」或「々」等較大的漢字查找區域中最後一個文本。

▲

查找最後一個數字

公式：

=LOOKUP(9E 307,A64:A75)

解析：9E 307是科學記數，表示9*10^307，是Excel允許鍵入的最大數值，可用來返回最後一個數值。

▲

查找最後一次採購單價

公式：

=LOOKUP(1,0/(B81:B92=E81),C81:C92)

通用公式：LOOKUP(1,0/(條件1),返回區域)

解析：

(B81:B92=E81)條件成立返回TRUE，條件不成立返回FALSE；

發生四則運算時TRUE相當於1，FALSE相當於0；

利用0/任何數=0、0/0=#DIV/0!的特性，0/(B81:B92=E81)部分構成了一個由0和#DIV/0!組成的數組；

用大於第二個參數所有數值的1作為查找值，即可查找出最後一次採購單價。

▲

通配符查找

公式：

=IFNA(LOOKUP(,0/FIND(D98,A$98:A$102),B$98:B$102),"")

注意：LOOKUP函數不支持通配符使用，可以用LOOKUP FIND組合

函數IFNA起容錯作用

有關函數IFNA的用法，請點擊下面鏈接：

函數篇：容錯高手IFERROR與IFNA

▲

取消合併單元格並填充

第一種：全部為文本

公式：

=LOOKUP("々",A$109:A109)

解析：可以用「々」或者「座」，不會打「々」的可以按快捷鍵<Alt 41385>

第二種：全部為數字

公式：

=LOOKUP(9E 307,F$109:F109)

第三種：文本數字混合

公式：

=LOOKUP(1,0/($I$109:I109<>""),$I$109:I109)

▲

查找返回多列數據

公式：

=LOOKUP(,0/($F131=$A131:$A136),B131:B136)，向右填充

▲

反向查找

公式：

=LOOKUP(,0/($F142=$D142:$D147),A142:A147)，向右填充

▲

交叉查詢

公式：

=LOOKUP(,0/(A153:A158=F153),OFFSET(A153:A158,,MATCH(G153,B152:D152,0)))

解析：MATCH(G153,B152:D152,0)部分找到5月在區域B152:D152中的位置為2；

OFFSET部分是以區域A153:A158為基點，偏移0行2列，返回新區域C153:C158的引用；

那麼公式就是LOOKUP(,0/(A153:A158=F153),C153:C158)

不這樣寫是為了實現動態查詢，當業務員或月份更改時，其對應數據區域自動改變，實現自動化查詢。

▲

合併單元格的查詢問題

公式：

=LOOKUP("座",OFFSET(A164,,,MATCH(G164,B164:B180,)))

解析：MATCH(G164,B164:B180,)部分找到客服「君柳」在區域B164:B180中的位置為8；

OFFSET（基點，偏移行數，偏移列數，行高，列寬）

OFFSET(A164,,,MATCH(G164,B164:B180,))是以A164單元格為基點，偏移0行0列，返回行高為8的新區域A164:A171的引用。

抹黑按F9得到：

用「座」或「々」等較大的漢字查找區域中最後一個文本。

▲

合併單元格的查詢問題

公式：

=LOOKUP(,0/(LOOKUP("座",A$186:A186)=G$186:G$192),H$186:H$192)

▲

提取不重複項

公式：

=LOOKUP(,0/FREQUENCY(0,ISNA(MATCH(A$208:A$216,E$207:E207,))-1),A$208:A208)&""

▲

提取數字

普通公式：

=LOOKUP(9E 307,--MID(A222,MIN(FIND({0;1;2;3;4;5;6;7;8;9},A222&1234567890)),ROW($1:$99)))

數組公式：=LOOKUP(9^99,--MID(A222,MATCH(,MID(A222,ROW($1:$99),1)*0,),ROW($1:$99)))，按<Ctrl Shift Enter>三鍵結束。

▲

按指定次數重複

公式：

=LOOKUP(,0/FREQUENCY(ROW(A1),SUBTOTAL(9,OFFSET(B$232,,,ROW($1:$9)))),A$232:A$236)&""

▲

計算合計金額

公式：

=SUM(LOOKUP(A247:A254,D247:E254)*B247:B254)

數組公式，按<Ctrl Shift Enter>三鍵結束。

注意：數組中的值必須按升序排列，即本題中D列品名升序排列。

▲

多條件查找

公式：

=LOOKUP(,0/(E262&F262=A$262:A$269&B$262:B$269),C$262:C$269)

或者公式：

=LOOKUP(,0/((A$262:A$269=E262)*(B$262:B$269=F262)),C$262:C$269)

通用公式：

LOOKUP(1,0/((條件1)*(條件2)*…*(條件n)),返回區域)

▲

一對多查找

公式：

=IFERROR(LOOKUP(,0/(D$275&ROW(A1)=A$275:A$283&COUNTIF(INDIRECT("A271:A"&ROW($275:$283)),D$275)),B$275:B$283),"")

數組公式，按<Ctrl Shift Enter>三鍵結束。

不知道何為二分法的就別說你會LOOKUP，要麼你就懂得使用套路！

光說不練假把式，動手操作才是硬道理！

函數篇：人見人愛的VLOOKUP，你真的會用他嗎？

作者：仰望~星空