Chapter 7. 函式 (二)：查詢與篩選 › 7.3 查詢函式：-LOOKUP

7.3 查詢函式：-LOOKUP

INDEX + MATCH 是實務上十分常見的功能，熟悉兩個可以完成大部分的查詢功能，而此節要介紹另一組常用的查詢函式 — LOOKUP，包括 VLOOKUP / HLOOKUP / LOOKUP 三個函式，學會這兩節所介紹的函式的用法後，可以自由視習慣選擇其中一種作法完成各種查詢：

  • VLOOKUP / HLOOKUP：垂直 (Vertical) / 水平 (Horizontal) 查詢，找欲搜尋的資料出現在範圍第一欄 / 列的哪一列 / 欄，並輸出該列的某一欄 / 列：

語法

VLOOKUP / HLOOKUP (搜尋準則, 範圍, 索引, [已排序])

輸入

  • 搜尋準則：欲搜尋的資料，必須是單一值。
  • 範圍：要在哪裡找，為多欄多列的陣列或範圍。
  • 索引：要輸出 範圍 的第幾欄 / 列。
  • 已排序：TRUE / FALSE，預設為 TRUE，意義如下：
    • TRUE：用於 搜尋準則 為數字資料且 範圍 第一欄遞增排序時，搜尋的方式會同 MATCH 的搜尋類型 = 1。
    • FALSE：完全比對，同 MATCH 的搜尋類型 = 0。

輸出

  • VLOOKUP：在 範圍 第一欄找 搜尋準則 所在列，輸出第 索引 欄。
  • HLOOKUP：在 範圍 第一列找 搜尋準則 所在欄，輸出第 索引 列。
  • LOOKUP：結合 VLOOKUP 與 HLOOKUP 「已排序」的搜尋用法

語法

  • 用法一：LOOKUP (搜尋準則, 搜尋範圍, 結果範圍)
  • 用法二：LOOKUP (搜尋準則, 搜尋與結果範圍)

輸入

  • 搜尋準則：欲搜尋的資料，必須是單一值。
  • 搜尋範圍：要在哪裡找，為單欄或單列的陣列或範圍。
  • 結果範圍：要輸出的範圍，大小與 搜尋範圍 相同。
  • 搜尋與結果範圍：要在哪裡找、輸出，為多欄多列的陣列或範圍。

輸出

兩種用法的查詢方式同 MATCH 的搜尋類型 = 1，輸出內容分別如下：

  • 用法一：在 搜尋範圍 中查詢 搜尋準則 所在列 / 欄，輸出 結果範圍 的同一列 / 欄。
  • 用法二：在 搜尋與結果範圍 第一欄 / 列查詢 搜尋準則 所在列 / 欄，輸出 搜尋與結果範圍 最後一列 / 欄的同一欄 / 列。其中要搜尋第一列或第一欄取決於 搜尋與結果範圍 的列數與欄數，若欄數 > 列數則搜尋第一列，反之搜尋第一欄。

