Excel XLOOKUP 函數 | 基本操作說明及範例 - BH Notes

Excel XLOOKUP 函數 | 基本操作說明及範例

xlookup-usage

當我們需要查找資料時,最常使用的是 VLOOKUP(垂直查找) 或 HLOOKUP(水平查找) 函數。自 OFFICE 365 開始, Excel 新增查找函數 XLOOKUP ,其強大的功能可以取代 VLOOKUP 及 HLOOKUP 函數。本文說明 XLOOKUP 函數的基本操作。 另外可以參考進階操作複雜操作

XLOOKUP 函數優點

與傳統的 VLOOKUP 及 HLOOKUP 相比, XLOOKUP 有以下 8 個優點:

  1. 可以垂直或水平查找。不需要花心力挑選 VLOOKUP 或 HLOOKUP ,只要使用 XLOOKUP ,就可以進行垂直或是水平查找
  2. 【查找值】可以在任意欄列。不同於 VLOOKUP 要求【查找值】在最左欄;HLOOKUP 要求【查找值】在最上列,XLOOKUP 【查找值】可以在任意欄列
  3. 【查找模式】預設為【完全符合】
  4. 可以反方向進行查找
  5. 可以返回多個值
  6. 查找不到資料時,可以指定回傳值
  7. 可以”多條件”查找
  8. 更快的執行速度。不同於 VLOOKUP/HLOOKUP 【查找範圍】涵蓋整個表格, XLOOKUP 只查找/回傳我們指定的欄位,大幅增加運算效能

語法說明

XLOOKUP ( lookup_value, lookup_array, return_array, [ if_not_found ], [ match_mode ], [ search_mode ] )

XLOOKUP ( 查找值, 查找範圍, 回傳值範圍, [ 查照不到的回傳值 ], [ 相符模式 ], [ 查找模式 ] )

備註: [ if_not_found ], [ match_mode ], [ search_mode ] 為選填引數,其餘皆為必填引數

  • lookup_value 查找值:這是必填的引數,是我們要查找的資料值
  • lookup_array 查找範圍:這是必填的引數,是我們要查找的資料範圍
  • return_array 回傳值範圍:這是必填的引數,是我們要回傳的資料範圍
  • [ if_not_found ] [ 查照不到的回傳值]:這是選填的引數,如果查找不到資料時,會回傳我們指定的值。預設回傳 #N/A 錯誤
  • [ match_mode ] [ 相符模式 ]:這是選填的引數,有下列 4 種模式,預設為 0:
    • 0:完全符合,如果查找不到,回傳 #N/A 錯誤
    • -1:完全符合,如果查找不到,回傳下一個較小的值
    • 1:完全符合,如果查找不到,回傳下一個較大的值
    • 2:使用萬用字元。可參考 Microsoft 說明
  • [ search_mode ] [ 查找模式 ]:這是選填的引數,有下列 4 種模式,預設為 1:
    • 1:從第一筆資料開始查找
    • -1:從最後一筆資料開始查找
    • 2:二分搜尋法(升序)
    • -2:二分搜尋法(降序)

基本操作範例

XLOOKUP 的操作彈性,本文介紹基本的操作方式,我們以”大學通訊”資料作為範例,練習查找大學的總機號碼

步驟 1 | 輸入 XLOOKUP 函數

我們要在儲存格 J2 回傳指定大學的電話號碼,因此在 J2 輸入 =XLOOKUP() ,接著依序輸入下列引數

xlookup_function

1-1 | 輸入【查找值】

這是第 1 個也是最主要的引數。由於我們要查找學校名稱,【查找值】我們輸入儲存格 I2

xlookup_lookup_value

1-2 | 輸入【查找範圍】

在範例中,我們的【查找範圍】是 C2: C7。因為還要將函數套用到其他儲存格,我們將【查找範圍】設定為絕對位址 $C$2: $C$7

xlookup_lookup_array

1-3 | 輸入【回傳值範圍】

在範例中,我們的【回傳值範圍】是 G2: G7。因為還要將函數套用到其他儲存格,將【回傳值範圍】設定為絕對位址 $G$2: $G$7

xlookup_return_array

步驟 2 | 拖曳函數,完成所有查找

滑鼠移動到 J2 儲存格的右下角,出現十字圖案時點擊拖曳,向下填滿函數,完成所有查找

xlookup_fillup

XLOOKUP 函數特點

忽略大小寫(case-insensitive)

XLOOKUP 函數查找資料時會忽略大小寫,如 “APPLE” 及 “apple” 會視為相同值

xlookup_case_insensitive

重複資料僅回傳第 1 筆

當資料表格有重複資料時,XLOOKUP 函數只會回傳查找到的第 1 筆資料

xlookup_multiple_value

常見的 XLOOKUP 函數回傳錯誤

#N/A 錯誤

如果【查找範圍】內沒有【查找值】,XLOOKUP 函數會回傳 #N/A 錯誤

xlookup_na

#VALUE! 錯誤

當我們查找垂直欄位,卻要回傳水平欄位的資料, XLOOKUP 函數會回傳 #VAULE 錯誤

#REF! 錯誤

當我們在兩個不同的 Excel 檔案查找資料,並且其中一個 Excel 檔案關閉時, XLOOKUP 函數會回傳 #REF 錯誤。要解決 #REF 錯誤,只要重新開啟 Excel 檔案就可以


如果本篇文章有幫助到你,請在下方拍手圖示按 5 下。只要花幾秒鐘登入 Google 或 FB 帳號,不需任何花費就能提供我實質的回饋,支持我繼續創作,謝謝

發表迴響