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