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

語法

範例

另請參閱

需要更多協助嗎?

想要其他選項嗎?

這項資訊有幫助嗎?

感謝您的意見反應!