如何在 Excel 中使用全新進階 XLOOKUP 函數（10 個範例）
Excel 全新 XLOOKUP 函數，是目前最強大且最簡單的查詢函數！Microsoft 經過不斷努力，終於推出此函數，全面取代 VLOOKUP、HLOOKUP、INDEX+MATCH 等傳統查詢函數。
在本教學中，我們將帶您深入了解 XLOOKUP 的強大優勢，並示範如何取得與靈活運用它，輕鬆解決各種查詢難題。
如何取得 XLOOKUP？
由於 XLOOKUP 函數僅適用於 Microsoft 365 Excel、Excel 2021 及更高版本，以及 Excel 網頁版，若您使用的是 Excel 2019 或更早版本，建議升級以暢享 XLOOKUP 的強大功能。
語法
此函數會在指定範圍或陣列中搜尋，並傳回第一個相符項目的值。其語法如下：
引數：
- Lookup_value (required)：您要尋找的值，可位於 table_array 範圍內的任意欄位。
- Lookup_array (required)您用來搜尋查詢值的陣列或範圍。
- Return_array (required)您希望從中提取數值的陣列或範圍。
- If_not_found (optional)：當找不到有效相符項目時，可自訂要返回的值。您可在 【if_not_found】 中設定提示文字，以顯示無相符項目；
否則，系統將預設返回 #N/A 錯誤值。 - Match_mode (optional)在此處可指定 lookup_value 與 lookup_array 中的值之間的比對方式。
- 0（預設）＝ 完全符合；若未找到相符項目，則傳回 #N/A。
- ——1 ＝ 完全符合。若未找到相符項目，則傳回下一個較小的值。
- 1 ＝ 完全符合；若無相符項目，則傳回下一個較大的值。
- 2 ＝ 部分比對。運用萬用字元（例如 *、？ 與 ~）進行萬用字元比對。
- Search_mode (optional)在此處可指定執行搜尋的順序。
- 1（預設）＝ 從 lookup_array 的第一個項目開始，依序搜尋至最後一個項目。
- ——1 ＝ 從 lookup_array 的最後一個項目開始，反向搜尋至第一個項目。當您需要取得 lookup_array 中最後一個相符結果時，此選項非常實用。
- 2 ＝ 執行二元搜尋，要求 lookup_array 必須依遞增順序排序；若未排序，將導致結果無效。
- ——2 ＝ 執行二元搜尋，要求 lookup_array 必須依遞減順序排序；若未排序，則返回的結果將無效。
如需有關引數的詳細資訊，請依照下列步驟操作：
1. 請在空白儲存格中輸入下列語法，並注意僅需輸入括號的其中一側。
2. 按下 Ctrl+A，隨即彈出提示方塊顯示函數引數，並自動完成括號的另一側。
3. 向下展開資料面板，即可查看 XLOOKUP 的全部六個函數引數。
 | >>> |  |
