如何在 Excel 中使用 NEW & ADVANCED XLOOKUP 函數（10 個示例）

如何在 Excel 中使用 NEW & ADVANCED XLOOKUP 函數（10 個示例）

Excel 的新功能 XLOOKUP 是 Excel 可以提供的最強大、最簡單的查找功能。 經過不懈的努力，微軟終于發布了這個XLOOKUP函數來替代VLOOKUP、HLOOKUP、INDEX+MATCH等查找函數。

在本教程中，我們將向您展示 XLOOKUP 的優勢是什么 和 你怎么能得到它并應用它 解決不同的查找問題。

如何獲得 XLOOKUP？

XLOOKUP 函數語法

XLOOKUP 函數示例

示例 1：完全匹配 使用 XLOOKUP 執行精確匹配 只需點擊幾下即可獲得精確匹配 示例 2：近似匹配 使用 XLOOKUP 執行近似匹配 示例 3：通配符匹配 使用 XLOOKUP 執行通配符匹配 示例 4：向左看 使用 XLOOKUP 向左看 只需單擊幾下即可從右到左查找值 示例 5：垂直或水平查找 使用 XLOOKUP 執行垂直或水平查找 示例 6：雙向查找 使用 XLOOKUP 執行雙向查找 示例 7：自定義未找到消息 使用 XLOOKUP 自定義未找到消息 使用方便的功能自定義 #N/A 錯誤 示例 8：多個值 使用 XLOOKUP 返回多個值 示例 9：多個條件 使用 XLOOKUP 執行多條件查找 使用快速方法進行多條件查找 示例 10：查找具有最后一個匹配項的值 使用 XLOOKUP 獲取最后匹配的結果 使用神奇的工具輕松查找最后一個匹配值

下載 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：完全匹配

使用 XLOOKUP 執行精確匹配

您是否曾經因為每次使用 VLOOKUP 時都必須指定精確匹配模式而感到沮喪？ 幸運的是，當您嘗試了令人驚嘆的 XLOOKUP 功能后，這個麻煩就不再存在了。默認情況下，XLOOKUP 生成完全匹配.

現在，假設您有一份辦公用品庫存清單，并且您需要知道一件物品的單價，比如說鼠標，請執行以下操作。

鍵入 下面的公式 進入空白單元格 F2，然后按 輸入 獲得結果的關鍵。

=XLOOKUP(E2,A2:A10,C2:C10)

現在您使用高級 XLOOKUP 公式知道鼠標的單價。 由于匹配代碼已默認為完全匹配，因此您無需指定它。 比 VLOOKUP 更容易、更高效。

只需點擊幾下即可獲得精確匹配

也許您正在使用較低版本的 Excel，并且還沒有升級到 Excel 2021 或 Microsoft 365 的計劃。 在這種情況下，我會推薦一個方便的功能 - 在列表公式中查找值 of Kutools for Excel. 使用此功能，您無需復雜的公式或訪問 XLOOKUP 即可獲得結果。

與our Excel加載項 研究所alled，請按以下步驟操作：

1. 點擊單元格放置匹配的結果。

2。 去 庫工具 標簽，點擊 公式助手，然后單擊 下拉列表中的公式助手.

3.在 公式助手對話框，請進行如下配置：

選擇 查找 ，在 公式類型部分; 在 選擇公式部分， 選擇 在列表中查找值; 在 參數輸入部分，請執行以下操作： 在 表數組框，選擇包含查找值和結果值的數據范圍； 在查找值框，選擇要搜索的值的單元格或范圍。 請注意它必須在 table_array 的第一列； 在 柱箱，選擇要從中返回匹配值的列。

4。 點擊 OK 按鈕獲取結果。

點擊下載Kutools for Excel 30天免費試用.

示例 2. 近似匹配

使用 XLOOKUP 執行近似匹配

運行一個 近似查找，您需要 在第五個參數中將匹配模式設置為 1 或 -1. 當沒有找到完全匹配時，它返回下一個更大或更小的值.

在這種情況下，您需要了解員工收入的稅率。 電子表格的左側是 2021 年的聯邦所得稅括號。如何在 E 列中獲得員工的稅率？ 別擔心。 請執行以下操作：

