|
都說VLOOKUP是什么大眾情人、使用頻率最高的，哼，有了我LOOKUP，他VLOOKUP可以退隱山林了，去慢慢感嘆：既生我VLOOKUP，何生他LOOKUP！ 函數(shù)語法解析 函數(shù)LOOKUP有兩種語法形式：向量形式和數(shù)組形式 向量形式 1、函數(shù)定義： 在單行區(qū)域或單列區(qū)域（稱為“向量”）中查找值，然后返回第二個單行區(qū)域或單列區(qū)域中相同位置的值。 2、語法格式： LOOKUP(lookup_value,lookup_vector,result_vector) LOOKUP（查找值，查找區(qū)域，結(jié)果區(qū)域） 3、參數(shù)說明： ①、lookup_value：必需。LOOKUP在第一個向量中搜索的值。 Lookup_value可以是數(shù)字、文本、邏輯值、名稱或?qū)χ档囊?/span>。 ②、lookup_vector：必需。 只包含一行或一列的區(qū)域。 lookup_vector中的值可以是文本、數(shù)字或邏輯值。 ③、result_vector：可選。只包含一行或一列的區(qū)域。 result_vector參數(shù)必須與lookup_vector參數(shù)大小相同。其大小必須相同。 4、注意事項： ①、如果LOOKUP函數(shù)找不到lookup_value，則該函數(shù)會與lookup_vector中小于或等于lookup_value的最大值進(jìn)行匹配。 ②、如果lookup_value小于lookup_vector中的最小值，則LOOKUP會返回#N/A錯誤值。 ③、重要:lookup_vector中的值必須按升序排列：...,-2,-1,0,1,2,...,A-Z,FALSE,TRUE；否則，LOOKUP可能無法返回正確的值。文本不區(qū)分大小寫。 數(shù)組形式 1、函數(shù)定義： 在數(shù)組的第一行或第一列中查找指定的值，并返回數(shù)組最后一行或最后一列中同一位置的值。 當(dāng)要匹配的值位于數(shù)組的第一行或第一列中時，請使用LOOKUP的這種形式。 2、語法格式： LOOKUP(lookup_value,array) 3、參數(shù)說明： ①、lookup_value：必需。LOOKUP在數(shù)組中搜索的值。 lookup_value參數(shù)可以是數(shù)字、文本、邏輯值、名稱或?qū)χ档囊?/span>。 ②、array：必需。包含要與lookup_value進(jìn)行比較的文本、數(shù)字或邏輯值的單元格區(qū)域。 4、注意事項： ①、如果LOOKUP找不到lookup_value的值，它會使用數(shù)組中小于或等于lookup_value的最大值。 ②、如果lookup_value的值小于第一行或第一列中的最小值（取決于數(shù)組維度），LOOKUP會返回#N/A錯誤值。 ③、LOOKUP的數(shù)組形式與HLOOKUP和VLOOKUP函數(shù)非常相似。區(qū)別在于：HLOOKUP在第一行中搜索lookup_value的值，VLOOKUP在第一列中搜索，而LOOKUP根據(jù)數(shù)組維度進(jìn)行搜索。 ④、如果數(shù)組包含寬度比高度大的區(qū)域（列數(shù)多于行數(shù)）LOOKUP會在第一行中搜索lookup_value的值。 ⑤、如果數(shù)組是正方的或者高度大于寬度（行數(shù)多于列數(shù)），LOOKUP會在第一列中進(jìn)行搜索。 ⑥、使用HLOOKUP和VLOOKUP函數(shù)，您可以通過索引以向下或遍歷的方式搜索，但是LOOKUP始終選擇行或列中的最后一個值。 ⑦、重要：數(shù)組中的值必須按升序排列：...,-2,-1,0,1,2, ...,A-Z,FALSE,TRUE；否則，LOOKUP可能無法返回正確的值。文本不區(qū)分大小寫。 經(jīng)典套路（通用公式）： 1、LOOKUP(1,0/((條件1)*(條件2)*…*(條件n)),返回區(qū)域) 2、LOOKUP(2,1/((條件1)*(條件2)*…*(條件n)),返回區(qū)域) 函數(shù)示例 ▲ 01 經(jīng)典用法
公式： =LOOKUP(F3,A3:A8,D3:D8) 注意：lookup_vector中的值必須按升序排列。
公式： =LOOKUP(F3,A3:D8) 注意：數(shù)組中的值必須按升序排列。 當(dāng)要匹配的值位于數(shù)組的第一行或第一列中時，可以使用數(shù)組形式。
公式： =LOOKUP(1,0/(N3=I3:I8),L3:L8) 注意：亂序情況也可以使用。 ▲ 02 區(qū)間查找
公式： =LOOKUP(B14,E$14:E$17,F$14:F$17) 注意：lookup_vector中的值必須按升序排列。
公式： =LOOKUP(B14,E$14:F$17) 注意：數(shù)組中的值必須按升序排列。 當(dāng)要匹配的值位于數(shù)組的第一行或第一列中時，可以使用數(shù)組形式。 ▲ 03 格式不一致的查找 分兩種情況： 第一種：查找值文本型，查找區(qū)域數(shù)值型
公式： =LOOKUP(D25*1,A25:A30,B25:B30) 解析：查找值和查找區(qū)域格式不一致將出現(xiàn)錯誤值#N/A 需將查找值轉(zhuǎn)換為和查找區(qū)域一樣的格式 轉(zhuǎn)換的方式很多種，比如： 0，-0，--，*1，/1，^1......等等。 注意：lookup_vector中的值必須按升序排列。
公式： =LOOKUP(D25*1,A25:B30) 注意：數(shù)組中的值必須按升序排列。 當(dāng)要匹配的值位于數(shù)組的第一行或第一列中時，可以使用數(shù)組形式。
公式： =LOOKUP(2,1/(G25:G30=J25*1),H25:H30) 注意：亂序情況也可以使用。 第二種：查找值數(shù)值型，查找區(qū)域文本型
公式： =LOOKUP(D36&'',A36:A41,B36:B41) 解析：查找值和查找區(qū)域格式不一致將出現(xiàn)錯誤值#N/A 查找值數(shù)值型，查找區(qū)域文本型，將查找值連接個空（&''）變?yōu)槲谋?，格式統(tǒng)一后就能查找出正確結(jié)果了。 注意：lookup_vector中的值必須按升序排列。
公式： =LOOKUP(D36&'',A36:B41) 注意：數(shù)組中的值必須按升序排列。 當(dāng)要匹配的值位于數(shù)組的第一行或第一列中時，可以使用數(shù)組形式。
公式： =LOOKUP(1,0/(G36:G41=J36&''),H36:H41) 注意：亂序情況也可以使用。 ▲ 04 查找最后一個文本
公式： =LOOKUP('座',A47:A58) 解析：用“座”或“々”等較大的漢字查找區(qū)域中最后一個文本。 ▲ 05 查找最后一個數(shù)字
公式： =LOOKUP(9E 307,A64:A75) 解析：9E 307是科學(xué)記數(shù)，表示9*10^307，是Excel允許鍵入的最大數(shù)值，可用來返回最后一個數(shù)值。 ▲ 06 查找最后一次采購單價
公式： =LOOKUP(1,0/(B81:B92=E81),C81:C92) 通用公式：LOOKUP(1,0/(條件1),返回區(qū)域) 解析： (B81:B92=E81)條件成立返回TRUE，條件不成立返回FALSE； 發(fā)生四則運(yùn)算時TRUE相當(dāng)于1，F(xiàn)ALSE相當(dāng)于0； 利用0/任何數(shù)=0、0/0=#DIV/0!的特性，0/(B81:B92=E81)部分構(gòu)成了一個由0和#DIV/0!組成的數(shù)組； 用大于第二個參數(shù)所有數(shù)值的1作為查找值，即可查找出最后一次采購單價。 ▲ 07 通配符查找
公式： =IFNA(LOOKUP(,0/FIND(D98,A$98:A$102),B$98:B$102),'') 注意：LOOKUP函數(shù)不支持通配符使用，可以用LOOKUP FIND組合 函數(shù)IFNA起容錯作用 有關(guān)函數(shù)IFNA的用法，請點擊下面鏈接： ▲ 08 取消合并單元格并填充 第一種：全部為文本
公式： =LOOKUP('々',A$109:A109) 解析：可以用“々”或者“座”，不會打“々”的可以按快捷鍵<Alt 41385> 第二種：全部為數(shù)字
公式： =LOOKUP(9E 307,F$109:F109) 第三種：文本數(shù)字混合
公式： =LOOKUP(1,0/($I$109:I109<>''),$I$109:I109) ▲ 09 查找返回多列數(shù)據(jù)
公式： =LOOKUP(,0/($F131=$A131:$A136),B131:B136)，向右填充 ▲ 10 反向查找
公式： =LOOKUP(,0/($F142=$D142:$D147),A142:A147)，向右填充 ▲ 11 交叉查詢
公式： =LOOKUP(,0/(A153:A158=F153),OFFSET(A153:A158,,MATCH(G153,B152:D152,0))) 解析：MATCH(G153,B152:D152,0)部分找到5月在區(qū)域B152:D152中的位置為2； OFFSET部分是以區(qū)域A153:A158為基點，偏移0行2列，返回新區(qū)域C153:C158的引用； 那么公式就是LOOKUP(,0/(A153:A158=F153),C153:C158) 不這樣寫是為了實現(xiàn)動態(tài)查詢，當(dāng)業(yè)務(wù)員或月份更改時，其對應(yīng)數(shù)據(jù)區(qū)域自動改變，實現(xiàn)自動化查詢。 ▲ 12 合并單元格的查詢問題
公式： =LOOKUP('座',OFFSET(A164,,,MATCH(G164,B164:B180,))) 解析：MATCH(G164,B164:B180,)部分找到客服“君柳”在區(qū)域B164:B180中的位置為8； OFFSET（基點，偏移行數(shù)，偏移列數(shù)，行高，列寬） OFFSET(A164,,,MATCH(G164,B164:B180,))是以A164單元格為基點，偏移0行0列，返回行高為8的新區(qū)域A164:A171的引用。 抹黑按F9得到：
用“座”或“々”等較大的漢字查找區(qū)域中最后一個文本。 ▲ 13 合并單元格的查詢問題
公式： =LOOKUP(,0/(LOOKUP('座',A$186:A186)=G$186:G$192),H$186:H$192) ▲ 14 提取不重復(fù)項
公式： =LOOKUP(,0/FREQUENCY(0,ISNA(MATCH(A$208:A$216,E$207:E207,))-1),A$208:A208)&'' ▲ 15 提取數(shù)字
普通公式： =LOOKUP(9E 307,--MID(A222,MIN(FIND({0;1;2;3;4;5;6;7;8;9},A222&1234567890)),ROW($1:$99)))
數(shù)組公式：=LOOKUP(9^99,--MID(A222,MATCH(,MID(A222,ROW($1:$99),1)*0,),ROW($1:$99)))，按<Ctrl Shift Enter>三鍵結(jié)束。 ▲ 16 按指定次數(shù)重復(fù)
公式： =LOOKUP(,0/FREQUENCY(ROW(A1),SUBTOTAL(9,OFFSET(B$232,,,ROW($1:$9)))),A$232:A$236)&'' ▲ 17 計算合計金額
公式： =SUM(LOOKUP(A247:A254,D247:E254)*B247:B254) 數(shù)組公式，按<Ctrl Shift Enter>三鍵結(jié)束。 注意：數(shù)組中的值必須按升序排列，即本題中D列品名升序排列。 ▲ 18 多條件查找
公式： =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)),返回區(qū)域) ▲ 19 一對多查找
公式： =IFERROR(LOOKUP(,0/(D$275&ROW(A1)=A$275:A$283&COUNTIF(INDIRECT('A271:A'&ROW($275:$283)),D$275)),B$275:B$283),'') 數(shù)組公式，按<Ctrl Shift Enter>三鍵結(jié)束。 不知道何為二分法的就別說你會LOOKUP，要么你就懂得使用套路！ 光說不練假把式，動手操作才是硬道理！ 函數(shù)篇：人見人愛的VLOOKUP，你真的會用他嗎？ 作者：仰望~星空 |
|
|
