相關主題
×
公式與函數
使用 Microsoft 登入
登入或建立帳戶。
您好:
Select a different 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 專家,並使用按鈕註冊免費課程

另請參閱

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

XMATCH 函數

Excel 函數 (按字母排序)

Excel 函數 (依類別排序)

需要更多協助?

擴展您的技能
探索訓練

這項資訊有幫助嗎?

您對語言品質的滿意度如何?
以下何者是您會在意的事項?

感謝您的意見反應!

×