你所煩惱的LOOKUP函數，原來是這么回事

你所煩惱的LOOKUP函數，原來是這么回事

話說十年之前，Excel函數方興未艾，大家對函數世界的探索還很熱情，LOOKUP是那時公認的超級函數之一。超級有兩方面的意思，一方面是LOOKUP非常強大，在數據查詢的問題上，基本沒有什么是它解決不掉的事兒；另一方面是這家伙非常復雜，甚至有人根據它的語法，猜想出該函數使用了編程上的經典算法二分法（不過后來證明這猜想在邏輯上完全不能自洽，如果你感興趣，這點我們最后再聊）。

——這是十年之前。如今十年過去了，再復雜的函數也都被總結出簡單的套路了。

LOOKUP函數的官方語法有兩個——但都沒有太大實用價值。這個函數已經被玩到官方定義的語法成為廢紙、民間的套路成為事實上的語法的地步；所以所謂官方語法看一眼就好。

語法1 向量形式▼

=LOOKUP(lookup_value,lookup_vector,result_vector)

語法2 數組形式▼

= LOOKUP(lookup_value,array)

……自古深情留不住，總是套路得人心。網上流傳的LOOKUP套路有很多，但看破表相之后，核心法則也就只有兩個：區間查詢和條件查詢。

此外，雖然是老生常談，但依然要說的是，如果您是函數新手，本章內容有看不懂的部分，又很想看懂，請重讀函數基礎之要，也就是函數系列教程的2、3、7章，特別是第7章：為什么說0和1是Excel函數邏輯運算的核心要義。

1

區間查詢

LOOKUP函數的區間查詢套路語法格式如下：

=LOOKUP(查找值，首列升序排列的查找區域，結果區域)

需要說明的是，使用此套路時，查找范圍的首列必須升序排列。

舉個例子。

如下圖所示，需要根據F:G的評分標準，對A:C數據區域的得分作評級。大于等于0小于60為及格，大于等于60小于80為及格，大于等于80小于90為優良，大于等于90小于95為優秀，大于等于95為優異……也就是將查詢區域劃分為了多個區間，并升序排列。

C2單元格輸入以下公式并向下復制填充即可：

=LOOKUP(B2,F:F,G:G)

F列是升序排列的查找區域，G列是其對應的結果區域。LOOKUP從查找區域中尋找小于等于查找值的最大值，比如尋找81，查詢區域中（F列）小于該值的最大值是80，于是先獲取結果80，然后再返回80所對應的G列結果”優良”。

……可能需要再次強調的是，這種區間查詢方式，要求查找區域的首列必須升級排列！有朋友可能會說F列沒有升序排列啊？F2單元格的0比F1單元格的”分數”小。這沒事，LOOKUP聰明的很，你不說他都知道F1是不是標題行。

本例也可以使用以下公式：

=LOOKUP(B2,F:G)

這是由于當查找區域是多列，同時又省略結果區域時，LOOKUP會默認查找區域的首列（本例為F列）為查找區域，同時默認其最后一列（本例為G列）為結果區域。

注意，咱們說的是最后一列，而不是第2列。

例如，在D2單元格輸入以下公式可以返回H列的評級

=LOOKUP(B2,F:H)

LOOKUP默認首列F列為查找區域，末列H列為結果區域。

看個廣告放松一下

畢竟沒有廣告的推文是不真誠的..▼

2

條件查詢

LOOKUP函數的條件查詢套路語法格式如下：

=LOOKUP(一個比查找范圍內所有同類型值都大的值，查找區域，結果區域)

該套路固定返回查找區域最后一個同類型數據所對應的結果。

比如，查找A列最后出現的文本：

=LOOKUP(“座”,A:A,A:A)

“座”是文本型數據里極大的值，比絕大部分常見的文本值均大，因而該公式返回A列最后出現的文本值。

由于該公式的查找區域和結果區域是相同的，也可以寫為以下形式：

=LOOKUP(“座”,A:A)

當省略結果區域時，LOOKUP默認查找區域即為結果區域。

再比如，查找A列最后出現的數值：

=LOOKUP(9^9,A:A)

9^9是9的9次方，一個極大的數值，比絕大部分數值均大，因而該公式返回A列最后一個數值。

……

覺得這個套路沒有什么實用價值？

打個響指，那咱們就把這個套路換個形式延伸一下……

如下圖所示，需要根據A:B列的數據源，查詢D2單元格指定人名的考試成績。

這是一個單條件的查詢問題，LOOKUP公式如下：

=LOOKUP(1,0/(A1:A10=D2),B1:B10)

(A1:A10=D2)部分判斷A1：A10區域的值是否等于D2，返回由邏輯值TRUE和FALSE組成的內存數組:

{FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}

