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

——————————————

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

***ROUND 05 橫向查詢

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

LOOKUP：砍瓜切菜，輕松EASY

這種問(wèn)題對(duì)VLOOKUP來(lái)說(shuō)可能充滿挑戰(zhàn)，但對(duì)LOOKUP而言，簡(jiǎn)直是如砍瓜切菜般輕松EASY?。?！

=LOOKUP(B7,B2:K2,B3)

公式說(shuō)明

LOOKUP相對(duì)于VLOOKUP來(lái)說(shuō)是更自由的函數(shù)，它對(duì)查詢區(qū)域進(jìn)行二分法匹配，并不要求查詢區(qū)域需縱向排列。用LOOKUP來(lái)完成橫向查詢時(shí)，其語(yǔ)句和縱向查詢并無(wú)區(qū)別。但在橫向查詢時(shí)，目標(biāo)區(qū)域可以簡(jiǎn)寫(xiě)為結(jié)果區(qū)域的首個(gè)單元格。這是因?yàn)?，?dāng)LOOKUP的第三個(gè)參數(shù)被簡(jiǎn)寫(xiě)時(shí)，它會(huì)自動(dòng)橫向擴(kuò)展結(jié)果區(qū)域直至與查詢區(qū)域等長(zhǎng)！也就是說(shuō)，本例中的

“=LOOKUP(B7,B2:K2,B3)”與“=LOOKUP(B7,B2:K2,B3:K3)”等同。

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

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

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

公式說(shuō)明

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

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

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

公式說(shuō)明

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

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

***ROUND 06 逆向查詢

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

VLOOKUP：天賦不足，嵌套來(lái)補(bǔ)

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

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

公式說(shuō)明

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

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

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

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

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

接下來(lái)，小花套用上圖具體分解一下。

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í)","路飛";"花花果實(shí)","羅賓";"黃泉果實(shí)","布魯克";"人人果實(shí)","喬巴";"手術(shù)果實(shí)","羅";"磁鐵果實(shí)","基德 ";"霸王龍果實(shí)","X·德雷克";"城堡果實(shí)","卡彭·貝基"}

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

LOOKUP：木有壓力，純屬炫技

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

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

公式說(shuō)明

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

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

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

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

本例中的條件為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忽略錯(cuò)誤值后僅B2對(duì)應(yīng)的結(jié)果0小于目標(biāo)值1，所以公式返回B2對(duì)應(yīng)的A列人物名“路飛”。

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

***結(jié)束語(yǔ)***

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

＊＊＊＊部落窩教育－excel查詢函數(shù)技巧＊＊＊＊

原創(chuàng)：小花/部落窩教育（未經(jīng)同意，請(qǐng)勿轉(zhuǎn)載）