XLOOKUP 函數

當您需要在表格或範圍依列尋找專案時,請使用XLOOKUP函數。 例如,依元件編號查看汽車元件的價格,或根據員工識別碼尋找員工名稱。 有了 XLOOKUP,您就可以在一欄中尋找搜尋字詞,並傳回另一個資料列中相同資料列的結果,不論傳回資料行在哪一側。

使用 [公告] 來公告重大影響

附註: 目前通道中 Microsoft 365 訂閱者可以使用此函數。 從2020年7月開始,您可以在半年企業頻道中 Microsoft 365 訂閱者使用。 如需如何將功能推出至 Microsoft 365 訂閱者的詳細資訊,請參閱何時取得 Microsoft 365 的最新功能

XLOOKUP 函數搜尋範圍或陣列,並傳回對應至所找到的第一個相符專案。 如果不存在相符的專案,則 XLOOKUP 會傳回最接近(近似值)的相符專案。 

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) 

引數

說明

lookup_value

必要

查閱值

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 (range D2: D11)引數。 它不會包含 match_mode 引數,因為 XLOOKUP 預設為完全相符的值。

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

附註: XLOOKUP 與 VLOOKUP 的不同之處是,它會使用個別的查閱並傳回陣列,其中 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)

附註: 與 VLOOKUP 不同的是,lookup_array 資料行位於 return_array 資料行的右側,而 VLOOKUP 只能從左至右看起來。

範例 5

接下來,我們將使用嵌套的 XLOOKUP 函數來執行垂直和水準相符。 在這種情況下,它會先在 B 欄中尋找毛利潤,然後在表格的最上方列中尋找 [數季度] (範圍 C5: F5),並傳回兩者交集的值。 這與搭配使用INDEXMATCH函數的方式類似。 您也可以使用 XLOOKUP 來取代HLOOKUP函數。

XLOOKUP 函數的圖像,它是透過嵌套 2 XLOOKUPs,從資料表傳回水準資料。 公式是: = 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 函數嵌套在一起,以加總兩個範圍之間所有的值。 在這種情況下,我們會將 grapes、香蕉和 include "梨子" 的值加總,這兩者之間是兩個。

使用 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 技術社群中的專家、在 Answers 社群取得支援,或是在 Excel User Voice 上建議新功能或增強功能。

另請參閱

XMATCH 函數

Excel 函數 (按字母排序)

Excel 函數 (依類別)

附註:  本頁面是經由自動翻譯而成,因此文中可能有文法錯誤或不準確之處。 讓這些內容對您有所幫助是我們的目的。 告訴我們這項資訊是否有幫助? 這裡是供您參考的英文文章

增進您的 Office 技巧
探索訓練
優先取得新功能
加入 Office 測試人員

這項資訊有幫助嗎?

感謝您的意見反應!

感謝您的意見反應! 我們將協助您與我們的其中一個 Office 支援專員連絡以深入了解您的意見。

×