公式與函數

XLOOKUP

XLOOKUP 函數

使用 XLOOKUP 函數,在表格或範圍中按列尋找專案。 例如,依據零件編號來查看汽車零件的價格,或根據員工 ID 尋找員工姓名。 使用 XLOOKUP,您可以在一欄中尋找搜尋字詞,然後從另一欄中的同一列返回結果,無論返回欄位於哪一側。

您的瀏覽器不支援視訊。

語法

XLOOKUP 函數會搜尋範圍或陣列,然後返回對應到第一個相符專案的專案。 如果沒有相符專案存在,則 XLOOKUP 可以返回最接近 (大約) 相符專案。 

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

引數

說明

lookup_value

必填*

要搜尋的值

*如果省略,XLOOKUP 會返回在 lookup_array 中找到的空白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 在範圍中尋找國家/地區名稱,然後返回其電話國碼。 它包含儲存格F2 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 同時返回員工名稱和部門。

XLOOKUP 函數的範例,用來根據員工 IDt 來退回員工名稱和部門。 公式為:=XLOOKUP (B2,B5:B14,C5:D14,0,1)

———————————————————————————

範例 3    會新增if_not_found引數至上一個範例。

XLOOKUP 函數的範例,用來根據員工識別碼和 if_not_found 引數來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 中尋找 毛利 ,然後在表格頂端列 (範圍 C5:F5) 中尋找 Qtr1, 最後會回到兩個交集處的值。 這類似于同時使用 INDEX 和MATCH 函數。

提示: 您也可以使用 XLOOKUP 取代 HLOOKUP 函數。

XLOOKUP 函數的影像,用來以巢式方式將 2 個 XLOOKUUP 從表格返回水準資料。 公式為:=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 函數,加總兩個範圍之間的所有值。 在此例中,我們要加總蘋果、蘋果及包括梨子的值,這兩者介於兩者之間。

使用 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 社群取得支援。

另請參閱

XMATCH 函數

Excel 函數 (按字母排序)

Excel 函數 (依類別排序)

需要更多協助?

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

這項資訊有幫助嗎?

×