Related topics
×
公式與函數
Sign in with Microsoft
Sign in or create an account.
公式與函數

XLOOKUP

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

附註: 在 Excel 2016 和 Excel 2019 中無法使用 XLOOKUP,不過,您可能會遇到在 Excel 2016 或 Excel 2019 中使用由其他人使用較新版 Excel 所建立之 XLOOKUP 函數在 Excel 2016 或 Excel 2019 中使用活頁簿的情況。

您的瀏覽器不支援視訊。 請安裝 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以 遞減 順序排序的二進位搜尋。 如果未排序,將會傳回不正確結果。

範例

範例 1   使用 XLOOKUP 查詢範圍中的國家/地區名稱,然後傳回其電話國碼。 它包括 lookup_value (儲存格 F2) 、 lookup_array ( 範圍 B2:B11) ,以及 return_array (範圍 D2:D11) 引數。 它不包含 match_mode 引數,因為 XLOOKUP 根據預設會產生完全符合的專案。

用來根據員工識別碼傳回員工名稱和部門的 XLOOKUP 函數範例。 公式為 =XLOOKUP (B2,B5:B14,C5:C14) 。

附註: 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) ,最後傳回兩者交集處的值。 這類似于同時使用 INDEXMATCH 函數。

提示: 您也可以使用 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 技術社群中的專家,或是在 Answers 社群取得支援。

在 Excel 上取得即時和免費解答

另請參閱

XMATCH 函數

Excel 函數 (按字母排序)

Excel 函數 (依類別排序)

Need more help?

Expand your skills
Explore Training
Get new features first
Join Microsoft Office Insiders

Was this information helpful?

How satisfied are you with the translation quality?
What affected your experience?

Thank you for your feedback!

×