XLOOKUP 函數

使用 XLOOKUP 功能可以依列查找表格或範圍中的項目。例如，透過零件編號查詢汽車零件的價格，或根據員工編號找到員工姓名。使用 XLOOKUP，你可以在一欄尋找搜尋詞，然後從同一列的結果回傳到另一欄，無論返回欄在哪一側。

附註: XLOOKUP 在 Excel 2016 和 Excel 2019 中無法使用。不過，你可能會遇到在 Excel 2016 或 Excel 2019 中使用帶有 XLOOKUP 函數的工作簿，如果是別人使用較新版本的 Excel 製作的。

您的瀏覽器不支援影片。請安裝 Microsoft Silverlight、Adobe Flash Player 或 Internet Explorer 9。

語法

XLOOKUP 函式會搜尋一個範圍或陣列，然後回傳對應第一個匹配的項目。若不匹配，XLOOKUP 可回傳最接近 (近似) 匹配。

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

引數	描述
lookup_value 必備*	要搜尋的價值若省略，XLOOKUP 會回傳 lookup_array* 中找到的空白儲存格。
lookup_array 必要	要搜尋的陣列或範圍
return_array 必要	要回傳的陣列或範圍
[if_not_found] 選擇性	若未找到有效匹配，請回傳您提供的[if_not_found]文字。若未找到有效匹配且缺少[if_not_found]，則返回 #N/A 。
[match_mode] 選擇性	指定相符類型： 0 - 完全吻合。如果找不到，請 #N/A返回。這是預設值。 -1 - 完全吻合。如果找不到，則退還下一個較小的物品。 1 - 完全吻合。如果找不到，則退回下一個較大的物品。 2: 萬用字元比對，其中 *、？和 ~ 具有特殊意義。
[search_mode] 選擇性	請指定使用以下搜尋模式： 1 - 從第一個物品開始搜尋。這是預設值。 -1 - 從最後一項開始進行反向搜尋。 2 - 執行依賴 lookup_array 排序的二分搜尋。如果未排序，將會傳回無效結果。 -2: 執行二進位搜尋，依賴 lookup_array 以遞減順序排序搜尋。如果未排序，將會傳回無效結果。

引數

描述

lookup_value

必備*

要搜尋的價值 *若省略，XLOOKUP 會回傳 lookup_array 中找到的空白儲存格。

lookup_array

必要

要搜尋的陣列或範圍

return_array

必要

要回傳的陣列或範圍

[if_not_found]

選擇性

若未找到有效匹配，請回傳您提供的[if_not_found]文字。

若未找到有效匹配且缺少[if_not_found]，則返回 #N/A 。

[match_mode]

選擇性

指定相符類型：

0 - 完全吻合。如果找不到，請 #N/A返回。這是預設值。

-1 - 完全吻合。如果找不到，則退還下一個較小的物品。

1 - 完全吻合。如果找不到，則退回下一個較大的物品。

2: 萬用字元比對，其中 *、？和 ~ 具有特殊意義。

[search_mode]

選擇性

請指定使用以下搜尋模式：

1 - 從第一個物品開始搜尋。這是預設值。

-1 - 從最後一項開始進行反向搜尋。

2 - 執行依賴 lookup_array 排序的二分搜尋。如果未排序，將會傳回無效結果。

-2: 執行二進位搜尋，依賴 lookup_array 以遞減順序排序搜尋。如果未排序，將會傳回無效結果。

範例

範例 1 使用 XLOOKUP 在範圍內查詢國家名稱，然後回傳其電話國家代碼。它包含 lookup_value (單元 F2) 、 lookup_array (範圍 B2：B11) ，以及 return_array (範圍 D2：D11) 參數。它沒有包含 match_mode 參數，因為 XLOOKUP 預設會產生完全吻合的參數。

附註: XLOOKUP 使用查找陣列和回傳陣列，而 VLOOKUP 則使用單一資料表陣列，後接欄位索引號。此情況下等價的 VLOOKUP 公式為： =VLOOKUP (F2，B2：D11,3，FALSE)

———————————————————————————

範例二 是根據員工ID號碼查詢員工資訊。與 VLOOKUP 不同，XLOOKUP 可以回傳包含多個項目的陣列，因此一個公式可以同時回傳員工名稱和部門，這些資料來自 C5：D14 的儲存格。

XLOOKUP 函式範例，用於根據員工 ID 返回員工姓名與部門。公式為：=XLOOKUP (B2，B5：B14，C5：D14,0,1)

———————————————————————————

範例3 在前例上加入了一個if_not_found 論證。

XLOOKUP 函式範例，用於根據員工 ID 回傳員工姓名與部門，並搭配 if_not_found 參數。公式為 =XLOOKUP (B2，B5：B14，C5：D14,0,1，「找不到員工」)

———————————————————————————

範例4 在C欄尋找E2格輸入的個人所得，並在B欄找到相符的稅率。若找不到 if_not_found 參數，則回傳 0 (0) 。 match_mode參數設為 1，表示函式會尋找完全匹配的參數，若找不到，則回傳下一個較大的項目。最後， search_mode 參數設為 1，表示函式會從第一個項目搜尋到最後一個。

XLOOKUP 函數的影像，用於回傳基於最大收入的稅率。這是近似匹配。公式為：=XLOOKUP (E2，C2：C7，B2：B7,1,1)

附註: XARRAY 的 lookup_array 欄位於 return_array 欄的右側，而 VLOOKUP 只能從左到右查看。

———————————————————————————

範例 5 使用巢狀的 XLOOKUP 函式來執行垂直與水平匹配。它首先在 B 欄尋找毛利，接著在表格頂列尋找 Qtr1 ， (範圍為 C5：F5) ，最後回傳兩者交點的值。這類似於同時使用索引與匹配函式。

提示: 你也可以用 XLOOKUP 來取代 HLOOKUP 函式。

XLOOKUP 函式的圖片，用於透過巢狀 2 個 XLOOKUP 從資料表回傳水平資料。公式為：=XLOOKUP (D2，$B 6：$B 17，XLOOKUP ($C 3，$C 5：$G 5，$C 6：$G 17) )

附註: 格子 D3：F3 的公式為： =XLOOKUP (D2，$B 6：$B 17，XLOOKUP ($C 3，$C 5：$G 5，$C 6：$G 17) ) 。

———————————————————————————

範例 6 使用 SUM 函數及兩個巢狀的 XLOOKUP 函式，將兩個範圍間的所有值相加。在這種情況下，我們要加總葡萄、香蕉的數值，並包含梨子，這兩者介於兩者之間。

使用 XLOOKUP 搭配 SUM，將介於兩個選擇之間的數值加總

E3 儲存格的公式為：=SUM (XLOOKUP (B3，B6：B10，E6：E10) ：XLOOKUP (C3，B6：B10，E6：E10) )

運作方式為何？ XLOOKUP 回傳一個範圍，因此計算時，公式結果如下： =SUM ($E$7：$E$9) 。你可以自己看看這個方法，方法是選擇一個帶有類似 XLOOKUP 公式的儲存格，然後選擇 Formula> Formula Auditing > Evaluate Formula，最後選擇 Evaluate 進行計算。

附註: 感謝 Microsoft Excel MVP Bill Jelen 提出這個範例。

———————————————————————————

另請參閱

您可以隨時詢問 Excel 技術社群中的專家，或在社群中取得支援。

XMATCH 函數

Excel 函數 (按字母排序)

Excel 函數 (依類別排序)

語法

範例

另請參閱

Need more help?

Want more options?

Was this information helpful?

Thank you for your feedback!