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 函數 (依類別)

免費在網路上開始使用 Excel

語法

範例

另請參閱

需要更多協助嗎?

想要其他選項嗎?

這項資訊有幫助嗎?

感謝您的意見反應!