編按：

哈嘍，大家好！經(jīng)過4天緊張激烈的角逐，VLOOKUP與LOOKUP終于迎來了最后的較量——缺失查詢和多條件查詢！勝負(fù)在此一舉，查詢雙雄，究竟誰才是最后的王者呢？

作為《VLOOKUP&LOOKUP雙雄戰(zhàn)》系列文章的收官之戰(zhàn)，LOOKUP(1,0/(條件)...結(jié)構(gòu)將縱橫查詢沙場，所向披靡；而作為對手，VLOOKUP將如何應(yīng)對？閑話少說，直入正文！

ROUND 07 缺失查詢

在本系列開篇時，VLOOKUP和LOOKUP就在如何通過人物簡稱查詢?nèi)Q的模糊包含查詢問題上有過一番較量，彼時，VLOOKUP略勝一籌。本回合，它們將面對包含查詢的逆命題——缺失查詢。下圖中，我們已知人物全名是特拉法爾加·羅，要查詢他所擁有的果實名稱。這本來是一個很簡單的問題，但是出于某種原因，果實對照表中的姓名并不是全稱，而是簡稱，OH MY GOD，不會真尷尬?。?！

圖7.1：缺失查詢——全稱查簡稱

LOOKUP函數(shù)：信手拈來，花樣百出

這類問題對LOOKUP非常簡單，特別是在我們講解了1/0結(jié)構(gòu)以后。

=LOOKUP(1,0/FIND(A2:A9,D3),B2:B9)

圖7.2：缺失查詢——LOOKUP信手拈來

公式說明

FIND(A2:A9,D3)用于判斷A2:A9中的每一個簡稱是否被D3全稱所包含。若包含，則返回簡稱在全稱中出現(xiàn)的位置，否則公式返回錯誤值#VALUE!。于是0/FIND(A2:A9,D3)即返回一組由數(shù)字0和錯誤值組成的有序數(shù)組，數(shù)字0在數(shù)組中的位置即查詢返回值所在行。接著LOOKUP函數(shù)開始發(fā)揮作用，忽略錯誤值，返回小于且最接近于目標(biāo)值1的查詢區(qū)域值所對應(yīng)的值，即最后一個0值所對應(yīng)的B6的值。詳細(xì)解讀如下:

彩蛋1：你發(fā)現(xiàn)了嗎？如果LOOKUP的查詢值足夠大(實際上，大于D3的字符數(shù)即可)，那么我們大可把FIND函數(shù)前“0/”去掉，使用=LOOKUP(100,FIND(A2:A9,D3),B2:B9)這樣的公式也可以完成模糊包含查找。

圖7.3：缺失查詢——"0/"可去除

彩蛋2：你發(fā)現(xiàn)了嗎？彩蛋1的公式竟然和本系列第一篇中的LOOKUP模糊包含查詢公式如出一轍，差別僅在于FIND函數(shù)的兩個參數(shù)互換位置。沒錯，你沒看錯，這就是神奇的LOOKUP函數(shù)，高手的摯愛。

圖7.4：缺失查詢——本系列第一篇LOOKUP模糊包含查詢

