編按：

哈嘍，大家好！前面說到一直處于下風(fēng)的LOOKUP，終于在第四回合的較量中，扳回一局。今天比拼的項目是橫向和逆向查詢?？礃幼?，這似乎對縱向查詢的VLOOKUP不太友好??！LOOKUP又能否乘勝追擊，再贏一輪呢？讓我們拭目以待！

面對VLOOKUP的步步緊逼，LOOKUP終于在第四回合的較量中，憑借二分法遏住頹勢。重整旗鼓后，LOOKUP吹響了反攻的號角，LOOKUP的1/0結(jié)構(gòu)正式登場，犀利進攻，看VLOOKUP如何應(yīng)敵！

ROUND 05  橫向查詢

在數(shù)據(jù)查詢中，我們也經(jīng)常遇到這樣的問題，查找范圍分布在同一行而非同一列，即橫向查詢問題。如下圖，我們要根據(jù)職位查找草帽海賊團中的人物姓名，應(yīng)該怎么做呢？

圖5.1：橫向查詢——劍士索隆

LOOKUP：砍瓜切菜，輕松EASY

這種問題對VLOOKUP來說可能充滿挑戰(zhàn)，但對LOOKUP而言，簡直是如砍瓜切菜般輕松EASY?。?！

=LOOKUP(B7,B2:K2,B3)

圖5.2：橫向查詢——LOOKUP砍瓜切菜

公式說明

LOOKUP相對于VLOOKUP來說是更自由的函數(shù)，它對查詢區(qū)域進行二分法匹配，并不要求查詢區(qū)域需縱向排列。用LOOKUP來完成橫向查詢時，其語句和縱向查詢并無區(qū)別。但在橫向查詢時，目標區(qū)域可以簡寫為結(jié)果區(qū)域的首個單元格。這是因為，當(dāng)LOOKUP的第三個參數(shù)被簡寫時，它會自動橫向擴展結(jié)果區(qū)域直至與查詢區(qū)域等長！也就是說，本例中的 “=LOOKUP(B7,B2:K2,B3)”與“=LOOKUP(B7,B2:K2,B3:K3)”等同。

VLOOKUP：內(nèi)有賢臣，外有強援

面對LOOKUP的挑釁，不可一世的VLOOKUP函數(shù)絕不輕易認輸，橫向查詢硬上也要上！請出轉(zhuǎn)置函數(shù)TRANSPOSE來幫忙。

{=VLOOKUP(B7,TRANSPOSE($A$2:$K$3),2,0)}

圖5.3：橫向查詢——VLOOKUP的賢臣

公式說明

TRANSPOSE函數(shù)是一個轉(zhuǎn)置函數(shù)，其效果與選擇性粘貼中的轉(zhuǎn)置相同。我們通過TRANSPOSE函數(shù)將橫向區(qū)域轉(zhuǎn)置為縱向區(qū)域，然后再用VLOOKUP函數(shù)進行縱向查詢。“每一個成功的函數(shù)背后都有另一個優(yōu)秀的函數(shù)”，大概就是這個道理。注意，該公式為數(shù)組公式，輸入公式后需按Ctrl+Shift+Enter三鍵才能返回正確的結(jié)果。

當(dāng)然，打仗親兄弟，VLOOKUP也沒必要事事親力親為，有時請個外援能解決的事，何必仰人鼻息。雖說這樣做有違決斗精神，但成王敗寇，過程，Who care?

=HLOOKUP(B7,$A$2:$K$3,2,0)

圖5.4：橫向查詢——VLOOKUP的強援

公式說明

HLOOKUP函數(shù)是VLOOKUP函數(shù)的孿生兄弟，其功能和用法與VLOOKUP如出一轍，差別僅在于HLOOKUP是橫向查詢，即它是在查詢范圍的第一行匹配目標值，而不是在第一列。本例中，HLOOKUP函數(shù)將B7與查詢區(qū)域第一行A2:K2一一匹配，找到等于B7的H2，返回H2所在列與查詢區(qū)域第2行對應(yīng)的單元格H3的值。

第五回合，橫向查詢，VLOOKUP雖然有TRANSPOSE這樣的幫手為內(nèi)應(yīng)，更兼親兄弟HLOOKUP函數(shù)這樣的外援，但仍難以扭轉(zhuǎn)敗局。此番，LOOKUP勝在簡單、勝在可縮寫，勝在橫縱皆宜！！

ROUND 06 逆向查詢

前述應(yīng)用場景中，查詢區(qū)域都有一個共同點，即結(jié)果區(qū)域或結(jié)果行列始終在查詢區(qū)域或匹配行列的右側(cè)或下方，這很符合VLOOKUP的查詢要求，因此它總能通過匹配首列返回指定列。但很多時候，結(jié)果區(qū)域并不總是在匹配區(qū)域的右側(cè)，例如：

VLOOKUP：天賦不足，嵌套來補

此時，VLOOKUP函數(shù)是不是黔驢技窮了？當(dāng)然不是，IF({1,0},....)了解一下！

=VLOOKUP(D2,IF({1,0},B2:B9,A2:A9),2,0)