上述函式說明較為複雜，因此以下使用兩個範例說明之：

  1. 下圖：其中已命名範圍「成績表 / 學生 / 國文成績 / 數學成績 / 英文成績」

    公式

    (1) =VLOOKUP("丁",成績表,3,FALSE)

    (2) =HLOOKUP("英文",成績表,3,FALSE)

    (3) =LOOKUP("丁",學生,數學成績)

    (4) =LOOKUP("丁",成績表)

    解說

    (1) 在 成績表 第一欄找到 "丁" 所在的列 (第 5 列)，輸出第 3 欄的第 5 列，即丁的數學成績 80。

    (2) 在 成績表 第一欄找到 "英文" 所在的欄 (第 4 欄)，輸出第 4 欄的第 3 列，即乙的英文成績 75。

    (3) 在 學生 找到 "丁" 所在的列 (第 5 列)，輸出 數學成績 第 5 列的值。

    (4) 因為 成績表 的列數 >= 欄數，因此在 成績表 第一欄找到 "丁" 所在的列 (第 5 列)，輸出 成績表 最後一欄第 5 列的值，即丁的英文成績 65。

  2. 下圖：其中已命名範圍「成績換算表 / 最小值 / 最大值 / GPA / 等第」

    公式

    (1) =VLOOKUP(75,成績換算表,4,FALSE)

    (2) =VLOOKUP(75,成績換算表,4,TRUE)

    (3) =LOOKUP(75,最小值,等第)

    (4) =LOOKUP(75,成績換算表)

    解說

    (1) 在 成績換算表 第一欄找到 75 所在的列，但因為 已排序=FALSE 且最小值中找不到 75 所以輸出 #N/A!。

    (2) 因為 已排序=TRUE 且第一欄為遞增排序，所以會找到小於 75 的最大值所在的列 (即 73、第 7 列)，輸出第 7 列的第 4 欄，即 B。

    (3) 在 最小值 找到小於 75 的最大值所在的列 (第 7 列)，輸出 等第 第 7 列的值。

    (4) 因為 成績換算表 的列數 >= 欄數，因此在 成績換算表 第一欄找到小於 75 的最大值所在的列 (第 7 列)，輸出 成績換算表 最後一欄第 7 列的值。

了解這些函式後也會發現這三個函式都有一些限制，例如 VLOOKUP / HLOOKUP 的 搜尋準則 一定要出現在 範圍 的首欄 / 列、而 LOOKUP 在搜尋數字時只能針對遞增的資料進行精確的搜尋。

然而上述 VLOOKUP / HLOOKUP 的限制可以使用「陣列」解決，簡單來說陣列包括以下四個要素：

  • 大括號 { }：創立陣列，之後會在大括號內填入陣列的內容。
  • 半形逗號 ,：陣列中的分欄符號。
  • 半形分號 ;：陣列中的分列符號。
  • 欲組合的內容：可以是單一值、儲存格範圍或某公式的輸出等，其中內容的欄或列必須一致才能組合，例如可以使用半形逗號連接 A1:B3 和 D1:D3，因為其列數相同，但若使用分號則會跳出 #REF!，因為前者有兩欄、但後者只有一欄。

詳細陣列的用法及其相關公式將會在第 11 章說明，在此將延續範例 B. 示範如何在 VLOOKUP 中使用陣列，以解決上述欲搜尋項目並非在資料的非第一欄 / 列之限制：

  1. 延續範例 B.，輸出等第 A- 的 GPA

    公式

    =VLOOKUP("A-",{等第,GPA},2,FALSE)

    解說

    • 使用大括號將 等第 和 GPA 合成一個兩欄的陣列，就能使用 VLOOKUP 找出 "A-" 在陣列首欄 (即 等第) 的第幾列 (第 9 列)，輸出第 2 欄 (即 GPA) 的第 9 列，即 3.7。
    • 當然在此可使用 INDEX+MATCH 完成，即 =INDEX(GPA,MATCH("A-",等第,0))。

總而言之，VLOOKUP / HLOOKUP、LOOKUP、INDEX + MATCH 有各自適合的使用時機，根據作者的使用習慣歸納如下：

  • VLOOKUP / HLOOKUP：要參照的欄位是「完全相符」的情況下。
  • LOOKUP：要參照的欄位是「遞增的數字且不用完全相符」的情況下。
  • INDEX + MATCH：要參照的欄位是「遞減的數字且不用完全相符」的情況下，因為 VLOOKUP / HLOOKUP / LOOKUP 皆無法達成。

然而上述函式都還是存在一些限制，例如無法決定搜尋順序 (都是預設左至右、上至下)、搜尋文字時無法使用萬用字元搜尋等，而下一節介紹的兩個函式 — XMATCH、XLOOKUP 將能完美解決這兩個限制。

← 上一節

7.2 查詢函式：INDEX / MATCH

下一節 →

7.4 查詢函式：XMATCH / XLOOKUP