彩蛋3：你發(fā)現(xiàn)了嗎？LOOKUP結(jié)構(gòu)中“0/”的作用其實是將任何結(jié)果都轉(zhuǎn)化為0和錯誤值，該結(jié)構(gòu)的核心技術(shù)是LOOKUP忽略錯誤值的特性、二分法(默認(rèn)升序并返回最大的小于等于目標(biāo)值的值)和自帶數(shù)組運算。所以，有時我們也可以用LOOKUP(2,1/(條件)...或LOOKUP(0.1,0/(條件)...等等結(jié)構(gòu)來替代，只需確保第一個參數(shù)總比第二個參數(shù)返回的數(shù)組中的最大值大即可！

圖7.5：缺失查詢——1/0結(jié)構(gòu)的變形

VLOOKUP函數(shù)：最后的倔強

在模糊缺失查詢問題上，VLOOKUP本該繳械投降的。但作為大眾情人的VLOOKUP一身傲骨，死也要站著死！

=VLOOKUP(D3,IF({1,0},IFERROR(VLOOKUP(T(IF({1},"*"&A2:A9&"*")),D3,1,0),1),B2:B9),2,0)

圖7.6：缺失查詢——VLOOKUP的倔強

公式說明

一個強行使用兩個VLOOKUP的嵌套函數(shù)，其大致意思是將A2:A9中被D3包含的文本用D3替換，其余保持不變；然后將替換后的A2:A9和不做改變的B2:B9組成一個新的查詢區(qū)域；最后再用VLOOKUP的基本套路完成查詢。

問：具體運算過程如何理解？

答：不用理解，如遇此類問題，請用LOOKUP！

問：為什么要用兩次VLOOKUP，而不使用其他函數(shù)替代？

答：丟分不丟人，這是VLOOKUP最后的倔強！

ROUND 08 多條件查詢

在多數(shù)Excel實操問題上，往往需要多個條件才能準(zhǔn)確指向目標(biāo)，于是有了多條件求和函數(shù)SUMIFS，有了多條件計數(shù)函數(shù)COUNTIFS。但是沒有多條件查詢函數(shù)VLOOKUPS和LOOKUPS，這是為什么呢？因為這兩個函數(shù)本身就能實現(xiàn)多條件查詢！??！如下例，我們需要通過職位和性別來找到名單中唯一的女性船長并返回她的明細(xì)，該怎么做？

圖8.1：多條件查詢——哪來的女船長？

VLOOKUP：合縱連橫，無往不利

在逆向查詢中，我們學(xué)習(xí)了如何利用IF{1,0}結(jié)構(gòu)構(gòu)建列序交換的虛擬數(shù)組來完成逆向查詢，在這里使用連接符&，我們用它來配合VLOOKUP函數(shù)完成多條件查詢。

{=VLOOKUP(E3&F3,IF({1,0},C2:C8&B2:B8,A2:A8),2,0)}

圖8.2：多條件查詢——VLOOKUP的連接符

公式說明

首先用連接符"&"將E3和F3組合起來，形成新的查詢值，即“船長女”；接下來要做的就是構(gòu)建一個以職位和性別組成的首列并與姓名列組成新的查詢范圍。我們還是用&來完成，將C2:C8和B2:B8連接起來，形成新的查詢列“職位+性別”列，即{"船長男";"航海士女";"船長男";"劍士男";"船長女";"考古學(xué)家女";"船長男"}，然后使用IF{1,0}結(jié)構(gòu)將新的查詢列與姓名列A2:A8組成新的查詢范圍，最后VLOOKUP發(fā)揮功能，完成查詢。此時如果你只是單純地用Enter來完成計算，可能就要吃癟了，因為這是一個數(shù)組運算，需按Ctrl+Shift+Enter才能完成計算，小伙伴們慎之慎之！?。?/span>

LOOKUP：1/0結(jié)構(gòu)真正的戰(zhàn)場

在上一篇中，小花花了很大篇幅來講解LOOKUP(1,0/(條件)......結(jié)構(gòu)，當(dāng)然不只是為了完成逆向查詢這種簡單問題！多條件查詢，才是LOOKUP(1,0/(條件)......結(jié)構(gòu)真正的戰(zhàn)場！

=LOOKUP(1,0/((E3=C2:C8)*(F3=B2:B8)),A2:A8)

圖8.3：多條件查詢——LOOKUP的1/0結(jié)構(gòu)

公式說明

該公式使用了LOOKUP(1,0/(條件)......結(jié)構(gòu)的復(fù)雜版——LOOKUP(1,0/((條件1)*(條件2)...*(條件n)),結(jié)果列)。它將LOOKUP(1,0/(條件)......結(jié)構(gòu)中的單一條件升級為多條件相乘的形式(條件1)*(條件2)...*(條件n)，利用乘法運算中TRUE=1、FALSE=0的原理，使得只有當(dāng)所有條件都滿足而返回TRUE時，TRUE*TRUE=1*1=1，此時0/1等于0；但凡任何一個條件不滿足返回FALSE， 0乘以任何數(shù)都等于0，此時0/0返回錯誤值#DIV/0!。于是LOOKUP函數(shù)的第二參數(shù)查詢區(qū)域成為一組由0和#DIV/0!組成的有序數(shù)組{#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;0;#DIV/0!;#DIV/0!}。如前述，LOOKUP可以忽略錯誤值并返回最后一個小于或等于目標(biāo)值的二分位值所對應(yīng)的結(jié)果，于是最后一個0所對應(yīng)的值A6“邦尼”就是公式的返回值。

于是有的小伙伴就要納悶了，既然VLOOKUP可以用連接符"&"連接多個條件，LOOKUP可以用乘號"*"串聯(lián)多個條件，它們都能完成多條件查詢，那是不是這一回合雙方打平??？當(dāng)然不是，在多條件查詢領(lǐng)域，LOOKUP具有絕對的優(yōu)勢，它不僅能完成和VLOOKUP一樣的精確性比對匹配，還能完成區(qū)間條件的查詢。簡單的說，VLOOKUP連接法的多條件查詢只能判別查找值和查詢區(qū)域中的值是否相等，而LOOKUP的0/1結(jié)構(gòu)還能完成大于或小于這類非精確查找的條件判別。

=LOOKUP(1,0/((E3=B2:B7)*(C2:C7<10000)),A2:A7)

圖8.4：多條件查詢——VLOOKUP不知道的LOOKUP

公式說明

該公式與上一公式的區(qū)別在于，第二個條件的邏輯判斷符號不再是等號，而是小于號"<"，這是VLOOKUP用連接符所無法完成的復(fù)雜工作。這就是為什么有人說，如果要評價函數(shù)圈最大的發(fā)現(xiàn)，那LOOKUP的兩分法絕對能獨占鰲頭！誠然！

結(jié)束語

本文，小花介紹了包含查詢和多條件查詢這兩個難度較高的應(yīng)用情境，至于多值查詢、批量查詢等諸多查詢高難度用法，還有待小伙伴們進(jìn)一步去挖掘、去深究！希望經(jīng)過本文的學(xué)習(xí)，小伙伴們能夠更加深刻地了解、使用VLOOKUP和LOOKUP這兩個高頻函數(shù)的用法！

系列結(jié)束語

本系列，我們從VLOOKUP和LOOKUP這兩個函數(shù)的基礎(chǔ)用法開始，逐步擴展到包含查詢、交叉查詢、區(qū)間查詢、橫向查詢、逆向查詢，直至本文講解的缺失查詢和多條件查詢，全程深入對比了VLOOKUP和LOOKUP這兩個函數(shù)，并詳細(xì)說明了每一個公式的計算原理，重點剖析了VLOOKUP+MATCH、LOOKUP的兩分法、LOOKUP(1,0/(條件)......結(jié)構(gòu)等重要知識點。八個回合的較量中，我們發(fā)現(xiàn)，常規(guī)查詢VLOOKUP更具優(yōu)勢，但一旦查詢難度上升時，LOOKUP的優(yōu)勢便慢慢顯露出來。VLOOKUP像是LOOKUP的便捷版，使用起來更加便利但也有更多限制，LOOKUP則使用起來更加自由但更難把握。至此， VLOOKUP&LOOKUP雙雄戰(zhàn)就此畫上句號，千里良駒VLOOKUP，荒原野馬LOOKUP，你更喜歡哪個？

本文配套的練習(xí)課件請加入QQ群：264539405下載。

做Excel高手，快速提升工作效率，部落窩教育《一周Excel直通車》視頻和《Excel極速貫通班》直播課全心為你！

掃下方二維碼關(guān)注公眾號，可隨時隨地學(xué)習(xí)Excel：

相關(guān)推薦：

VLOOKUP&LOOKUP雙雄戰(zhàn)（一）《VLOOKUP&LOOKUP雙雄戰(zhàn)（一）：VLOOKUP的漂亮開局》

VLOOKUP&LOOKUP雙雄戰(zhàn)（二）《VLOOKUP&LOOKUP雙雄戰(zhàn)(二)：LOOKUP劍走偏鋒》

VLOOKUP&LOOKUP雙雄戰(zhàn)（三）《VLOOKUP&LOOKUP雙雄戰(zhàn)（三）：LOOKUP守得云開見月明》

VLOOKUP&LOOKUP雙雄戰(zhàn)（四）《VLOOKUP&LOOKUP雙雄戰(zhàn)（四）：在橫向和逆向查詢上的血拼！》