Excel：XLOOKUP 函數功能

Excel：XLOOKUP 函數功能：

在使用 Excel 365 的過程中，發現了 XLOOKUP 的強大功能，它合併了VLOOKUP (垂直搜尋) 或 HLOOKUP (水平搜尋) 函數，能夠在工作上解決複雜的搜尋問題，使用 XLOOKUP 函數，在表格或範圍中依列尋找專案。使用 XLOOKUP，您可以在一欄中尋找搜尋字詞，並從另一欄的同一列傳回結果，無論傳回數據行位於哪一側。但 XLOOKUP 在 Excel 2016 以上的版本才有此函數。

XLOOKUP 函數功能

XLOOKUP 函數優點：
與傳統的 VLOOKUP 及 HLOOKUP 相比， XLOOKUP 有以下 8 個優點：

• 可以垂直或水平查找。不需要花心力挑選 VLOOKUP 或 HLOOKUP ，只要使用 XLOOKUP ，就可以進行垂直或是水平查找
• 【查找值】可以在任意欄列。不同於 VLOOKUP 要求【查找值】在最左欄；HLOOKUP 要求【查找值】在最上列，XLOOKUP 【查找值】可以在任意欄列
• 【查找模式】預設為【完全符合】
• 可以反方向進行查找
• 可以返回多個值
• 查找不到資料時，可以指定回傳值
• 可以 "多條件" 查找
• 更快的執行速度。不同於 VLOOKUP/HLOOKUP 【查找範圍】涵蓋整個表格， XLOOKUP 只查找/回傳我們指定的欄位，大幅增加運算效能

XLOOKUP 語法：
XLOOKUP 函數會搜尋範圍或陣列，然後傳回找到的第一個相符專案。 如果沒有相符專案，則 XLOOKUP 可以傳回最接近 (大約) 相符專案。
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

XLOOKUP 引數：

引數	描述	選擇
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函數基本搜尋用法：
例子 1.1； 使用 XLOOKUP 搜尋某個範圍中的牌子名，然後傳回型號。它包括 lookup_value (單元格 J3)、lookup_array (範圍 B3:B12)，以及 return_array (範圍 C3:C12) 自變數。
=XLOOKUP(J3,B3:B12,C3:C12,"Not Found",0)

XLOOKUP 函數基本搜尋用法

例子 1.2； 使用 XLOOKUP 搜尋某個範圍中的含有型號，然後傳回總銷售額。它包括 lookup_value (單元格 J3)、lookup_array (範圍 C3:C12)，以及 return_array (範圍 G3:G12) 自變數。
=XLOOKUP(J3,C3:C12,G3:G12)

XLOOKUP 函數基本搜尋用法

例子 1.3； 使用 XLOOKUP 搜尋某個範圍中的含有型號，然後傳回總銷售額。它包括 lookup_value (單元格 J3)、lookup_array (範圍 C3:C12)，以及 return_array (範圍 G3:G12) 自變數，從最後起搜尋。
=XLOOKUP(J3,C3:C12,G3:G12,"Not Found",0,-1)

XLOOKUP 函數基本搜尋用法

2. XLOOKUP 函數搭配萬用字元（Wildcard）搜尋用法：
例子 2.1； 使用 XLOOKUP 搜尋某個範圍中的含有型號 (使用萬能字 / Wildcard)，然後傳回銷售總計。它包括 lookup_value (單元格 J3)、lookup_array (範圍 C3:C12)，以及 return_array (範圍 H3:H12) 自變數。
=XLOOKUP("*"&J3&"*",C3:C12,H3:H12,"Not Found",2)

XLOOKUP 函數搭配萬用字元（Wildcard）搜尋用法

3. XLOOKUP 函數進行多條件搜尋用法：
例子 3.1： 使用 XLOOKUP 搜尋某個範圍中的含有型號和年份，然後傳回總銷售額。它包括 lookup_value (單元格 J3) 和(單元格 K3)、lookup_array (範圍 C3:C12) 和(範圍 A3:A12)，以及 return_array (範圍 H3:H12) 自變數。
=XLOOKUP(1,(C3:C12 =J3)*(A3:A12=K3),H3:H12)

XLOOKUP 函數進行多條件搜尋用法

4. XLOOKUP 函數二維度搜尋用法：
例子 4.1； 使用 XLOOKUP 搜尋某個範圍中的含有型號和季度，然後傳回Q4銷售額。它包括 lookup_value (單元格 J3) 和(單元格 K3)、 lookup_array (範圍 C3:C12) 和(範圍 D2:G2)，以及 return_array (範圍 H3:H12) 自變數。
=XLOOKUP(J3,C3:C12,XLOOKUP(K3,D2:G2,D3:G12))

XLOOKUP 函數二維度搜尋用法

5. XLOOKUP 函數搜尋後返回多個值用法：
例子 5.1； 使用 XLOOKUP 搜尋某個範圍中的含有型號，然後傳回Q1~Q4的銷售額和總銷售額。它包括 lookup_value (單元格 J3)、 lookup_array (範圍 C3:C12)，以及 return_array (範圍 D3:H12) 自變數。
=TRANSPOSE(XLOOKUP(J3,C3:C12,D3:H12,"NG"))

XLOOKUP 函數搜尋後返回多個值用法

例子 5.2； 使用 XLOOKUP 搜尋某個範圍中的含有型號，然後傳回 Q1~Q4 的銷售額和總銷售額。它包括 lookup_value (單元格 J3)、 lookup_array (範圍 C3:C12)，以及 return_array (範圍 D3:H12) 自變數，從最後起搜尋。
=TRANSPOSE(XLOOKUP(J3,C3:C12,D3:H12,"NG",0,-1))

XLOOKUP 函數搜尋後返回多個值用法

6. XLOOKUP 函數搜尋後FILTER 篩選用法：
例子 6.1； 使用 XLOOKUP 搜尋某個範圍中的含有型號，然後傳回 Q1~Q4 的銷售額和總銷售額。它包括 lookup_value (單元格 J3)、 lookup_array (範圍 C3:C12)，以及 return_array (範圍 D3:H12) 自變數，但過濾輸出的列數。
=XLOOKUP(J3,C3:C12,FILTER(D3:H12,{0,0,0,0,1}))

XLOOKUP 函數搜尋後FILTER 篩選用法

7. XLOOKUP 函數符合大小寫比對搜尋用法：
例子 7.1； 使用 XLOOKUP 搜尋某個範圍中的含有型號，然後傳回總銷售額。它包括 lookup_value (單元格 J3)、 lookup_array (範圍 C3:C12)，以及 return_array (範圍 D3:H12) 自變數，但搜尋要完全相同包括大小寫(case-sensitive)。
=XLOOKUP(TRUE, EXACT(J3,C3:C12),H3:H12, "NG")

XLOOKUP函數符合大小寫比對搜尋用法

※※ 在舊版的 Excel 中若要回傳陣列「{…}」，在公式輸入時需要使用 Ctrl + Shift +Enter 的輸入方式，會在公式最外圍自動加上一個「{…………}」。新版(Microsoft 365)則可以直接按 Enter 即可。