1. 鍵入 下面的公式 進入空白單元格 E2，然后按 輸入

獲得結果的關鍵。

然后根據需要更改返回結果的格式。

=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; 按 輸入 按鈕獲取單元格 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 執行通配符匹配

在我們研究之前 XLOOKUP 通配符匹配函數，我們先來看看 什么是通配符.

在 Microsoft Excel 中， 通配符是一種特殊的字符，可以替換任何字符. 特別是 有幫助 當您想要執行部分匹配查找時。

通配符分為三種類型： 星號 (*), 問號（？）及 波浪號 (~).

星號 (*) 表示文本中的任意數量的字符； 問號 (?) 代表文本中的任何單個字符； 波浪號 (~) 用于將通配符 (*, ? ~) 轉換為文字字符。 在通配符前面放置波浪號 (~) 以實現此功能；

在大多數情況下，當我們執行 XLOOKUP 通配符匹配功能時，我們使用星號 (*) 字符。 現在讓我們看看通配符匹配是如何工作的。

假設您有一份美國 50 家最大公司的股票市值列表，并且您想知道幾家公司的市值，但公司名稱很短，這是通配符匹配的完美場景。 請跟著我一步一步來做這個把戲。

√ 注意：要進行通配符匹配，最重要的是將第五個參數 [match_mode] 設置為 2。

1. 鍵入 下面的公式 到空白單元格 H3，然后按 輸入 獲得結果的關鍵。

=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：向左看

使用 XLOOKUP 向左看

一個 VLOOKUP 的缺點 是的是 被限制在查找列的右側執行查找. 如果您嘗試查找留給查閱列的值，您將收到 #N/A 錯誤。 別擔心。 XLOOKUP 是解決這個問題的完美查找函數。

XLOOKUP 旨在查找值 左邊或右邊 的查找列。 它沒有限制，滿足 Excel 用戶的需求。 在下面的示例中，我們將向您展示訣竅。

假設您有一個帶有電話代碼的國家/地區列表，并且您想查找帶有已知電話代碼的國家/地區名稱。

我們需要查找 C 列并返回 A 列中的值。請執行以下操作：

1. 鍵入 下面的公式 進入空白單元格 G2。

=XLOOKUP(F2,C2:C11,A2:A11)

2。 按 輸入 獲得結果的關鍵。

√ 注意：XLOOKUP 向左查找函數可以代替 Index 和 Match 向左查找值。

只需單擊幾下即可從右到左查找值

對于那些不想記住公式的人，我會在這里推薦一個有用的功能 - 從右到左查找 of Kutools for Excel. 使用此功能，您可以在幾秒鐘內執行從右到左的查找。

與our Excel加載項 研究所alled，請按以下步驟操作：

1。 去 庫工具 Excel 中的選項卡，找到 超級查找和點擊 從右到左查找 在下拉列表中。

2.在 從右到左查找對話框，需要進行如下配置：

在 查找值和輸出范圍部分，請指定 查找范圍 和 輸出范圍; 在 數據范圍部分，輸入 數據范圍，然后指定 關鍵列 和 返回欄;

3。 點擊 OK 按鈕獲取結果。

點擊下載Kutools for Excel 30天免費試用.

示例 5：垂直或水平查找

使用 XLOOKUP 執行垂直或水平查找

作為 Excel 用戶，您可能熟悉 VLOOKUP 和 HLOOKUP 函數。VLOOKUP 是在列中垂直查看 和 HLOOKUP 是水平看一行.

現在 新的 XLOOKUP 結合了它們， 意思是 您只需要使用一種語法來執行垂直查找或水平查找. 天才，不是嗎？

在下面的示例中，我們將說明如何僅使用一種 XLOOKUP 語法來垂直或水平運行查找。

執行垂直查找, 鍵入 下面的公式 在空白單元格 E2 中，按 輸入 獲得結果的關鍵。

=XLOOKUP(E1,A2:A13,B2:B13)

執行水平查找, 鍵入 下面的公式 在空白單元格 P2 中，按 輸入 獲得結果的關鍵。

=XLOOKUP(P1,B1:M1,B2:M2)

