Excel解析lookup的經典查找方式
第一,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 (查找字元,數據源區域)的形式代替。
推薦閱讀:
※一個老公懲罰老婆的方式 值得一看(5)
※5種調情方式迎合你的男人
※在外人面前,爸媽別再用這4種禮貌方式迎合自己,卻傷害孩子
※你的財富壽命由生活方式來決定?
※學習22種思維方式