Excel函數查找之王——lookup函數之進階應用

在上一篇教程中，我們學習了lookup函數入門。今天讓我們深入學習lookup函數的其他應用，深入挖掘其內涵，徹底發揮其功能。

首先我們回顧LOOKUP函數的函數語法。它有兩種語法形式：向量和數組。函數 LOOKUP 的向量形式是在單行區域或單列區域(向量)中查找數值，然後返回第二個單行區域或單列區域中相同位置的數值;函數 LOOKUP 的數組形式在數組的第一行或第一列查找指定的數值，然後返回數組的最後一行或最後一列中相同位置的數值。

(1)向量形式：

公式為 = LOOKUP(lookup_value,lookup_vector,result_vector)

式中lookup_value—函數LOOKUP在第一個向量中所要查找的數值，它可以為數字、文本、邏輯值或包含數值的名稱或引用;

lookup_vector—只包含一行或一列的區域lookup_vector 的數值可以為文本、數字或邏輯值;

result_vector—只包含一行或一列的區域其大小必須與 lookup_vector 相同。

(2)數組形式：公式為

= LOOKUP(lookup_value,array)

式中array—包含文本、數字或邏輯值的單元格區域或數組它的值用於與 lookup_value 進行比較。

例如：LOOKUP(5.2,{4.2,5,7,9,10})=5。

注意：array的數值必須按升序排列，否則函數LOOKUP不能返回正確的結果。文本不區分大小寫。如果函數LOOKUP找不到lookup_value，則查找array中小於lookup_value的最大數值。如果lookup_value小於array中的最小值，函數LOOKUP返回錯誤值#N/A。

一、查找最後一個文本值或數值

左圖為數據表，請問最後一個文本值或數值是什麼?

最後一個數值公式：

B2=LOOKUP(9E 307,A:A)

公式解讀：9E 307是excel能識別的最大數，通過在A列中查找9E 307這個數，當然，我們也可以用其他一個很大的值來代替。因為函數默認區域內數值是升序排列的，它會從中間數開始查找，查找到的數值一定是比9E 307小的，因為默認升序排列，要繼續向下查找......,直至返回最後一個數值為止。

最後一個文本公式：

C2=LOOKUP("座",A:A)

公式解讀：座是excel中支持的比較大的一個中文字元，通過查找座這個字元。來查找最後一個文本。

合併單元格是萬惡之源。當我們遇到合併單元格時候，我們可以先破合併單元格，填充相應的內容。上圖為三國名人表，請在姓名後面將每個人的國家找出來。

以前我教過一個方法，破合併單元格，即先取消合併單元格，再定位空值，最後再按ctrl enter即可。今天再交給大家一個方法，使用lookup函數公式來直接破合併單元格。

公式：C2=LOOKUP(1,0/($A$2:A2<>""),$A$2:A2)

公式解讀：$A$2:A2是混合引用，如果將公式往下拉動，引用範圍不斷發生變化為：A2：A2，A2：A3，A2：A4，A2：A5。<>""是不為空。A2：A6，只有A2是不為空的值，其他都為空值。因此A2：A6<>""會返回邏輯值true，false，false，false，false，false。第一個邏輯值true對應的是A2，即蜀國，因此lookup函數公式會將從A2至A6單元格的數據，都得出A2結果，也就實現合併單元格的破解，並填充相應的數值。

如果不理解公式，只要拿來用即可，我們最終的目標是解決問題。

左表為各行全稱，請問交通的全稱是什麼?

公式一：D2=LOOKUP(1,0/FIND(C2,A2:A9),A2:A9)

公式解讀：FIND(C2,A2:A9)是表示在A2:A9內查找c2內容，如果查找不到則返回錯誤值，如果查到返回在具體的位置。

0/FIND(C2,A2:A9)，用0除以上一步得到的內存數組，如果上一步得到的是數值，則返回0，其他返回為錯誤值。

LOOKUP(1,0/FIND(C2,A2:A9),A2:A9)

返回「數組」中最後一個小於等於1(也就是「數組」中的0)的值對應的A2:A9的內容，也就是全稱。

如果實在不理解的話，先背下來，這種就是套路。Excel有很多函數公式套路，自古套路得人心。隨著時間的推移，學習的擴張，未來總有一天會有豁然開朗。

公式二：=VLOOKUP("*"&C2&"*",A2:A9,1,0)

本案例中用vlookup也是可以的。先將簡稱部分用連接符和星號前後連接起來，然後再利用vlookup進行查找，第三個參數是1，因為我們是要找全稱。最後一個參數可填0或者不填，不填就是默認是精確查找。

本教程的源數據表百度網盤網址是：http://pan.baidu.com/s/1eS2FDF8

方法總比問題多，不同的人會有不同的思路。我們的最終目的是解決問題，而不是炫耀技能。