【Excel函數教程】解析lookup的經典查找方式

 提示：點擊上方"excel教程"↑免費訂閱

　　學習是需要技巧和經驗的。感謝盧子老師為大家分享和交流他的實戰經驗。下面是本期講座的全部內容。

　　本期講座包括兩部分內容：第一，lookup函數用法介紹；第二，通過實例講解lookup函數經典的條件查找解法，通用公式基本可以寫為：LOOKUP(2,1/(條件),查找數組或區域)或LOOKUP(1,0/(條件),查找數組或區域)。

　　第一部分：lookup函數用法介紹

　　lookup函數和vlookup函數是excel中最常用的兩個查找函數。vlookup函數能做到的lookup函數同樣可以做到，而且可以做得更好。

　　LOOKUP函數有兩種語法形式：向量和數組。本期就向量形式的展開交流和探討。

　　向量形式的語法為：LOOKUP(lookup_value,lookup_vector,result_vector)

　　其中的參數意義如下：

　　Lookup_value：為所要查找的數值。Lookup_value 可以為數字、文本、邏輯值或包含數值的名稱或引用。
　　Lookup_vector：為只包含一行或一列的區域。Lookup_vector 的數值可以為文本、數字或邏輯值。Lookup_vector的數值必須按升序排序：...、-2、-1、0、1、2、...、A-Z、FALSE、TRUE,否則， LOOKUP不能返回正確的結果。文本不區分大小寫。
　　Result_vector:只包含一行或一列的區域，其大小必須與 lookup_vector 相同。
　　比如lookup(A1,B1:B10,C2:C11),其中C2:C11的尺寸要與B1:B10相同，且如果A1對應B列中的位置是B2的話，那麼返回的將是C3的值。

　　LOOKUP函數說明：

　　第一，如果函數 LOOKUP 找不到 lookup_value，則查找 lookup_vector 中小於或等於 lookup_value 的最大數值。這就是為何返回最後一個滿足條件的值的原理。
　　第二，如果 lookup_value 小於 lookup_vector 中的最小值，函數 LOOKUP 返回錯誤值 #N/A。
　　利用這個特性，我們可以用=LOOKUP(1,0/(條件),引用區域)這樣一個通用公式來作查找引用。

　　第二部分：lookup函數實例運用

　　運用一：模糊查找

　　模糊查找的核心是第二個參數排序必須是升序，否則會導致查找值錯誤。下圖所示的表1是按升序排序的，表2沒有排序。

　　分別在表1和表2下面對應的單元格輸入公式。

　　表1的數據源是按升序排序的，根據lookup函數用法：=LOOKUP(要查找的數據,查找範圍,結果)，在C24單元格設置公式：=LOOKUP(B24,$B$5:$B$17,$C$5:$C$17)，然後下拉得到正確結果。

　　表2的數據源是沒有排序的，在J24單元格輸入公式：=LOOKUP(I24,$I$5:$I$17,$J$5:$J$17) ，然後下拉，發現J25單元格得到的結果是H126，顯然不對。通過表2的源數據可以看到I25單元格對應的值應該為J8單元格的值H142。

　　為什麼會出錯呢?這就印證了第一部分的用法介紹中所講到的：Lookup_vector的數值必須按升序排序：...、-2、-1、0、1、2、...、A-Z、FALSE、TRUE,否則， LOOKUP不能返回正確的結果。文本不區分大小寫。

　　模糊查找，數據源一定要以升序先進行排序，否則就會出錯。在數據源沒有排序的情況下，如何才能查找到正確結果?LOOKUP函數有一個經典的條件查找解法，可以很好的解決此問題。

　　在第一部分有提到，通用公式基本可以寫為：LOOKUP(2,1/(條件),查找數組或區域) 或LOOKUP(1,0/(條件),查找數組或區域)。

　　公式中的2、1、0等數字的含義是什麼? 首先，條件是一組邏輯判斷的值或邏輯運算得到的由TRUE和FALSE組成或者0與非0組成的數組，因而：0/(條件)的作用是用於構建一個由0或者#DIV!0錯誤組成的值。比如數據源中能查找到對應值就是ture，沒有就是false。形式如：0/True=0，0/false=#DIV0!，查找到就0，沒有就是錯誤值。