範例
相信您已掌握 XLOOKUP 的基本原理，現在就讓我們立刻進入實用範例！
範例 1：完全符合
您是否曾因每次使用 VLOOKUP 時都必須手動指定完全相符模式而感到困擾？好消息是，當您改用強大的 XLOOKUP 函數時，這種麻煩將不復存在——XLOOKUP 預設即採用完全相符模式。
假設您目前有一份辦公用品庫存清單，想查詢某項商品（例如滑鼠）的單價，請依照下列步驟操作。
在空白儲存格 F2 中輸入下列公式，然後按下 ENTER 鍵即可取得結果。
=XLOOKUP(E2,A2:A10,C2:C10)
現在您已透過進階的 XLOOKUP 公式輕鬆取得滑鼠的單價。由於比對模式預設為完全相符，無需額外設定，比起 VLOOKUP 更簡單、更高效！
或許您目前使用的是較舊版本的 Excel，且暫無計畫升級至 Excel 2021 或 Microsoft 365. 在此情況下，我將為您推薦一項超實用功能——「尋找值」。無需複雜公式或 XLOOKUP，即可輕鬆取得結果！
1. 點擊您希望用來顯示比對結果的儲存格。
2. 前往「Kutools」選項卡，點擊「公式助手」，再從下拉清單中選取「公式助手」。
3. 在公式助手對話方塊中，請按以下方式進行設定：
- 在「公式類型」區段中選取「Lookup」；
- 在「選擇一個公式」區段中，選取「在區域中查找資料」；
- 在「參數輸入」區段中，請執行下列操作：
- 在「Table_array」方塊中，選取包含查詢值與結果值的數據區域；
- 在「Lookup_value」方塊中，選取您要搜尋的值所在的儲存格或範圍。請注意，該值必須位於 table_array 的第一欄中。
- 在「Column」方塊中，選取您希望從中返回相符值的欄位。
4. 點擊「確定」按鈕，立即取得結果。
Kutools for Excel－透過超過 300 項必備工具全面強化 Excel，讓您的工作更快速、更輕鬆，並運用 AI 功能實現更聰明的資料處理與高效生產力！立即取得
範例 2：近似比對
若要執行近似查詢，請將第五個引數的比對模式設為 1 或 ——1；當找不到完全相符的值時，系統會自動傳回下一個較大或較小的值。
在此案例中，您需要找出員工薪資所對應的稅率。試算表左側列出了 2021 年度聯邦所得稅級距。如何快速取得 E 欄中每位員工適用的稅率呢？別擔心，請依照下列步驟操作：
1. 在空白儲存格 E2 中輸入下列公式，按下 ENTER 鍵即可取得結果。
接著，可依需求調整傳回結果的格式。
=XLOOKUP(D2,B2:B8,A2:A8,,1)
 | >>> |  |