使用0除以該數組，0/TRUE結果為0，0/FALSE結果為錯誤值#DIV/0!，也就返回一個由0和錯誤值構成的內存數組：

{#DIV/0!;0;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!}

LOOKUP自帶天賦忽略錯誤值，而查找值1又比查找范圍內所有的0都大，因而該公式可以直接返回最后一個符合條件的值所對應的結果。

該公式總結一下，也就成了經典的LOOKUP單條件查詢套路：

=LOOKUP(1,0/(條件區域1=條件值),結果區域)

……

把這個套路再延伸一下，就可以成為”并且”關系的多條件查詢經典套路（注意括號的個數和位置）：

代碼看不全可以左右拖動…▼

=LOOKUP(1,0/((條件區域1=條件值1)*(條件區域2=條件值2)*…(條件區域n=條件值n)),結果區域)

舉個例子。

如下圖所示，需要根據A:C列數據源，計算同時滿足E列期次和F列姓名的數據。

G2公式如下：

=LOOKUP(1,0/((A$1:A$10=E2)*(B$1:B$10=F2)),C$1:C$10)

(A$1:A$10=E2)*(B$1:B$10=F2)部分，使用乘法運算，判斷兩個條件是否同時成立，返回由邏輯值TRUE和FALSE組成的內存數組。

然后還是使用0除以該數組，得到一個由0和錯誤值構成的單列內存數組。

1作為LOOKUP的查找值比查找范圍內所有的數值均大，因而該公式直接返回最后一個符合條件的查詢結果。

此外，和INDEX+MATCH函數不同的是，由于LOOKUP天生支持數組運算，所以盡管該公式執行了多項運算（一次性計算了多個值），也并不需要使用數組三鍵來結束公式的輸入——這就對公式的編寫和維護帶來了極大的便利。

……

繼續延伸，把上述套路的乘法換做加法，就可以成為”或”關系的多條件查詢套路：

代碼看不全可以左右拖動…▼

=LOOKUP(1,0/((條件區域1=條件值1)+(條件區域2=條件值2)+…(條件區域n=條件值n)),結果)

同樣舉個例子。

如下圖所示，需要根據A:C列數據源，查詢E列的姓名滿足A列別稱或B列全稱的成績。

F2公式如下：

代碼看不全可以左右拖動…▼

=LOOKUP(1,0/((A$1:A$10=E2)+(B$1:B$10=E2)),C$1:C$10)

(A$1:A$10=E2)+(B$1:B$10=E2)部分，使用加法的形式，判斷兩個條件是否至少有一條是成立的，返回一個有邏輯值TRUE和FALSE構成的內存數組，剩下的計算步驟就又回到了原來的軌道。

……

依然延伸，搭配FIND函數，就可以成為模糊匹配的查詢套路：

=LOOKUP(1,0/FIND(條件區,條件值),結果區域)

依然舉個例子：

如下圖所示，A列是簡稱，B列是成績，需要根據D列的全稱查找相應的成績。

公式如下：

=LOOKUP(1,0/FIND(A$1:A$8,D2),B$1:B$8)

FIND(A$1:A$8,D2)部分，判斷A1:A8的值在D2單元格是否存在，如果存在，返回數字序號，如不存在，返回錯誤值，比如此例返回內存數組：

{#VALUE!;3;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!;#VALUE!}

用0除以該數組，生成一個由0和錯誤值構成的內存數組……后面的計算步驟還是又回到了原來的軌道。

放飛你的思維，繼續延伸，你就可以得出”并且”關系下的多條件模糊匹配套路：

=LOOKUP(1,0/(FIND(條件區1,條件值1)*FIND(條件區2,條件值2)),結果區域)

換成加法，也就成了或關系多條件模糊匹配套路：

=LOOKUP(1,0/(FIND(條件區1,條件值1)+FIND(條件區2,條件值2)),結果區域)

……

3

結束語

分享了這么多栗子，其實只是LOOKUP在比較規整的單元格區域內的查詢用法，而沒有涉及到數據結構轉換以及字符串處理，也就是說，這一切還只是LOOKUP強大用法的冰山一角……

所以，當INDEX+MATCH說他們比VLOOKUP強大百倍時，作為VLOOKUP的大哥LOOKUP只會冷笑一聲，攏一攏衣袖，心想，呵呵，我比你倆的組合強大的又何止百倍……

當然，所謂強大，也并不是實用的意思。

這就好比大炮固然強大，用來打蚊子那就成了笑話。INDEX+MATCH固然比VLOOKUP強大，但很多情況下，它并不比VLOOKUP書寫簡便，LOOKUP固然比INDEX+MATCH強大，但很多情況下，它的計算效率又比較低下

這三者之間到底有哪些優劣之處？我們下一章再聊。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。