　　如果 LOOKUP 函數找不到 lookup_value (即：1)，則它與 lookup_vector 中小於或等於 lookup_value 的最大值(即：0)匹配。

　　也就是說，要在一個由0和#DIV!0組成的數組中查找1，肯定找不到1，因而將返回小於或等於1的最大值(也就是0)匹配。用大於0的數來查找0，肯定能查到最後一個滿足條件的。

　　以上的原理，被俗稱為「以大欺小法」。這種技巧在LOOKUP函數上的運用是很常見的。

　　利用上面的原理，不管有沒有排序，只要使用上面的「以大欺小法」都能得到正確結果。比如上面實例中，在J25單元格輸入公式：=LOOKUP(1,0/(I25=$I$5:$I$17),$J$5:$J$17)，就可以了。

　　運用二：精確查找

　　第一，查找的數據沒有對應值，可以利用ISNA(ISERROR)函數屏蔽錯誤值。

　　如上圖所示，表3是數據源，在下面左邊根據「番號」查找「俗稱」。單擊C51單元格，輸入公式=LOOKUP(1,0/(B51=$B$42:$B$45),$C$42:$C$45)，然後下拉可以看到下面的C52和C54單元格出現錯誤值。這種情況可以利用ISNA(ISERROR)函數屏蔽錯誤值。

　　只要在公式外面嵌套個if(isna(lookup(),"",lookup())，這樣的形式就可以把錯誤值屏蔽。在H51單元格，輸入這樣的公式：=IF(ISNA(LOOKUP(1,0/(G51=$B$42:$B$45),$C$42:$C$45)),"",LOOKUP(1,0/(G51=$B$42:$B$45),$C$42:$C$45))，下拉，就可以屏蔽錯誤值了。將錯誤值屏蔽了，表格就好看多了。

　　上面公式中，""，是顯示空的意思，錯誤就顯示空，沒有就查找。

　　第二，藉助錯誤值來判定產品是否存在。

　　下圖所示根據左邊的數據源，來判定右邊對應的數據是否在番號列中。

　　只需要嵌套一個isna函數就可以做到，如果沒有存在就錯誤，有存在就.這樣的形式。在H62單元格輸入公式：=IF(ISNA(LOOKUP(1,0/(G62=$B$62:$B$74))),"否","是")，下拉就即可得出結果。

　　「圖啥」網友問：iserror與isna函數的區別。ISNA只屏蔽#N/A錯誤，ISERROR屏蔽所有錯誤。

　　第三，LOOKUP函數多條件查找。

　　如上圖所示，根據「俗稱」和「訂單號」來查找「訂單數」和「尾數」，可以套用這樣的公式：=LOOKUP(1,0/(條件(1)*(2)*(3).。。。。。),引用區域)，用*或&將各個條件連接起來，*就是和的意思。

　　此題有兩種方法：
　　第一，在K112單元格輸入公式：=LOOKUP(1,0/(($I112=$B$112:$B$120)*($J112=$C$112:$C$120)),D$112:D$120)，複製公式就可以得到結果。
　　第二，另外也可以使用這個公式：=LOOKUP(1,0/($I112&$J112=$B$112:$B$120&$C$112:$C$120),D$112:D$120)

　　第四，含某個字符查找。

　　按照上圖所示，根據左邊的數據源，來對含有某個字符進行查找。單擊G128單元格，輸入公式：=LOOKUP(1,0/(FIND($F128,$B$128:$B$131)),B$128:B$131)，就可以得到結果。

　　VLOOKUP函數與lookup函數對比：

　　第一，在多條件查找方面，就能看出lookup函數好用。用vlookup多條件查找，最簡單的方法就是借用輔助列。
　　第二， VLOOKUP函數對於反向查找是需要嵌套其餘函數才能實現，而LOOKUP函數沒有正反之分，因此在這方面LOOKUP函數會更加容易實現。
　　第三， vlookup在查找字符方面，可以使用*號類通配符。LOOKUP是不支持通配符的，但可以使用FIND (查找字符，數據源區域)的形式代替。

excel急速貫通班

越努力越幸運，部落窩與你同行

學習QQ群：133240130