正如你可以看到， 語法是一樣的。 該 唯一的區別 兩個公式之間是你輸入 列 在您輸入時進行垂直查找 行 在水平查找中。

示例 6：雙向查找

使用 XLOOKUP 執行雙向查找

你還在用嗎 索引和匹配函數 在二維范圍內查找值？ 嘗試 改進的 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，按 輸入 按鈕獲取結果。

或者你可以做 另一種方式，使用“內部”XLOOKUP 來返回整行的值，這些值都是 Kim 的科目成績。 然后使用“外部”XLOOKUP 在 Kim 的所有科目成績中查找化學成績。

鍵入 下面的公式 在空白單元格 H4 中，然后按 輸入 按鈕獲取結果。

=XLOOKUP(H2,B1:E1,XLOOKUP(H1,A2:A10,B2:E10))

XLOOKUP的雙向查找功能也是其縱橫查找功能的完美詮釋。 如果你想試試！

示例 7：自定義未找到消息

使用 XLOOKUP 自定義未找到消息

就像其他查找函數一樣，當 XLOOKUP 函數 找不到匹配項中， #N/A 錯誤信息 將被退回。 某些 Excel 用戶可能會感到困惑。 但好消息是 錯誤處理 可以在 XLOOKUP 函數的第四個參數.

隨著 內置 [if_not_found] 參數，您可以指定 用于替換 #N/A 結果的自定義消息. 在可選的第四個參數中鍵入您需要的文本并將文本括在雙引號 （”）.

例如，找不到城市丹佛，因此 XLOOKUP 返回 #N/A 錯誤消息。 但是在我們使用文本“No Match”自定義第四個參數后，公式將顯示“No Match”文本而不是錯誤消息。

鍵入 下面的公式 在空白單元格 F3 中，然后按 輸入 按鈕獲取結果。

=XLOOKUP(E2,A2:A11,C2:C11,"No Match")

使用方便的功能自定義 #N/A 錯誤

要使用您的自定義消息快速覆蓋 #N/A 錯誤， Kutools for Excel is 一個完美的工具 在 Excel 中為您提供幫助。 憑借其內置 將 0 或 #N/A 替換為空白或特定值功能，您可以指定未找到消息而無需復雜的公式或訪問 XLOOKUP。

隨著我們的 Excel加載項 已安裝，請執行以下操作：

1。 去 庫工具 Excel 中的選項卡，找到 超級查找和點擊 將0或＃N / A替換為空白或特定值 在下拉列表中。

2.在 將 0 或 #N/A 替換為空白或特定值對話框，需要進行如下配置：

在 查找值和輸出范圍部分， 選擇 查找范圍 和 輸出范圍; 然后 選擇用特定值替換 0 或 #N/A 選項, 輸入文字 你喜歡; 在 數據范圍部分，選擇 數據范圍，然后指定 關鍵列 和 返回列.

3。 點擊 OK 按鈕獲取結果。 未找到匹配項時將顯示自定義消息。

點擊下載Kutools for Excel 30天免費試用.

示例 8：多個值

使用 XLOOKUP 返回多個值

另一個 優點 XLOOKUP 的特點是它能夠 返回多個值 在同一時間進行同一場比賽。 輸入一個公式以獲得第一個結果，然后輸入其他返回值 灑 自動進入相鄰的空白單元格。

在下面的示例中，您想要獲取有關學生 ID“FG9940005”的所有信息。 訣竅是在公式中提供一個范圍作為 return_array 而不是單個列或行。 在這種情況下，返回數組范圍是 B2:D9，包括三列。

鍵入 下面的公式 在空白單元格 G2 中，按 輸入 獲得所有結果的關鍵。

=XLOOKUP(F2,A2:A9,B2:D9)

所有結果單元格都顯示相同的公式。 你可以 編輯或修改 公式 在第一個單元格中，但 在其他單元格中，公式不可編輯. 你可以看到公式欄是 變灰, 表示不能對其進行更改。

總而言之，XLOOKUP 的多值函數是一個 有用的改進 與 VLOOKUP 相比。 您無需為每個公式分別指定每個列號。 豎起大拇指！

示例 9. 多個標準