√ 注意：第四個參數［未找到時］為選用，因此在此予以省略。
2. 現在您已得知 D2 儲存格的稅率。若要取得其餘結果，請將 lookup_array 與 return_array 的儲存格參照轉換為絕對參照。
- 雙擊儲存格 E2 以顯示公式 =XLOOKUP(D2，B2:B8，A2:A8，，1）；
- 在公式中選取查詢範圍 B2:B8，按下 F4 鍵以取得 $B$2：$B$8；
- 在公式中選取返回範圍 A2:A8，按下 F4 鍵以取得 $A$2：$A$8；
- 按下 Enter 鍵，即可取得儲存格 E2 的結果。
 | >>> |  |
3. 接著向下拖曳填滿控點，即可取得所有結果。
√ 注意：
- 按下鍵盤上的 F4 鍵，即可在列號與欄標前自動加上錢號（$），將儲存格參照快速轉換為絕對參照。
- 對查詢與返回範圍套用絕對參照後，我們已將儲存格 E2 中的公式變更為此版本：
=XLOOKUP(D2,$B$2:$B$8,$A$2:$A$8,,1)
- 當您從儲存格 E2 向下拖曳填滿控點時，E 欄各儲存格中的公式僅會在 lookup_value 部分自動調整。
例如，E13 中的公式現在變為：
=XLOOKUP(D13,$B$2:$B$8,$A$2:$A$8,,1)
範例 3：萬用字元比對
在我們探討 XLOOKUP 的萬用字元比對功能之前，先來了解什麼是萬用字元。
在 Microsoft Excel 中，萬用字元是一種特殊字元，能用來批量替換字串，特別適用於執行部分比對查詢時。
萬用字元共有三種：星號（*）、問號（？）與波狀符號（~）。
- 星號（*） 代表文字中的任意數量字元；
- 問號（？） 代表文字中的任意單一字元；
- 波狀符號（~）可用來將萬用字元（*、？、~）轉換為一般字元；只需在萬用字元前加上波狀符號（~）即可達成此功能。
在大多數情況下，執行 XLOOKUP 的萬用字元比對功能時，我們會使用星號（*）字元。現在，就讓我們來看看萬用字元比對是如何運作的！
假設您手邊有一份「美國前 50 大企業市值清單」，想查詢其中幾家公司的市值，但僅有公司簡稱——這正是萬用字元比對的絕佳應用情境！請依照以下步驟輕鬆完成操作。
√ 注意：若要執行萬用字元比對，關鍵在於將第五個參數 [match_mode] 設為 2.
1. 在空白儲存格 H3 中輸入下列公式，然後按下 ENTER 鍵即可取得結果。
=XLOOKUP("*"&G3&"*",B3:B52,D3:D52,,2)
 | >>> |  |
2. 現在您已得知 H3 儲存格的結果。若要取得其餘結果，請將游標置於陣列中並按下 F4 鍵，以固定 lookup_array 與 return_array。此時，H3 中的公式將變為：
=XLOOKUP("*"&G3&"*",$B$3:$B$52,$D$3:$D$52,,2)
3. 向下拖曳填滿控點，即可取得所有結果。
√ 注意：
- 儲存格 H3 公式中的 lookup_value 為 “*“&G3&“*“，我們運用 & 符號將星號萬用字元（*）與 G3 的值串接起來。
- 第四個參數 【If_not_found】 為選用項目，因此我直接省略。
範例 4：向左查詢
VLOOKUP 的其中一個缺點是只能查詢查詢欄右側的資料。若您嘗試查詢查詢欄左側的值，將會得到 #N/A 錯誤。別擔心，XLOOKUP 正是解決此問題的完美查詢函數！
XLOOKUP 的設計可自由對查詢欄左側或右側的區域進行檢索，毫無限制，完全契合 Excel 使用者的需求。以下範例將展示這項實用技巧。
假設您手邊有一份包含各國及其電話代碼的清單，想透過已知的電話代碼快速找出對應的國家名稱。
我們需要查詢 C 欄，並傳回 A 欄中的對應值。請依照下列步驟操作：
1. 在空白儲存格 G2 中輸入下列公式。
=XLOOKUP(F2,C2:C11,A2:A11)
2. 按下 ENTER 鍵，立即取得結果。
√ 注意：XLOOKUP 的向左查詢功能可取代 INDEX 與 MATCH 函數，用來查找查詢欄位左側的值。
對於不想記憶公式的使用者，我在此推薦一項超實用功能——「從右到左查找」！啟用此功能，您只需幾秒就能輕鬆完成從右向左的查詢。
1. 前往 Excel 中的「Kutools」選項卡，點選下拉清單中的「從右到左查找」，即可使用進階 LOOKUP 功能。
2. 在「從右到左查找」對話方塊中，請依下列方式進行設定：
- 在「待檢索值與輸出結果區域」區段中，指定查詢範圍與列表放置區域；
- 在「數據區域」區段中，輸入數據區域，然後指定「關鍵列」與「返回列」；
3. 點擊「確定」按鈕，立即取得結果。
Kutools for Excel－透過超過 300 項必備工具全面強化 Excel，讓您的工作更快速、更輕鬆，並運用 AI 功能實現更聰明的資料處理與高效生產力！立即取得
範例 5：垂直或水平查詢
身為 Excel 使用者，您應該熟悉 VLOOKUP 與 HLOOKUP 函數：VLOOKUP 用於在欄位中垂直查詢資料，而 HLOOKUP 則用於在列中水平查找。
現在，全新的 XLOOKUP 結合了兩者優點，讓您只需一套語法，就能輕鬆執行垂直或水平查詢——是不是很聰明？
以下範例將示範如何僅透過單一 XLOOKUP 函數，輕鬆執行垂直或水平查詢。
若要執行垂直查詢，請在空白儲存格 E2 中輸入下列公式，然後按下 ENTER 鍵以取得結果。
=XLOOKUP(E1,A2:A13,B2:B13)
若要執行水平查詢，請在空白儲存格 P2 中輸入下列公式，然後按下 ENTER 鍵以取得結果。
=XLOOKUP(P1,B1:M1,B2:M2)
如您所見，兩者的語法完全相同，唯一的差別在於：垂直查詢輸入的是欄位，而水平查詢輸入的是列。
範例 6：雙向查詢
您是否還在用 INDEX 與 MATCH 函數來查詢二維表格中的資料？立即試用升級版的 XLOOKUP，讓工作更輕鬆高效！
XLOOKUP 可執行雙重查詢，精準找出兩個值的交集。透過將一個 XLOOKUP 巢狀於另一個 XLOOKUP 內部，內部的 XLOOKUP 會傳回一整列或一整欄，並作為外部 XLOOKUP 的傳回陣列。
假設您有一份學生各科成績清單，想查詢 Kim 的化學成績。
讓我們看看如何運用強大的 XLOOKUP 輕鬆完成這項操作。
- 我們運用「內部」XLOOKUP 函數一次取得整列的回傳值：XLOOKUP(H2，B1:E1，B2:E10) 即可取得化學成績的對應範圍。
- 我們將「內部」XLOOKUP 巢狀於「外部」XLOOKUP 中，並以該「內部」XLOOKUP 作為完整公式中的 return_array。
- 最終公式如下：
=XLOOKUP(H1,A2:A10,XLOOKUP(H2,B1:E1,B2:E10))
- 在空白儲存格 H3 中輸入上述公式，然後按下 Enter 鍵即可取得結果。
或者，您也可以反向操作：先用「內部」XLOOKUP 在整行中找出 Kim 各科的成績作為返回值，再透過「外部」XLOOKUP 從這些科目成績中精準提取化學成績。
- 在空白儲存格 H4 中輸入下列公式，然後按下 Enter 鍵即可取得結果。
=XLOOKUP(H2,B1:E1,XLOOKUP(H1,A2:A10,B2:E10))
XLOOKUP 的雙向查詢功能，完美展現其垂直與水平查詢的強大實力。快親自試試看吧！
範例 7：自訂「找不到」訊息
和其他查詢函數一樣，當找不到相符項目時，會傳回 #N/A 錯誤訊息，這可能讓部分 Excel 使用者感到困惑。但好消息是，XLOOKUP 函數在第四個引數中內建了錯誤處理功能，讓您能輕鬆掌控錯誤顯示方式。
透過內建的 【if_not_found】 引數，您可自訂取代 #N/A 結果的提示訊息。只需在第四個選用引數中輸入所需文字，並以雙引號（“）括起來即可。
例如，當城市「Denver」找不到時，XLOOKUP 會傳回 #N/A 錯誤訊息；但若將第四個引數自訂為文字「No Match」，公式便會顯示「No Match」，而非錯誤訊息。
請在空白儲存格 F3 中輸入下列公式，然後按下 Enter 鍵以取得結果。
=XLOOKUP(E2,A2:A11,C2:C11,"No Match")
若想快速將 #N/A 錯誤替換為自訂訊息，Kutools for Excel 是 Excel 中的理想選擇！透過內建的「將 0 或 #N/A 替換為空白或特定值」功能，無需複雜公式或使用 XLOOKUP，即可輕鬆設定「找不到」時的顯示訊息。
1. 請切換至 Excel 中的「Kutools」選項卡，點選「高級 LOOKUP」下拉選單中的「將 0 或 #N/A 替換為空白或特定值」。
2. 在「將 0 或 #N/A 替換為空白或特定值」對話方塊中，請按以下方式進行設定：
- 在「待檢索值與輸出結果區域」區段中，選取查詢範圍與列表放置區域；
- 接著選取「以特定值取代 0 或 #N/A」選項，並輸入您想要的文字；
- 在「數據區域」區段中，選取數據範圍，並指定「關鍵列」與「返回列」。
3. 點選「確定」按鈕以取得結果；若找不到相符項目，系統將顯示自訂訊息。
Kutools for Excel－透過超過 300 項必備工具全面強化 Excel，讓您的工作更快速、更輕鬆，並運用 AI 功能實現更聰明的資料處理與高效生產力！立即取得
範例 8：多個值
XLOOKUP 的另一大優勢在於能針對同一筆相符資料同時傳回多個值。只要輸入一個公式取得第一個結果，其他返回值便會自動溢出至相鄰的空白儲存格。
以下範例中，您希望取得學生 ID「FG 9940005」的完整資訊。關鍵在於公式中的 `return_array` 必須指定一個範圍，而非單一欄或列；此處所設定的傳回陣列範圍為 B2:D9，共包含三欄資料。
請在空白儲存格 G2 中輸入下列公式，然後按下 ENTER 鍵以取得所有結果。
=XLOOKUP(F2,A2:A9,B2:D9)
所有結果儲存格皆顯示相同的公式。您僅能在第一個儲存格中編輯或修改公式，其餘儲存格的公式則無法編輯；此時您會發現編輯欄呈現灰色，表示內容不可變更。
總而言之，相較於 VLOOKUP，XLOOKUP 的多值傳回功能是一大進步！您再也不必為每個公式逐一指定欄位編號，實在太棒了！
範例 9：多重條件
XLOOKUP 的另一項強大新功能是支援多重條件查詢。關鍵在於公式中使用「&」運算子，將待檢索值區域與查詢陣列串接起來。以下範例將示範此用法。
我們需要查詢中型藍色花瓶的價格。此處需同時符合三個條件，才能找到對應項目。請在空白儲存格 I2 中輸入下列公式，然後按下 ENTER 鍵以取得結果。
=XLOOKUP(F2&G2&H2,A2:A12&B2:B12&C2:C12,D2:D12)
√ 注意：XLOOKUP 可直接處理陣列，無需按 Ctrl+Shift+Enter 來確認公式。
在 Excel 中，是否有比 XLOOKUP 更快速、更簡單的多重條件查詢方法？Kutools for Excel 提供了一項超讚的功能——「Lookup 多條件查找」！透過此功能，您只需點擊幾下，就能輕鬆完成多重條件查詢，效率大幅提升！
1. 請切換至 Excel 中的「Kutools」分頁，點選「進階 LOOKUP」，並在下拉選單中選擇「多條件查詢」。
2. 在「Lookup 多條件查找」對話方塊中，請依照下列步驟操作：
- 在「待檢索值與輸出結果區域」區段中，選取查詢值範圍與列表放置區域；
- 在「數據區域」區段中，請執行下列操作：
- 在「主鍵列」方塊中，按住 Ctrl 鍵逐一選取包含待檢索值區域的對應關鍵列；
- 在「返回列」方塊中，指定包含您所需返回值的欄位。
3. 點選「確定」按鈕，立即取得結果。
√ 注意：
- 對話方塊中「以指定值取代未找到而返回『N/A』的輸出結果」的區段為選用項目，您可依需求自行指定。
- 在「關鍵列」方塊中輸入的欄位數，必須與「待檢索值區域」方塊中輸入的欄位數相等，且兩個方塊中的條件順序須一一對應。
Kutools for Excel－透過超過 300 項必備工具全面強化 Excel，讓您的工作更快速、更輕鬆，並運用 AI 功能實現更聰明的資料處理與高效生產力！立即取得
範例 10：尋找最後一次相符的值
在 Excel 中找出最後一次相符的值時，請將第六個引數設為反向搜尋。
預設情況下，XLOOKUP 的搜尋模式為 1（由第一筆向最後一筆搜尋）。但 XLOOKUP 的優勢在於能靈活調整查詢方向——透過可選的 [search_mode] 引數即可掌控搜尋順序。只需將第六個引數設為 ——1，就能將查詢方向切換為從最後一筆往回搜尋至第一筆。
請參閱下方範例。我們希望查詢資料庫中 Emma 最近一次的銷售紀錄。
請在空白儲存格 G2 中輸入下列公式，然後按下 Enter 鍵以取得結果。
=XLOOKUP(F2,B2:B11,D2:D11,,,-1)
√ 注意：此處省略了第四與第五個引數（兩者皆為可選），僅將可選的第六個引數設為 -1.
若您無法使用 XLOOKUP，又不想記憶複雜公式，不妨善用「從下到上查找」功能，輕鬆搞定這項任務！
1. 請切換至 Excel 中的「Kutools」分頁，點選「進階 LOOKUP」，並在下拉清單中選擇「由下往上搜尋」。
2. 在「從下到上查找」對話方塊中，請按下列方式進行設定：
- 在「待檢索值與輸出結果區域」區段中，選取查詢範圍與列表放置區域；
- 在「數據區域」區段中，選取數據範圍，並指定「關鍵列」與「返回列」。
3. 點選「確定」按鈕，立即取得結果。
√ 注意：對話方塊中「以指定值取代未找到而返回『N/A』的輸出結果」的選項為可選項目，可依需求決定是否設定。
Kutools for Excel－透過超過 300 項必備工具全面強化 Excel，讓您的工作更快速、更輕鬆，並運用 AI 功能實現更聰明的資料處理與高效生產力！立即取得
下載 XLOOKUP 範例檔案
相關文章：
- 如何在 Excel 中靈活搭配使用 INDEX 與 MATCH 函數？
- 如何在 Excel 中運用模糊查找功能，找出近似比對的結果？
- 如何在 Excel 中運用雙向查詢公式？
- 如何在 Excel 中運用多條件 VLOOKUP 查詢數值？
- 如何在 Excel 中從右向左執行 VLOOKUP 查值？
最佳 Office 生產力工具
| 🤖 | KUTOOLS AI 助手：基於以下內容徹底革新數據分析：智慧執行 ｜ 產生程式碼｜ 建立自訂公式 ｜ 分析資料並產生圖表｜ 呼叫增強函數…… |
| 熱門功能：尋找、醒目提示或標記重複值 ｜ 刪除空白行 ｜ 合併列或儲存格而不遺失資料 ｜ 不使用公式的四捨五入…… | |
| 高級 LOOKUP：多重條件 VLookup ｜ 多重數值 VLookup ｜ 跨多個工作表 VLookup ｜ 模糊查找…… | |
| 高級下拉列表：快速建立下拉式清單 ｜ 相依式下拉式清單 ｜ 多選下拉式清單…… | |
| 欄位管理員：新增指定數量的欄位｜移動欄位｜切換隱藏欄位的可見狀態｜比較範圍與欄位…… | |
| 精選功能：網格聚焦 ｜ 設計視圖 ｜增強編輯欄 ｜ 工作簿與工作表管理員 ｜ 資源庫（自動文字）｜ 日期提取 ｜ 合併工作表 ｜ 加密／解密儲存格 ｜ 依清單傳送電子郵件 ｜ 超級篩選 ｜ 特殊篩選（篩選粗體儲存格／斜體／刪除線……） ...... | |
| 頂尖 15 工具組：12 文字工具（添加文本，刪除特定字符，……）｜ 50+ 圖表 類型（甘特圖，……）｜ 40+ 實用公式（基於生日計算年齡，……）｜ 19 插入工具（插入二維碼，從路徑插入圖片，……）｜ 12 轉換工具（金額轉大寫，匯率轉換，……）｜ 7 合併和拆分工具（高級合併行，分割儲存格，……）｜……以及更多 |
運用 Kutools for Excel 強化您的 Excel 技能，體驗前所未有的高效能！Kutools for Excel 提供超過 300 項進階功能，大幅提升生產力並節省寶貴時間。立即點擊，取得您最需要的功能……
Office Tab 為 Office 帶來分頁式介面，讓您的工作更輕鬆自在！
- 在 Word、Excel、PowerPoint 中啟用分頁式編輯與閱讀功能，以及 Access、Visio 與 Project。
- 在同視窗的新分頁中開啟並建立多份文件，而非另開新視窗。
- 每天為您提升 50% 的工作效率，並省下數百次滑鼠點擊！
所有 Kutools 增益集，一個安裝程式
Kutools for Office 套件整合了 Excel、Word、Outlook 與 PowerPoint 的增益集，以及 Office Tab Pro，非常適合需要跨多個 Office 應用程式協作的團隊使用！
- 全能套件— 包含 Excel、Word、Outlook 與 PowerPoint 增益集，以及 Office Tab Pro
- 一個安裝程式，一個授權— 數分鐘內即可完成設定（支援 MSI）
- 協同運作更出色— 在多個 Office 應用程式間實現流暢的生產力體驗
- 30 天完整功能試用— 無需註冊，無需信用卡
- 超值之選— 比單獨購買各增益集更省費用