EXCEL中l(wèi)ookup函數(shù)的經(jīng)典查找方式

-.z.主講老師：盧子老師講座主題：解析lookup函數(shù)的經(jīng)典查找方式學(xué)習(xí)是需要技巧和經(jīng)驗(yàn)的。感盧子老師為大家分享和交流他的實(shí)戰(zhàn)經(jīng)驗(yàn)。下面是本期講座的全部容。本期講座包括兩部分容：第一，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ù)是e*cel中最常用的兩個(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ù)值的名稱或引用。

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è)滿足條件的。以上的原理，被俗稱為“以大欺小法”。這種技巧在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)”查找“俗稱”。單擊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é)果?！皥D啥”網(wǎng)友問(wèn)：iserror與isna函數(shù)的區(qū)別。ISNA只屏蔽*N/A錯(cuò)誤，ISERROR屏蔽所有錯(cuò)誤。第三，LOOKUP函數(shù)多條件查找。如上圖所示，根據(jù)“俗稱”和“訂單號(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)類通配符。LOOKUP是不支持通配符的，但可以使用FIND(查找字符，數(shù)據(jù)源區(qū)域)的形式代替。本講座有關(guān)的E*cel源文件請(qǐng)?jiān)谡搲螺d：.blwbbs./forum.php"mod=redirect&goto=findpost&ptid=7&pid=24&fromuid=1講座主題：VLOOKUP函數(shù)運(yùn)用(下)在第3期的技術(shù)講座中，已經(jīng)學(xué)習(xí)了VLOOKUP函數(shù)的用法，也分析了一些基礎(chǔ)例子。無(wú)言老師在本期講座通過(guò)幾個(gè)實(shí)例來(lái)幫助大家更深入的了解VLOOKUP函數(shù)的使用。VLOOKUP函數(shù)運(yùn)用一：VLOOKUP函數(shù)第三個(gè)參數(shù)返回的列數(shù)可以通過(guò)match函數(shù)定位查找的返回所需的列數(shù)，vlookup和match嵌合使用。下圖所示的A11：H20單元格區(qū)域是e*cel源數(shù)據(jù)。=match(需要查找的數(shù)據(jù)表列標(biāo)題，元數(shù)據(jù)表列標(biāo)題圍，0)，這是精確查找定位第一次出現(xiàn)的列位置。請(qǐng)使用vlookup函數(shù)解出下圖所示的C24單元格中編號(hào)所對(duì)應(yīng)的其余單元格的值。單擊C24單元格右下角的向下三角形，可以更換選擇其余的編號(hào)。在E24單元格輸入公式：=VLOOKUP($C$24,$A$11:$H$20,MATCH(D24,$A$11:$H$11,0),0)即可得到答案。同樣的方法可以求出其余單元格的值，只是把定位的單元格地址更改一下就好了。VLOOKUP函數(shù)運(yùn)用二：原工作表存在通配符的查找。如下圖所示，原工作表A列中存在“*”通配符。通過(guò)上圖的源數(shù)據(jù)，要對(duì)下圖的C、D、E列對(duì)應(yīng)的數(shù)據(jù)進(jìn)行查找，該如何設(shè)計(jì)公式呢"單擊C231單元格，輸入以下公式：

=VLOOKUP(LEFT($B231,2)&"*",$A$219:$G$222,MATCH(C$230,$A$219:$G$219,0),0)，然后向右和向下拉即可查找相應(yīng)出相應(yīng)的數(shù)據(jù)。無(wú)言老師提到，還可以使用下面這樣的公式，也能實(shí)現(xiàn)。=VLOOKUP(MID($B239,1,2)&"""",$A$219:$G$222,MATCH(C$238,$A$219:$G$219,0),0)通過(guò)這個(gè)案例，可以看到*和"的替換作用，*號(hào)替換的可以為*個(gè)文本之前或之后的所有字符，"號(hào)代替的只是一個(gè)字符。通常來(lái)說(shuō)，通配符用的比較多的還是*號(hào)。VLOOKUP函數(shù)運(yùn)用三：反向查找。VLOOKUP函數(shù)通常只能從左往右的垂直方向有序查找。如果需要用到逆序，反向查找就需要使用IF或CHOOSE其中一個(gè)函數(shù)嵌套使用。這兩個(gè)函數(shù)在VLOOKUP函數(shù)的使用通常是這樣的形式：IF({1,0},查找容的列,返回容的列)和CHOOSE({1,2,3},查找容的列,返回容的列-1,返回容的列-2返回容的列-3),【返回容的列需要幾列就寫(xiě)入幾列】。下圖所示的是A258：F288單元格區(qū)域?yàn)樵磾?shù)據(jù)，為了演示需要，將其中的部分行區(qū)域隱藏了。如下圖所示，已知列數(shù)據(jù)，使用VLOOKUP函數(shù)查找年齡和工資列的數(shù)據(jù)。要完成此題，有兩種方法可以實(shí)現(xiàn)：第一，使用IF({1,0}嵌套VLOOKUP實(shí)現(xiàn)。

單擊年齡下方的I270單元格，輸入公式：

=VLOOKUP($H270,IF({1,0},$B$258:$B$288,$D$258:$D$288),2,0)，然后下拉。單擊工資下方的J270單元格，輸入公式：

=VLOOKUP($H270,IF({1,0},$B$258:$B$288,$F$258:$F$288),2,0)，然后下拉。提示：IF{1,0}函數(shù)在這里只能用到2個(gè)條件，因此使用有一定局限性。下面我們就使用另外一種方法來(lái)實(shí)現(xiàn)，即借助CHOOSE函數(shù)。其實(shí)IF函數(shù)可以做到的CHOOSE同樣能做到，而且CHOOSE比IF更靈活。第二，CHOOSE函數(shù)和VLOOKUP的嵌套使用的公式：同樣在年齡下方的I270單元格，輸入公式：=VLOOKUP($H288,CHOOSE({1,2,3},$B$259:$B$288,$D$259:$D$288,$F$259:$F$288),2,0)，然后下拉。單擊工資下方的J270單元格，輸入公式：=VLOOKUP($H288,CHOOSE({1,2,3},$B$259:$B$288,$D$259:$D$288,$F$259:$F$288),3,0)。如果大家對(duì)以上公式不是很理解，可以在公式欄中選中不明白的部分，按下F9鍵，俗稱“抹黑”，查看公式對(duì)應(yīng)的執(zhí)行結(jié)果。F9鍵在學(xué)習(xí)函數(shù)與公式中，對(duì)我們