【excellookup函數用法】深度解析：掌握其向量形式與數組形式，告別VLOOKUP的局限
在Excel的眾多函數中，LOOKUP函數是一個強大而有時被低估的工具。它能夠幫助我們在一個範圍內查找某個值，並返回對應位置上的另一值。儘管現代Excel版本中出現了功能更強大的XLOOKUP，以及廣為人知的VLOOKUP和HLOOKUP，但LOOKUP函數憑藉其獨特的機制和簡潔的語法，在特定場景下依然具有不可替代的優勢。本文將深入探討LOOKUP函數的兩種主要形式——向量形式（Vector Form）和數組形式（Array Form），詳細解析其用法、特點、適用場景及注意事項，幫助您充分理解並掌握這一實用功能。
認識LOOKUP函數：核心概念與基本原理
LOOKUP函數主要用於在一個單行或單列區域（或整個表格）中查找一個指定值，然後返回另一個單行或單列區域（或表格的最後一個行/列）中對應位置的值。其最顯著的特點是它默認執行近似匹配，這使得它非常適合處理分段查找或區間判斷的任務。在使用LOOKUP函數之前，理解其工作原理至關重要：
- 近似匹配（Approximate Match）：
LOOKUP函數總是嘗試找到小於或等於查找值（lookup_value）的最大值。如果找不到完全匹配的值，它會選擇小於lookup_value的最大值。 - 數據排序要求： 為了確保
LOOKUP函數正確運行並返回預期的結果，其查找的範圍（無論是向量還是數組的第一行/列）必須按升序排列。如果數據未排序，結果將是不可預測的，甚至可能返回錯誤。
LOOKUP函數的向量形式（Vector Form）
向量形式是LOOKUP函數最直接、最易於理解和使用的形式，它允許你在一個「查找向量」中找到值，然後從一個「結果向量」中返回對應的值。
語法
LOOKUP(lookup_value, lookup_vector, [result_vector])
參數解釋：
lookup_value(必需)： 你想要查找的值。這個值可以是數字、文本或邏輯值。lookup_vector(必需)： 包含你想要查找的數據的區域。它必須是單行或單列的單元格區域（即一個向量）。例如，A1:A10或B1:D1。這個區域的數據必須按升序排列。[result_vector](可選)： 包含你想要返回的數據的區域。它也必須是單行或單列的單元格區域，且與lookup_vector的長度和方向（行或列）相同。如果省略此參數，LOOKUP函數會返回lookup_vector中找到的值。
工作原理
LOOKUP函數首先在 lookup_vector 中找到小於或等於 lookup_value 的最大值。然後，它會返回 result_vector 中與該匹配值在相同位置上的值。
實戰案例：查找考試成績等級
假設我們有一個學生成績表，並希望根據分數段自動判斷學生的等級（優秀、良好、及格、不及格）。
數據設置：
成績等級標準（假設在 E2:E5 是分數下限，F2:F5 是對應等級）：
E列（分數下限） | F列（等級）
-------------- | ----------
0 | 不及格
60 | 及格
80 | 良好
90 | 優秀
學生成績：
A列（姓名） | B列（分數） | C列（等級）
---------- | ----------- | ----------
小明 | 75 |
小紅 | 92 |
小張 | 55 |
小李 | 80 |
在C2單元格輸入以下公式，並向下拖動填充：
=LOOKUP(B2, $E$2:$E$5, $F$2:$F$5)
公式解釋：
B2：這是我們要查找的學生分數（lookup_value）。$E$2:$E$5：這是分數下限的查找範圍（lookup_vector），注意它已按升序排列。使用絕對引用$符號是為了在拖動公式時保持這個範圍不變。$F$2:$F$5：這是對應的等級結果範圍（result_vector）。
結果：
- 小明 (75分):
LOOKUP會在E2:E5中找到小於或等於75的最大值，即60。然後返回F2:F5中對應60位置的值，即「及格」。 - 小紅 (92分): 找到90，返回「優秀」。
- 小張 (55分): 找到0，返回「不及格」。
- 小李 (80分): 找到80，返回「良好」。
通過這個例子可以看出，LOOKUP的向量形式在處理區間判斷和分段查找時非常高效和簡潔。
LOOKUP函數的數組形式（Array Form）
數組形式的LOOKUP函數通常用於在整個表格或一個矩形區域中進行查找。它的工作方式與向量形式略有不同，並且具有一些特殊的行為模式。
語法
LOOKUP(lookup_value, array)
參數解釋：
lookup_value(必需)： 你想要查找的值。可以是數字、文本或邏輯值。array(必需)： 一個包含數據的工作表區域（一個矩形區域），或是一個命名區域。
工作原理
數組形式的LOOKUP函數會在 array 的第一行或第一列中查找 lookup_value。具體是查找行還是列，取決於 array 的形狀：
- 如果
array的行數多於列數，或者行數與列數相等，LOOKUP會在array的第一列中查找lookup_value。 - 如果
array的列數多於行數，LOOKUP會在array的第一行中查找lookup_value。
一旦找到匹配（近似匹配，小於或等於 lookup_value 的最大值），LOOKUP 函數將返回 array 中最後一行或最後一列中與該匹配值對應位置的值。
與向量形式一樣，array 的第一行或第一列（取決於查找方向）必須按升序排列。
實戰案例：查找產品價格（基於產品ID）
假設我們有一個產品信息表，包含產品ID、名稱和價格。
數據設置：
產品信息表（假設在 E2:G5）：
E列（產品ID） | F列（產品名稱） | G列（價格）
-------------- | -------------- | ----------
1001 | 鼠標 | 50
1002 | 鍵盤 | 120
1003 | 顯示器 | 800
1004 | 攝像頭 | 200
查找需求：
A列（訂單ID） | B列（查找產品ID） | C列（產品價格）
---------- | --------------- | ----------
ORD001 | 1003 |
ORD002 | 1001 |
ORD003 | 1002 |
在C2單元格輸入以下公式，並向下拖動填充：
=LOOKUP(B2, $E$2:$G$5)
公式解釋：
B2：我們要查找的產品ID（lookup_value）。$E$2:$G$5：這是我們的產品信息表（array）。此區域的行數（4行）多於列數（3列），因此LOOKUP會在第一列（E列，產品ID）中查找。查找成功后，它會返回最後一列（G列，價格）中對應位置的值。
結果：
- 訂單ID ORD001 (查找產品ID 1003):
LOOKUP會在E列中找到1003。然後返回G列中對應1003位置的值，即800。 - 訂單ID ORD002 (查找產品ID 1001): 找到1001，返回50。
- 訂單ID ORD003 (查找產品ID 1002): 找到1002，返回120。
請注意，儘管數組形式可以完成類似VLOOKUP的任務，但它的查找和返回邏輯不如VLOOKUP直觀。VLOOKUP明確指定了返回列的索引，而LOOKUP的數組形式則固定返回第一列/行或最後一列/行。
LOOKUP函數對比其他查找函數：優勢與局限
LOOKUP的優勢：為何仍需學習它？
- 簡潔性： 在特定場景下，尤其是近似匹配且查找範圍和結果範圍是簡單的向量時，
LOOKUP的語法比VLOOKUP或INDEX+MATCH更簡潔。 - 不限查找列位置： 與
VLOOKUP不同，LOOKUP函數的向量形式允許result_vector在lookup_vector的左側或右側，這提供了更大的靈活性。數組形式也只關心第一列/行和最後一列/行，不要求返回列在查找列的右側。 - 默認近似匹配： 對於需要進行區間判斷和分段查找的場景，
LOOKUP函數是天生的利器，無需額外參數設置。 - 查找最後一個非空值或數值： 結合一些技巧，
LOOKUP可以非常巧妙地實現查找某一行或某一列中最後一個數值或非空文本。例如：=LOOKUP(9.99999999999999E+307,A:A)可以查找A列中最後一個數值。
LOOKUP的局限性與注意事項：
- 強制升序排序： 這是
LOOKUP函數最大的局限。如果查找範圍未按升序排列，函數將返回錯誤結果，甚至#N/A錯誤。而VLOOKUP、HLOOKUP和XLOOKUP在精確匹配模式下沒有此限制。 - 默認近似匹配： 雖然是優勢，但在需要精確匹配的場景下，
LOOKUP無法直接實現。此時必須依賴VLOOKUP(...,FALSE)、MATCH(...,0)或XLOOKUP(...,0)。 - 數組形式的非直觀性： 數組形式的查找方向（第一行/列）和返回位置（最後一行/列）是固定的，不如
VLOOKUP和HLOOKUP那樣可以明確指定返回列/行索引，或者INDEX+MATCH那樣高度靈活。這可能導致初學者混淆。 - 當lookup_value小於查找範圍最小值時： 如果
lookup_value小於lookup_vector或array中的最小值，LOOKUP函數將返回#N/A錯誤。
高級應用：查找最後一個數值或非空文本
LOOKUP函數的一個鮮為人知但非常實用的高級技巧是查找某個區域（行或列）中的最後一個數值或非空文本。這通常通過利用LOOKUP的近似匹配特性和Excel對極大數的處理來實現。
查找最後一個數值：
假設你有一列數據（例如 A列），其中包含數字和一些空單元格或文本，你想要找到這列中最後一個出現的數字。
=LOOKUP(9.99999999999999E+307, A:A)
解釋：
9.99999999999999E+307是Excel中能表示的最大正數。- 當
LOOKUP查找這個極大值時，它會在A列中尋找小於或等於這個極大值的最大數字。由於這個值比任何實際的數字都大，它最終會找到A列中最後一個出現的數字。
查找最後一個非空文本：
類似地，可以查找最後一個非空文本。
=LOOKUP(REPT("Z",255), A:A)
解釋：
REPT("Z",255)生成一個由255個「Z」組成的字符串，這是Excel中能表示的最長字符串之一，且在字母排序上通常晚於任何實際的文本。LOOKUP會找到A列中按字母順序排列小於或等於這個「ZZZ...」字符串的最後一個文本值。
這種技巧非常靈活，可以在處理動態數據範圍時派上用場，例如在處理不確定數據行數時，需要總是引用到最後一行數據。
總結
LOOKUP函數雖然不如VLOOKUP或XLOOKUP那樣萬能，但它在處理近似匹配和區間查找方面具有獨特的優勢。理解其向量形式和數組形式的不同工作原理，並牢記其對查找範圍升序排列的嚴格要求，是掌握此函數的關鍵。當您需要處理等級評定、分段傭金計算或查找最後一個非空值等任務時，LOOKUP函數無疑是一個值得考慮的簡潔高效的解決方案。通過本文的詳細解析和實例，相信您已對LOOKUP函數的用法有了全面的認識，並能在實際工作中靈活運用。
常見問題（FAQ）
如何判斷LOOKUP函數應該使用向量形式還是數組形式？
這取決於你的數據組織方式和查找需求。如果你的查找值和結果值分別位於兩個獨立的單行或單列區域，且希望根據查找值在其中一個區域中找到位置，然後在另一個區域返回對應位置的值，那麼向量形式（LOOKUP(lookup_value, lookup_vector, [result_vector])）更合適。如果你的數據在一個矩形表格中，並且你希望在表格的第一行或第一列查找，然後返回最後一行或最後一列的值，那麼數組形式（LOOKUP(lookup_value, array)）可能更適用。一般來說，向量形式更常用且更直觀。
為何LOOKUP函數要求查找範圍必須升序排列？
LOOKUP函數的設計初衷就是為了高效執行近似匹配。它的內部算法依賴於查找範圍的有序性來進行二分查找或類似的優化查找。當查找值時，它會快速定位到小於或等於查找值的最大值。如果數據未排序，這種查找機制將無法正常工作，導致函數返回錯誤的結果（可能是一個看似隨機的值）或#N/A錯誤。因此，務必在應用LOOKUP函數前，對你的查找範圍進行升序排序。
如何用LOOKUP函數實現精確匹配？
LOOKUP函數本身不直接提供精確匹配的選項，它總是進行近似匹配。如果你需要精確匹配，建議使用其他函數，例如VLOOKUP(lookup_value, table_array, col_index_num, FALSE)（將最後一個參數設為FALSE），或者更靈活的INDEX和MATCH組合，再或者Excel 365中的XLOOKUP函數。
LOOKUP函數返回#N/A錯誤是為什麼？
LOOKUP函數返回#N/A錯誤通常有以下幾個原因：
- 查找值小於查找範圍的最小值： 如果
lookup_value比lookup_vector或array中的任何值都小，LOOKUP函數將無法找到「小於或等於」的匹配項。 - 查找範圍未排序： 這是最常見的原因之一。即使數據中包含查找值，如果範圍未按升序排列，函數也可能因為無法正確執行查找邏輯而返回
#N/A。 - 查找範圍或結果範圍為空或包含錯誤值。
#N/A錯誤。
LOOKUP函數和XLOOKUP函數有什麼區別？我應該用哪個？
LOOKUP和XLOOKUP雖然都用於查找，但功能和靈活性有顯著差異。
LOOKUP： 默認近似匹配，查找範圍必須升序排列，支持向量和數組兩種形式，但數組形式的查找和返回邏輯相對固定。XLOOKUP： 是Excel 365及更高版本中引入的更現代、功能更強大的函數。它默認精確匹配，但也可以輕鬆配置為近似匹配。它不限制查找範圍的排序，支持從左到右或從右到左的查找，並且可以返回多個結果，處理錯誤等。
XLOOKUP，因為它功能更全面，更易於使用，並且解決了LOOKUP和VLOOKUP的許多局限。只有在特定場景（如需要查找最後一個值，或在舊版Excel中且數據已排序）下，LOOKUP函數才可能是一個更簡潔的選擇。
