Excel中Lookup()函數的使用技巧

Lookup()與Vlookup()和Hlookup()同屬於查詢類函數，Lookup()函數僅在單行或者單列中查詢，它有兩種語法結構：

第一種是向量形式的語法結構：Lookup(lookup_value，lookup_vector，[result_vector])，功能是在某一行或者某一列查詢指定的值，然後返回另一行或者另一列相同位置的值。

lookup_value表示要查詢的值。

lookup_vector表示在其中查詢指定值的單行（或者單行的一維陣列）或者單列（或者單列的一維陣列）。

[result_vector]可選，表示返回值的行或者列，大小必須與第二個參數lookup_vector一樣。

第二種是陣列形式的語法結構：Lookup(lookup_value，array)，功能是在陣列首行或者首列查詢指定的值，然後返回陣列最後一行或者列相同位置的值。

在介紹Lookup()函數的使用技巧之前，首先強調使用該函數的幾個注意事項：

1、查詢區域的資料即函數中的第二個參數（陣列的首行或者首列）必須按照升序排列，否則結果可能有誤。

2、如果在查詢區域找不到指定值，則返回小於指定值的最大值。

3、如果指定的查詢值小於查詢區域的最小值，函數會返回錯誤值。

我們用例項來演示一下函數的使用方法。

在單元格H2鍵入公式「=LOOKUP(G2,B2:B10,D2:D10)」，「產品2」是指定的查詢值，B2:B11是查詢列，D2:D11是返回值的列，與查詢列大小相等，語法沒有問題，但結果顯然不準確，原因是查詢列未按照大小排序。

將B列按照「數值」升序排列之後，結果就對了。

以上公式也可以改成陣列形式「=LOOKUP(G3,B2:D11)」，結果一樣。

接下來，我們介紹幾個Lookup的使用技巧：

1、逆向查詢

通常查詢列在左側，返回資料列在右側，反之稱為逆向查詢。如果想用Vlookup()函數實現逆向查詢，需要使用輔助列或者是創建一個記憶體陣列等輔助方式（請參閱Excel中Vlookup()函數使用技巧）。而Lookup()函數自帶逆向查詢的功能，在單元格N2鍵入公式「=LOOKUP(M2,B2:B10,A2:A10)」，便能返回位於查詢列左側的序號。

2、多條件判斷

提到多條件判斷，往往會想到IF()函數的巢狀使用，實際上可以考慮用Lookup()代替，公式可能更加清晰明瞭。

單元格區域J1:K4為投入標準的規則，據此描述鍵入公式「=LOOKUP(C2,{0;3000;5000},{"有限投入";"重點投入";"保持投入"})」即可判斷不同產品的投入標準。因為函數返回等於或者小於指定值的最大值，所以取區間最小值組成查詢列。以公式為例，單元格C2的「3900」是查詢值，第一個陣列即查詢陣列中沒有相同的值，所以匹配小於「3900」的最大值即「3000」，返回第二個陣列相同位置的值即「重點投入」。

3、返回多值

我們希望用一個公式查詢所有奇數產品「實際達成」的值並且求和，用Vlookup()函數要頗費些功夫，因為Vlookup()函數即使將第一個參數寫作陣列也只能返回查詢到的第一個值而不能返回一個記憶體陣列，需要藉助N()和IF()函數實現（具體請參閱Excel中T()函數和N()函數的使用技巧）。

如果使用Lookup()函數就輕鬆得多，Lookup()的第一個參數可以是陣列並且能夠返回一個記憶體陣列參加其它運算。以上公式改為「=SUM(LOOKUP("產品"&{1,3,5,7,9},B2:D11))」，就能得到我們需要的結果。

"產品"&{1,3,5,7,9}通過連線符生成一個奇數產品名稱的陣列{"產品1","產品3","產品5","產品7","產品9"}作為查詢值，B列是查詢區域，返回D列的資料，生成一個記憶體陣列{2836,872,5100,8100,600}，然後通過Sum()函數求和。

4、有合併單元格資料表格的統計

不需要修改原資料表格或者新增輔助列，在單元格H2鍵入公式「=SUM(--(LOOKUP(ROW($2:$11),IF($A$2:$A$11<>"",ROW($2:$11)),$A$2:$A$11)=G2))」即可計算各渠道的產品個數。

其中：

ROW($2:$11)生成單元格行號陣列{2;3;4;5;6;7;8;9;10;11}作為查詢值。

IF($A$2:$A$11<>"",ROW($2:$11))返回查詢區域陣列{2;FALSE;FALSE;FALSE;6;FALSE;FALSE;9;FALSE;FALSE}，其中數字是合併單元格中非空單元格行號，而空單元格返回FALSE。

$A$2:$A$11對應的陣列{"線上";0;0;0;"線下";0;0;"其它";0;0}是返回值。

根據Lookup()函數的特性，查詢值在查詢區域找不到匹配的值時，將匹配小於查詢值的最大值，譬如合併的空值單元格A3對應的數字「3」在查詢區域找不到匹配值，便返回「2」對應的值「線上」。依次特性，Lookup()函數最後生成一個記憶體陣列{"線上";"線上";"線上";"線上";"線下";"線下";"線下";"其它";"其它";"其它"}。

{"線上";"線上";"線上";"線上";"線下";"線下";"線下";"其它";"其它";"其它"}=G2生成一個邏輯值陣列{TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}，雙減號「--」將邏輯值轉化為數值{1;1;1;1;0;0;0;0;0;0}，最後Sum()函數求和得出產品個數。

最後再強調一下，以上例圖中帶了大括號的都是陣列公式，需要Ctrl+Shift和Enter三鍵確認，陣列公式可以參閱知道這些Excel陣列概念和運算規則，陣列公式就豁然開朗了。

Lookup ()函數真香！