XLOOKUP 函數

使用 XLOOKUP 函數依列尋找表格或範圍中的物件。例如，按零件編號查找汽車零件的價格，或根據員工 ID 查找員工姓名。使用 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)

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

範例 2 會根據員工 ID 號碼來查閱員工資訊。與 VLOOKUP 不同，XLOOKUP 可以傳回包含多個專案的陣列，因此單一公式可以從儲存格 C5：D14 傳回員工姓名和部門。

用來根據員工 IDt 傳回員工名稱和部門的 XLOOKUP 函數範例。公式為：=XLOOKUP (B2，B5：B14，C5：D14,0,1)

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

範例 3 將 if_not_found 引數新增至上述範例。

XLOOKUP 函數的範例，用於根據具有 if_not_found 引數的員工 ID 傳回員工名稱和部門。公式為 =XLOOKUP (B2，B5：B14，C5：D14,0,1，“找不到員工”)

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

範例 4 在 C 欄中尋找在儲存格 E2 中輸入的個人收入，並在 B 欄中找到相符的稅率。它會將 if_not_found 引數設定為傳回 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 欄中查找毛利潤，然後在表格 (範圍 C5：F5) 的頂行中查找 Qtr1 ，最後返回兩者交集處的值。這類似於同時使用 INDEX 和 MATCH 函數。

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

用於透過巢狀 2 個 XLOOKUP 從表格傳回水平資料的 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公式的儲存格，然後選取 [公式] > [公式稽核] > [評估公式]，然後選取 [評估] 以逐步執行計算，以自行查看其運作方式。

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

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

另請參閱

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

XMATCH 函數

Excel 函數 (按字母排序)

Excel 函數 (依類別排序)

語法

範例

另請參閱

Need more help?

Want more options?

Was this information helpful?

Thank you for your feedback!