如何在 Excel 中使用 NEW & ADVANCED XLOOKUP 函數（10 個示例）
Excel 的新功能 X查找 是 Excel 可以提供的最強大、最簡單的查找功能。 經過不懈的努力，微軟終於發布了這個XLOOKUP函數來替代VLOOKUP、HLOOKUP、INDEX+MATCH等查找函數。
在本教程中，我們將向您展示 XLOOKUP 的優勢是什麼 和 你怎麼能得到它並應用它 解決不同的查找問題。
如何獲得 XLOOKUP？
自 XLOOKUP 函數 is 只可用 in 適用於 Microsoft 365 的 Excel, Excel中2021和 網絡版Excel，您可以將您的 Excel 升級到可用版本以獲取 XLOOKUP。
XLOOKUP 函數語法
XLOOKUP 函數 查找範圍或數組，然後返回第一個匹配結果的值。 “ 句法 XLOOKUP 函數如下：
參數：
- 查找值（必填）：您正在尋找的價值。 它可以在 table_array 範圍的任何列中。
- Lookup_array（必需）：您在其中搜索查找值的數組或範圍。
- 返回數組（必填）：要從中獲取值的數組或範圍。
- If_not_found（可選）：未找到有效匹配項時返回的值。 您可以自定義 [if_not_found] 中的文本以顯示不匹配。
否則，返回值默認為#N/A。 - 匹配模式（可選）：在這裡您可以指定如何將lookup_value 與lookup_array 中的值進行匹配。
- 0（默認）= 完全匹配。 如果未找到匹配項，則返回 #N/A。
- -1 = 完全匹配。 如果未找到匹配項，則返回下一個較小的值。
- 1 = 完全匹配。 如果未找到匹配項，則返回下一個較大的值。
- 2 = 部分匹配。 使用通配符，如 *, ? 和 ~ 運行通配符匹配。
- 搜索模式（可選）：在這裡您可以指定要執行的搜索順序。
- 1（默認）= 在lookup_array 中從第一項到最後一項搜索lookup_value。
- -1 = 從最後一項到第一項搜索lookup_value。 當您需要在 lookup_array 中獲取最後一個匹配結果時，它會有所幫助。
- 2 = 執行需要按升序排序的lookup_array 的二分查找。 如果不排序，則返回結果無效。
- -2 = 執行需要按降序排序的lookup_array 的二分查找。 如果不排序，則返回結果無效。
為 XLOOKUP 函數參數的詳細信息，請執行以下操作：
1. 鍵入 下面的語法 進入一個空單元格，請注意您只需要輸入括號的一側。
2。 按 按Ctrl + A，然後 提示框 彈出顯示 函數參數. 支架的另一側自動完成。
3. 下拉數據面板, 然後你可以看到所有 六個函數參數 XLOOKUP 的。
 |
>>> |  |
XLOOKUP 函數示例
相信你現在已經掌握了XLOOKUP功能的基本原理。 讓我們直接進入 實際例子 XLOOKUP 的。
示例 1：完全匹配
您是否曾經因為每次使用 VLOOKUP 時都必須指定精確匹配模式而感到沮喪？ 幸運的是，當您嘗試了神奇的 XLOOKUP 功能後，這個麻煩就不再存在了。 默認情況下，XLOOKUP 生成完全匹配.
現在，假設您有一份辦公用品庫存清單，並且您需要知道一件物品的單價，比如說鼠標，請執行以下操作。
鍵入 下面的公式 進入空白單元格 F2，然後按 Enter 獲得結果的關鍵。
=XLOOKUP(E2,A2:A10,C2:C10)
現在您使用高級 XLOOKUP 公式知道鼠標的單價。 由於匹配代碼已默認為完全匹配，因此您無需指定它。 比 VLOOKUP 更容易、更高效。
也許您正在使用較低版本的 Excel，並且還沒有升級到 Excel 2021 或 Microsoft 365 的計劃。 在這種情況下，我會推薦 一個方便的功能 - 在列表公式中查找值 of Excel的Kutools. 使用此功能，您無需複雜的公式或訪問 XLOOKUP 即可獲得結果。
與our Excel加載項 研究所alled，請按以下步驟操作：
1. 點擊單元格放置匹配的結果。
2。 轉到 庫工具 標籤，點擊 公式助手，然後單擊 下拉列表中的公式助手.
3。 在裡面 公式助手對話框，請進行如下配置：
- 選擇 查找 ，在 公式類型部分;
- 在 選擇公式部分， 選擇 在列表中查找值;
- 在 參數輸入部分，請執行以下操作：
- 在 表數組框，選擇包含查找值和結果值的數據范圍；
- 在 查找值框，選擇要搜索的值的單元格或範圍。 請注意它必須在 table_array 的第一列；
- 在 柱箱，選擇要從中返回匹配值的列。
4。 點擊 OK 按鈕來獲得結果。
示例 2. 近似匹配
運行一個 近似查找，你需要 在第五個參數中將匹配模式設置為 1 或 -1. 當沒有找到完全匹配時，它返回下一個更大或更小的值.
在這種情況下，您需要了解員工收入的稅率。 電子表格的左側是 2021 年的聯邦所得稅括號。如何在 E 列中獲得員工的稅率？ 別擔心。 請執行以下操作：
1. 鍵入 下面的公式 進入空白單元格 E2，然後按 Enter 獲得結果的關鍵。
然後根據需要更改返回結果的格式。
=XLOOKUP(D2,B2:B8,A2:A8,,1)
 |