使用 XLOOKUP 執行多條件查找

另一個 驚人的新功能 XLOOKUP 的特點是它能夠 使用多個條件查找. 訣竅是 串聯 查找值和查找數組 “＆“ 操作員 分別在公式中。 讓我們通過下面的例子來說明。

我們需要知道中藍色花瓶的價格。 在這種情況下，需要三個查找值（條件）來查找匹配項。 鍵入 下面的公式 在空白單元格 I2 中，然后按 輸入 獲得結果的關鍵。

=XLOOKUP(F2&G2&H2,A2:A12&B2:B12&C2:C12,D2:D12)

√ 注意：XLOOKUP 可以直接處理數組。 無需使用 Control + Shift + Enter 確認公式。

使用快速方法進行多條件查找

有沒有 更快更容易 在excel中執行多標準查找而不是XLOOKUP的方法？ Kutools for Excel 提供 一個驚人的功能 - 多條件查找. 使用此功能，您只需單擊幾下即可運行多個條件查找！

隨著我們的 Excel加載項 已安裝，請執行以下操作：

1。 去 庫工具 Excel 中的選項卡，找到 超級查找和點擊 多條件查找 在下拉列表中。

2.在 多條件查找對話框，請執行以下操作：

在 查找值和輸出范圍部分，選擇 查找值范圍 和 輸出范圍; 在數據范圍部分，請執行以下操作： 點擊 對應的鍵列 通過持有一一包含查找值 按Ctrl 鍵入 鍵列框; 指定 欄 其中包含返回的值 返回列框.

3。 點擊 OK 按鈕獲取結果。

√ 注意：

Replace #N/A error value with a specified value 部分在對話框中是可選的，您可以指定或不指定。 在 Key column 框中輸入的列數必須等于在 Lookup Values 框中輸入的列數，并且兩個框中的條件順序必須彼此一一對應。

點擊下載Kutools for Excel 30天免費試用.

示例 10. 查找最后一個匹配項的值

使用 XLOOKUP 獲取最后匹配的結果

為了找到 最后匹配值 在 Excel 中，設置 第六論點 在 XLOOKUP 函數中 逆序搜索.

默認情況下，XLOOKUP 中的搜索模式設置為 1，這是 從頭到尾搜索. 但好的一點是 XLOOKUP 是 查找的方向可以改變. XLOOKUP 提供 可選 [搜索模式] 參數 控制搜索順序。 只需將第六個參數中的搜索模式設置為 -1，查找方向更改為從最后一個到第一個搜索。

請看下面的例子。 我們想知道數據庫中 Emma 的最后一次銷售。

鍵入 下面的公式 在空白單元格 G2 中，然后按 輸入 獲得結果的關鍵。

=XLOOKUP(F2,B2:B11,D2:D11,,,-1)

√ 注意：第四個和第五個參數是可選的，在這種情況下省略。 我們只將可選的第六個參數設置為 -1。

使用神奇的工具輕松查找最后一個匹配值

如果您無法訪問 XLOOKUP 并且不想記住復雜的公式，您可以應用 從下到上查找功能 of Kutools for Excel 完成它 容易.

隨著我們的 Excel加載項 已安裝，請執行以下操作：

1。 去 庫工具 Excel 中的選項卡，找到 超級查找和點擊 從下到上查找 在下拉列表中。

2.在 從下到上查找對話框，需要進行如下配置：

在 查找值和輸出范圍部分，選擇 查找范圍 和 輸出范圍; 在 數據范圍部分，選擇 數據范圍，然后指定 關鍵列 和 返回欄.

3。 點擊 OK 按鈕獲取結果。

√ 注意：對話框中的用指定值替換#N/A 錯誤值部分是可選的，您可以指定或不指定。

點擊下載Kutools for Excel 30天免費試用.

下載 XLOOKUP 示例文件

XLOOKUP 示例.xlsx

相關文章： Excel中如何同時使用INDEX和MATCH？ 如何應用模糊查找在 Excel 中查找近似匹配結果？ 如何在 Excel 中使用雙向查找公式？ 如何在 Excel 中使用多個條件查找值？ 如何在 Excel 中從右到左查找值？