目錄
×
公式與函數
公式與函數

XLOOKUP

使用 XLOOKUP 函數來按列尋找資料表或範圍中的專案。 例如,以零件編號來查看汽車零件的價格,或根據員工識別碼尋找員工名稱。 使用 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] 文字。

如果找不到有效的相符專案,且 [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 使用尋找陣列和 return 陣列,而 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 中尋找毛利,然後在資料表的頂端列尋找Qtr1 (範圍 C5:F5) ,最後會回到兩者交集處的值。 這類似于同時 使用 INDEXMATCH 函數。

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

XLOOKUP 函數的影像,用來將 2 個 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 函數,以加總兩個範圍之間的所有值。 在此例中,我們要加總兩者之間的蘋果、香蕉和梨的數值。

使用 XLOOKUP 與 SUM 來合計落在兩個選取範圍之間的值範圍

儲存格 E3 中的公式為:=SUM (XLOOKUP (B3,B6:B10,E6:E10) :XLOOKUP (C3,B6:B10,E6:E10) )

它的運作方式為何? XLOOKUP 會返回範圍,因此當計算結果時,公式看起來會像這樣:=SUM ($E$7:$E $9) 。 您可以選取具有類似此公式的 XLOOKUP 公式的儲存格,然後選取公式>公式稽核>評估公式,然後選取評估以逐步完成計算,即可自行查看其運作方式。

附註: 感謝 MVP Microsoft Excel Bill Jelen建議此範例。

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

需要更多協助嗎?

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

另請參閱

XMATCH 函數

Excel 函數 (依英文字母順序排列)

Excel 函數 (依類別排序)

需要更多協助?

擴展您的技能
探索訓練
優先取得新功能
加入 Microsoft 測試人員

這項資訊有幫助嗎?

您對翻譯品質的滿意度為何?

會影響您使用體驗的因素為何?

是否還有其他的意見反應? (選填)

感謝您的意見反應!

×