圖6.1：逆向查詢——VLOOKUP與IF({1,0}

公式說明

本例中我們觀察到查詢值D2所需匹配的列“惡魔果實”在結(jié)果列“人物”的右側(cè)，我們無法正常使用VLOOKUP“匹配首列返回第N列”來完成。所以此時解決問題的思路就是如何讓B列“惡魔果實”出現(xiàn)在A列“人物”的左側(cè)，進而將B列作為VLOOKUP查詢范圍的“首列”。解決這一問題的方法就是IF({1,0},....)結(jié)構(gòu)。我們可以從下面三個方面來理解它：

1.IF函數(shù)是邏輯函數(shù)，它的基本語句是=IF(logical_test,value_if_true,value_if_false)；

2.數(shù)值1表示TRUE，0表示FALSE；

3.{1,0}表示由1和0組成的數(shù)組。

綜上，IF({1,0},....)的首個條件是TRUE和FALSE組成的數(shù)組，而IF(TRUE和IF(FALSE又分別返回value_if_true和value_if_false，即IF({1,0},....)的返回值是value_if_true和value_if_false組成的數(shù)組。

接下來，小花套用上圖具體分解一下。

IF({1,0},B2:B9,A2:A9)

={IF(1,B2:B9,A2:A9),IF(0,B2:B9,A2:A9)}

={IF(TRUE,B2:B9,A2:A9),IF(FALSE,B2:B9,A2:A9)}

={B2:B9,A2:A9}

={"橡膠果實","路飛";"花花果實","羅賓";"黃泉果實","布魯克";"人人果實","喬巴";"手術(shù)果實","羅";"磁鐵果實","基德 ";"霸王龍果實","X·德雷克";"城堡果實","卡彭·貝基"}

它的作用是為VLOOKUP構(gòu)建一個虛擬的查詢范圍B2: A9，其中匹配列B2:B9在結(jié)果列A2:A9的左側(cè)。緊接著，VLOOKUP發(fā)揮所長，完成查詢工作。

LOOKUP：木有壓力，純屬炫技

當(dāng)然，這類所謂逆向查詢，對于LOOKUP函數(shù)是不存在任何困擾的。查詢區(qū)域和結(jié)果區(qū)域分離，給了LOOKUP很大的便利。但LOOKUP的另一屬性卻經(jīng)常困擾使用者，那就是其自帶的模糊查詢要求——查詢區(qū)域必須升序排列，否則公式幾乎都會出錯！這一屬性使得很多小伙伴傾向于使用VLOOKUP來解決問題。借著逆向查詢這個輕松取勝的回合，小花要為LOOKUP正名：首列不升序，一樣可以查詢，LOOKUP沒有死角！

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

圖6.2：逆向查詢——LOOKUP(1,0/(條件)......結(jié)構(gòu)

公式說明

在該系列文章中，我們首次使用到經(jīng)典的LOOKUP(1,0/(條件)......結(jié)構(gòu)。不夸張地說，該結(jié)構(gòu)是史詩級的，它主要用到以下知識點：

1.LOOKUP函數(shù)自帶數(shù)組運算，無需按Ctrl+Shift+Enter。該結(jié)構(gòu)中的條件通常表示為“匹配列區(qū)域=目標單元格”的形式，通過數(shù)組運算，相等返回TRUE，不相等返回FALSE。再用數(shù)字0除以運算結(jié)果，0/TRUE=0/1=0，O/FALSE=0/0=#DIV/0!；即LOOKUP(1,0/(條件)......結(jié)構(gòu)在計算過程中，參數(shù)2查詢區(qū)域是由0和#DIV/0!組成的數(shù)組{0,#DIV/0!,#DIV/0!,0...}；

2.LOOKUP的匹配過程會自動忽略錯誤值，即參數(shù)2運算過程中的#DIV/0!將被忽略，僅保留所有的0，即{0,0,0...}；

3.LOOKUP采用二分法查詢，返回最后一個小于或等于目標值的匹配列值所對應(yīng)的結(jié)果；LOOKUP(1,0/(條件)......結(jié)構(gòu)的查詢目標值為1，查詢區(qū)域是N個0組成的有序數(shù)組，所以，最后一個0所對應(yīng)的值即為公式返回結(jié)果。反推，即LOOKUP(1,0/(條件)......結(jié)構(gòu)總是返回最后一個滿足條件的值。

本例中的條件為B2:B9=D2，僅B2等于D2，返回TRUE,其余返回FALSE。即0/(B2:B9=D2)的查詢區(qū)域結(jié)果為{0,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!,#DIV/0!}，LOOKUP忽略錯誤值后僅B2對應(yīng)的結(jié)果0小于目標值1，所以公式返回B2對應(yīng)的A列人物名“路飛”。

第六回合，把VLOOKUP嚇出一身汗的逆向查詢問題，卻成了LOOKUP炫技的背景板，高下立現(xiàn)。

結(jié)束語

本文中，我們引入了查詢函數(shù)圈不可不會的經(jīng)典套路——LOOKUP 1/0結(jié)構(gòu)。這是一個非常高能的函數(shù)用法，說來你可能不信，小花用了整整一周的時間來思考如何更好地講解這一知識點，希望能給小伙伴們帶來幫助！

本文配套的練習(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守得云開見月明》