本期Excel教程講座包括兩部分內(nèi)容：第一，lookup函數(shù)用法介紹；第二，通過(guò)實(shí)例講解lookup函數(shù)經(jīng)典的條件查找解法，通用公式基本可以寫(xiě)為：LOOKUP(2,1/(條件),查找數(shù)組或區(qū)域)或LOOKUP(1,0/(條件),查找數(shù)組或區(qū)域)。
第一部分：lookup函數(shù)用法介紹
lookup函數(shù)和vlookup函數(shù)是excel中最常用的兩個(gè)查找函數(shù)。vlookup函數(shù)能做到的lookup函數(shù)同樣可以做到，而且可以做得更好。
LOOKUP函數(shù)有兩種語(yǔ)法形式：向量和數(shù)組。本期就向量形式的展開(kāi)交流和探討。
向量形式的語(yǔ)法為：LOOKUP(lookup_value,lookup_vector,result_vector)
其中的參數(shù)意義如下：
Lookup_value：為所要查找的數(shù)值。Lookup_value 可以為數(shù)字、文本、邏輯值或包含數(shù)值的名稱(chēng)或引用。
Lookup_vector：為只包含一行或一列的區(qū)域。Lookup_vector 的數(shù)值可以為文本、數(shù)字或邏輯值。Lookup_vector的數(shù)值必須按升序排序：...、-2、-1、0、1、2、...、A-Z、FALSE、TRUE,否則， LOOKUP不能返回正確的結(jié)果。文本不區(qū)分大小寫(xiě)。
Result_vector:只包含一行或一列的區(qū)域，其大小必須與 lookup_vector 相同。
比如lookup(A1,B1:B10,C2:C11),其中C2:C11的尺寸要與B1:B10相同，且如果A1對(duì)應(yīng)B列中的位置是B2的話，那么返回的將是C3的值。
LOOKUP函數(shù)說(shuō)明：
第一，如果函數(shù) LOOKUP 找不到 lookup_value，則查找 lookup_vector 中小于或等于 lookup_value 的最大數(shù)值。這就是為何返回最后一個(gè)滿足條件的值的原理。
第二，如果 lookup_value 小于 lookup_vector 中的最小值，函數(shù) LOOKUP 返回錯(cuò)誤值 #N/A。
利用這個(gè)特性，我們可以用=LOOKUP(1,0/(條件),引用區(qū)域)這樣一個(gè)通用公式來(lái)作查找引用。
第二部分：lookup函數(shù)實(shí)例運(yùn)用
運(yùn)用一：模糊查找
模糊查找的核心是第二個(gè)參數(shù)排序必須是升序，否則會(huì)導(dǎo)致查找值錯(cuò)誤。下圖所示的表1是按升序排序的，表2沒(méi)有排序。
分別在表1和表2下面對(duì)應(yīng)的單元格輸入公式。
表1的數(shù)據(jù)源是按升序排序的，根據(jù)lookup函數(shù)用法：=LOOKUP(要查找的數(shù)據(jù),查找范圍,結(jié)果)，在C24單元格設(shè)置公式：=LOOKUP(B24,$B$5:$B$17,$C$5:$C$17)，然后下拉得到正確結(jié)果。
表2的數(shù)據(jù)源是沒(méi)有排序的，在J24單元格輸入公式：=LOOKUP(I24,$I$5:$I$17,$J$5:$J$17) ，然后下拉，發(fā)現(xiàn)J25單元格得到的結(jié)果是H126，顯然不對(duì)。通過(guò)表2的源數(shù)據(jù)可以看到I25單元格對(duì)應(yīng)的值應(yīng)該為J8單元格的值H142。
為什么會(huì)出錯(cuò)呢?這就印證了第一部分的用法介紹中所講到的：Lookup_vector的數(shù)值必須按升序排序：...、-2、-1、0、1、2、...、A-Z、FALSE、TRUE,否則， LOOKUP不能返回正確的結(jié)果。文本不區(qū)分大小寫(xiě)。
模糊查找，數(shù)據(jù)源一定要以升序先進(jìn)行排序，否則就會(huì)出錯(cuò)。在數(shù)據(jù)源沒(méi)有排序的情況下，如何才能查找到正確結(jié)果?LOOKUP函數(shù)有一個(gè)經(jīng)典的條件查找解法，可以很好的解決此問(wèn)題。
在第一部分有提到，通用公式基本可以寫(xiě)為：LOOKUP(2,1/(條件),查找數(shù)組或區(qū)域) 或LOOKUP(1,0/(條件),查找數(shù)組或區(qū)域)。
公式中的2、1、0等數(shù)字的含義是什么? 首先，條件是一組邏輯判斷的值或邏輯運(yùn)算得到的由TRUE和FALSE組成或者0與非0組成的數(shù)組，因而：0/(條件)的作用是用于構(gòu)建一個(gè)由0或者#DIV!0錯(cuò)誤組成的值。比如數(shù)據(jù)源中能查找到對(duì)應(yīng)值就是ture，沒(méi)有就是false。形式如：0/True=0，0/false=#DIV0!，查找到就0，沒(méi)有就是錯(cuò)誤值。
如果 LOOKUP 函數(shù)找不到 lookup_value (即：1)，則它與 lookup_vector 中小于或等于 lookup_value 的最大值(即：0)匹配。
也就是說(shuō)，要在一個(gè)由0和#DIV!0組成的數(shù)組中查找1，肯定找不到1，因而將返回小于或等于1的最大值(也就是0)匹配。用大于0的數(shù)來(lái)查找0，肯定能查到最后一個(gè)滿足條件的。
以上的原理，被俗稱(chēng)為“以大欺小法”。這種技巧在LOOKUP函數(shù)上的運(yùn)用是很常見(jiàn)的。
利用上面的原理，不管有沒(méi)有排序，只要使用上面的“以大欺小法”都能得到正確結(jié)果。比如上面實(shí)例中，在J25單元格輸入公式：=LOOKUP(1,0/(I25=$I$5:$I$17),$J$5:$J$17)，就可以了。
運(yùn)用二：精確查找
第一，查找的數(shù)據(jù)沒(méi)有對(duì)應(yīng)值，可以利用ISNA(ISERROR)函數(shù)屏蔽錯(cuò)誤值。
如上圖所示，表3是數(shù)據(jù)源，在下面左邊根據(jù)“番號(hào)”查找“俗稱(chēng)”。單擊C51單元格，輸入公式=LOOKUP(1,0/(B51=$B$42:$B$45),$C$42:$C$45)，然后下拉可以看到下面的C52和C54單元格出現(xiàn)錯(cuò)誤值。這種情況可以利用ISNA(ISERROR)函數(shù)屏蔽錯(cuò)誤值。
只要在公式外面嵌套個(gè)if(isna(lookup(),"",lookup())，這樣的形式就可以把錯(cuò)誤值屏蔽。在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))，下拉，就可以屏蔽錯(cuò)誤值了。將錯(cuò)誤值屏蔽了，表格就好看多了。
上面公式中，""，是顯示空的意思，錯(cuò)誤就顯示空，沒(méi)有就查找。
第二，借助錯(cuò)誤值來(lái)判定產(chǎn)品是否存在。
下圖所示根據(jù)左邊的數(shù)據(jù)源，來(lái)判定右邊對(duì)應(yīng)的數(shù)據(jù)是否在番號(hào)列中。
只需要嵌套一個(gè)isna函數(shù)就可以做到，如果沒(méi)有存在就錯(cuò)誤，有存在就......這樣的形式。在H62單元格輸入公式：=IF(ISNA(LOOKUP(1,0/(G62=$B$62:$B$74))),"否","是")，下拉就即可得出結(jié)果。
“圖啥”網(wǎng)友問(wèn)：iserror與isna函數(shù)的區(qū)別。ISNA只屏蔽#N/A錯(cuò)誤，ISERROR屏蔽所有錯(cuò)誤。
第三，LOOKUP函數(shù)多條件查找。
如上圖所示，根據(jù)“俗稱(chēng)”和“訂單號(hào)”來(lái)查找“訂單數(shù)”和“尾數(shù)”，可以套用這樣的公式：=LOOKUP(1,0/(條件(1)*(2)*(3).。。。。。),引用區(qū)域)，用*或&將各個(gè)條件連接起來(lái)，*就是和的意思。
此題有兩種方法：
第一，在K112單元格輸入公式：=LOOKUP(1,0/(($I112=$B$112:$B$120)*($J112=$C$112:$C$120)),D$112:D$120)，復(fù)制公式就可以得到結(jié)果。
第二，另外也可以使用這個(gè)公式：=LOOKUP(1,0/($I112&$J112=$B$112:$B$120&$C$112:$C$120),D$112:D$120)
第四，含某個(gè)字符查找。
按照上圖所示，根據(jù)左邊的數(shù)據(jù)源，來(lái)對(duì)含有某個(gè)字符進(jìn)行查找。單擊G128單元格，輸入公式：=LOOKUP(1,0/(FIND($F128,$B$128:$B$131)),B$128:B$131)，就可以得到結(jié)果。
VLOOKUP函數(shù)與lookup函數(shù)對(duì)比：
第一，在多條件查找方面，就能看出lookup函數(shù)好用。用vlookup多條件查找，最簡(jiǎn)單的方法就是借用輔助列。
第二， VLOOKUP函數(shù)對(duì)于反向查找是需要嵌套其余函數(shù)才能實(shí)現(xiàn)，而LOOKUP函數(shù)沒(méi)有正反之分，因此在這方面LOOKUP函數(shù)會(huì)更加容易實(shí)現(xiàn)。
第三， vlookup在查找字符方面，可以使用*號(hào)類(lèi)通配符。LOOKUP是不支持通配符的，但可以使用FIND (查找字符，數(shù)據(jù)源區(qū)域)的形式代替。
聯(lián)系客服