>>> |  |
√ 注意：第四個參數 [If_not_found] 是可選的，所以我就省略了。
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 的結果了。 要獲得其餘結果，您需要 使 lookup_array 和 return_array 固定 通過將光標放在數組中並按 F4 鍵。 那麼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 是解決這個問題的完美查找函數。
X查找 旨在查找值 左邊或右邊 的查找列。 它沒有限制，滿足 Excel 用戶的需求。 在下面的示例中，我們將向您展示訣竅。
假設您有一個帶有電話代碼的國家/地區列表，並且您想查找帶有已知電話代碼的國家/地區名稱。
我們需要查找C列並返回A列中的值。請執行以下操作：
1. 鍵入 下面的公式 進入空白單元格 G2。
=XLOOKUP(F2,C2:C11,A2:A11)
2。 按 Enter 獲得結果的關鍵。
√ 注意：XLOOKUP 向左查找函數可以代替 Index 和 Match 向左查找值。
對於那些不想記住公式的人，我會在這裡推薦 一個有用的功能 - 從右到左查找 of Excel的Kutools. 使用此功能，您可以在幾秒鐘內執行從右到左的查找。
與our Excel加載項 研究所alled，請按以下步驟操作：
1。 轉到 庫工具 Excel 中的選項卡，找到 超級查找，然後點擊 從右到左查找 在下拉列表中。
2。 在裡面 從右到左查找對話框，需要進行如下配置：
- 在 查找值和輸出範圍部分，請指定 查找範圍 和 輸出範圍;
- 在 數據范圍部分，輸入 數據范圍，然後指定 關鍵列 和 返回欄;
3。 點擊 OK 按鈕來獲得結果。
示例 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：雙向查找
你還在用嗎 索引和匹配函數 在二維範圍內查找值？ 嘗試 改進的 XLOOKUP 完成你的工作 更容易.
XLOOKUP 可以執行 雙重查找，找到一個 路口 的兩個值。 經過 嵌套 一個 XLOOKUP 在另一個 XLOOKUP 裡面，裡面的 XLOOKUP 可以返回一整行或一列，然後這個返回的行或列作為返回數組輸入到外部 XLOOKUP 中。
假設你有一個不同學科的學生成績列表，你想知道 Kim 的化學學科的成績。
讓我們看看我們如何使用神奇的 XLOOKUP 來實現這一目標。
- 我們運行“內部”XLOOKUP 以返回輸入列的值。 XLOOKUP(H2,B1:E1,B2:E10) 可以獲得一系列化學成績。
- 我們通過使用“內部”XLOOKUP 作為完整公式中的 return_array 將“內部”XLOOKUP 嵌套在“外部”XLOOKUP 中。
- 然後是最後的公式：
=XLOOKUP(H1,A2:A10,XLOOKUP(H2,B1:E1,B2:E10))
- 鍵入 上面的公式 進入空白單元格 H3，按 Enter 按鈕來獲得結果。
或者你可以做 另一種方式，使用“內部”XLOOKUP 來返回整行的值，這些值都是 Kim 的科目成績。 然後使用“外部”XLOOKUP 在 Kim 的所有科目成績中查找化學成績。
- 鍵入 下面的公式 在空白單元格 H4 中，然後按 Enter 按鈕來獲得結果。
=XLOOKUP(H2,B1:E1,XLOOKUP(H1,A2:A10,B2:E10))
XLOOKUP的雙向查找功能也是其縱橫查找功能的完美詮釋。 如果你想試試！
示例 7：自定義未找到消息
就像其他查找函數一樣，當 XLOOKUP 函數 找不到匹配項是， #N/A 錯誤信息 將被退回。 某些 Excel 用戶可能會感到困惑。 但好消息是 錯誤處理 可以在 XLOOKUP 函數的第四個參數.
隨著 內置 [if_not_found] 參數，您可以指定 用於替換 #N/A 結果的自定義消息. 在可選的第四個參數中鍵入您需要的文本並將文本括在 雙引號 （”）.
例如，找不到城市丹佛，因此 XLOOKUP 返回 #N/A 錯誤消息。 但是在我們使用文本“No Match”自定義第四個參數後，公式將顯示“No Match”文本而不是錯誤消息。
鍵入 下面的公式 在空白單元格 F3 中，然後按 Enter 按鈕來獲得結果。
=XLOOKUP(E2,A2:A11,C2:C11,"No Match")
要使用您的自定義消息快速覆蓋 #N/A 錯誤， Excel的Kutools is 一個完美的工具 在 Excel 中為您提供幫助。 憑藉其內置 將 0 或 #N/A 替換為空白或特定值功能，您可以指定未找到的消息而無需複雜的公式或訪問 XLOOKUP。
以 Excel加載項 已安裝，請執行以下操作：
1。 轉到 庫工具 Excel 中的選項卡，找到 超級查找，然後點擊 將0或＃N / A替換為空白或特定值 在下拉列表中。
2。 在裡面 將 0 或 #N/A 替換為空白或特定值對話框，需要進行如下配置：
- 在 查找值和輸出範圍部分， 選擇 查找範圍 和 輸出範圍;
- 然後 選擇用特定值替換 0 或 #N/A 選項, 輸入文字 你喜歡;
- 在 數據范圍部分，選擇 數據范圍，然後指定 關鍵列 和 返回列.
3。 點擊 OK 按鈕獲取結果。 未找到匹配項時將顯示自定義消息。
示例 8：多個值
另一個 優點 XLOOKUP 的特點是它能夠 返回多個值 在同一時間進行同一場比賽。 輸入一個公式以獲得第一個結果，然後輸入其他返回值 灑 自動進入相鄰的空白單元格。
在下面的示例中，您想要獲取有關學生 ID“FG9940005”的所有信息。 訣竅是在公式中提供一個範圍作為 return_array 而不是單個列或行。 在這種情況下，返回數組範圍是 B2:D9，包括三列。
鍵入 下面的公式 在空白單元格 G2 中，按 Enter 獲得所有結果的關鍵。
=XLOOKUP(F2,A2:A9,B2:D9)
所有結果單元格都顯示相同的公式。 你可以 編輯或修改 公式 在第一個單元格中，但 在其他單元格中，公式不可編輯. 你可以看到公式欄是 變灰, 表示不能對其進行更改。
總而言之，XLOOKUP 的多值函數是一個 有用的改進 與 VLOOKUP 相比。 您無需為每個公式分別指定每個列號。 豎起大拇指！
示例 9. 多個標準
另一個 驚人的新功能 XLOOKUP 的特點是它能夠 使用多個條件查找. 訣竅是 串聯 查找值和查找數組 “＆“ 操作員 分別在公式中。 讓我們通過下面的例子來說明。
我們需要知道中藍色花瓶的價格。 在這種情況下，需要三個查找值（條件）來查找匹配項。 鍵入 下面的公式 在空白單元格 I2 中，然後按 Enter 獲得結果的關鍵。
=XLOOKUP(F2&G2&H2,A2:A12&B2:B12&C2:C12,D2:D12)
√ 注意：XLOOKUP 可以直接處理數組。 無需使用 Control + Shift + Enter 確認公式。
有沒有 更快更容易 在excel中執行多標準查找而不是XLOOKUP的方法？ Excel的Kutools 提供 一個驚人的功能 - 多條件查找. 使用此功能，您只需單擊幾下即可運行多個條件查找！
以 Excel加載項 已安裝，請執行以下操作：
1。 轉到 庫工具 Excel 中的選項卡，找到 超級查找，然後點擊 多條件查找 在下拉列表中。
2。 在裡面 多條件查找對話框，請執行以下操作：
- 在 查找值和輸出範圍部分，選擇 查找值範圍 和 輸出範圍;
- 在 數據范圍部分，請執行以下操作：
- 點擊 對應的鍵列 通過持有一一包含查找值 按Ctrl 鍵入 鍵列框;
- 指定 欄 其中包含返回的值 返回列框.
3。 點擊 OK 按鈕來獲得結果。
√ 注意：
- Replace #N/A error value with a specified value 部分在對話框中是可選的，您可以指定或不指定。
- 在 Key column 框中輸入的列數必須等於在 Lookup Values 框中輸入的列數，並且兩個框中的條件順序必須彼此一一對應。
示例 10. 查找最後一個匹配項的值
為了找到 最後匹配值 在 Excel 中，設置 第六論點 在 XLOOKUP 函數中 逆序搜索.
默認情況下，XLOOKUP 中的搜索模式設置為 1，這是 從頭到尾搜索. 但好的一點是 X查找 是 查找的方向可以改變. XLOOKUP 提供 可選 [搜索模式] 參數 控制搜索順序。 只需將第六個參數中的搜索模式設置為 -1，查找方向更改為從最後一個到第一個搜索。
請看下面的例子。 我們想知道數據庫中 Emma 的最後一次銷售。
鍵入 下面的公式 在空白單元格 G2 中，然後按 Enter 獲得結果的關鍵。
=XLOOKUP(F2,B2:B11,D2:D11,,,-1)
√ 注意：第四個和第五個參數是可選的，在這種情況下被省略。 我們只將可選的第六個參數設置為 -1。
如果您無法訪問 XLOOKUP 並且不想記住複雜的公式，您可以應用 從下到上查找功能 of Excel的Kutools 完成它 容易.
以 Excel加載項 已安裝，請執行以下操作：
1。 轉到 庫工具 Excel 中的選項卡，找到 超級查找，然後點擊 從下到上查找 在下拉列表中。
2。 在裡面 從下到上查找對話框，需要進行如下配置：
- 在 查找值和輸出範圍部分，選擇 查找範圍 和 輸出範圍;
- 在 數據范圍部分，選擇 數據范圍，然後指定 關鍵列 和 返回欄.
3。 點擊 OK 按鈕來獲得結果。
√ 注意：對話框中的用指定值替換#N/A 錯誤值部分是可選的，您可以指定或不指定。
下載 XLOOKUP 示例文件
相關文章：
- Excel中如何同時使用INDEX和MATCH？
- 如何應用模糊查找在 Excel 中查找近似匹配結果？
- 如何在 Excel 中使用雙向查找公式？
- 如何在 Excel 中使用多個條件查找值？
- 如何在 Excel 中從右到左查找值？
- 超級公式欄 （輕鬆編輯多行文本和公式）； 閱讀版式 （輕鬆讀取和編輯大量單元格）； 粘貼到過濾範圍...
- 合併單元格/行/列 和保存數據； 拆分單元格內容； 合併重複的行和總和/平均值...防止細胞重複； 比較範圍...
- 選擇重複或唯一 行; 選擇空白行 （所有單元格都是空的）； 超級查找和模糊查找 在許多工作簿中； 隨機選擇...
- 確切的副本 多個單元格，無需更改公式參考； 自動創建參考 到多張紙； 插入項目符號，複選框等...
- 收藏并快速插入公式，範圍，圖表和圖片； 加密單元 帶密碼 創建郵件列表 並發送電子郵件...
- 提取文字，添加文本，按位置刪除， 刪除空間; 創建和打印分頁小計； 在單元格內容和註釋之間轉換...
- 超級濾鏡 （將過濾方案保存並應用於其他工作表）； 高級排序 按月/週/日，頻率及更多； 特殊過濾器 用粗體，斜體...
- 結合工作簿和工作表; 根據關鍵列合併表； 將數據分割成多個工作表; 批量轉換xls，xlsx和PDF...
- 數據透視表分組依據 週號，週幾等 顯示未鎖定的單元格 用不同的顏色 突出顯示具有公式/名稱的單元格...
- 在Word，Excel，PowerPoint中啟用選項卡式編輯和閱讀，發布者，Access，Visio和Project。
- 在同一窗口的新選項卡中而不是在新窗口中打開並創建多個文檔。
- 將您的工作效率提高 50%，每天為您減少數百次鼠標點擊！
