XLOOKUP 函數

使用 XLOOKUP 函數，在表格或範圍中依列尋找專案。例如，依據零件編號查詢汽車零件的價格，或根據員工標識元尋找員工名稱。使用 XLOOKUP，您可以在一欄中尋找搜尋字詞，並從另一欄的同一列傳回結果，無論傳回數據行位於哪一側。

附註: Excel 2016 和 Excel 2019 不提供 XLOOKUP。不過，您可能會遇到在 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 會根據員工標識碼來尋找員工資訊。與 VLOOKUP 不同，XLOOKUP 可以傳回含有多個項目的陣列，因此單一公式可以從單元格 C5：D14 傳回員工名稱和部門。

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

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

範例 3 會將if_not_found 自變數新增至上一個範例。

用來傳回員工名稱和部門的 XLOOKUP 函數範例，其依據員工標識碼與if_not_found自變數。公式為 =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 中尋找 Gross Profit ，然後在表格的第一列中尋找 Qtr1 (範圍 C5：F5) ，最後傳回兩者交集處的值。這類似於同時使用 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 函數加總兩個範圍之間的所有值。在此情況下，我們想要加總兩者之間的葡萄、香蕉及梨子的值。

搭配 SUM 使用 XLOOKUP 加總介於兩個選取範圍之間的值範圍

單元格 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!