公式與函數

XLOOKUP

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

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

您的瀏覽器不支援影片。 請安裝 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 技術社群中的專家,或在社群中取得支援。

XMATCH 函數

Excel 函數 (按字母排序)

Excel 函數 (依類別排序)

需要更多協助嗎?

想要其他選項嗎?

探索訂閱權益、瀏覽訓練課程、瞭解如何保護您的裝置等等。

社群可協助您詢問並回答問題、提供意見反應,以及聆聽來自具有豐富知識的專家意見。