如何在 Microsoft Excel 中使用 XLOOKUP 函數

Excel 的新 XLOOKUP 將取代 VLOOKUP，為 Excel 最流行的功能之一提供強大的替代品。 這個新功能解決了 VLOOKUP 的一些限制，並具有額外的功能。 這是你需要知道的。

什麼是 XLOOKUP？

新的 XLOOKUP 功能為 VLOOKUP 的一些最大限制提供了解決方案。 此外，它還取代了 HLOOKUP。 例如，XLOOKUP 可以向左看，默認為完全匹配，並允許您指定單元格範圍而不是列號。 VLOOKUP 不是那麼容易使用或用途廣泛。 我們將向您展示這一切是如何運作的。

目前，XLOOKUP 僅對 Insiders 計劃的用戶可用。 任何人都可以加入預覽體驗計劃，以便在最新的 Excel 功能可用後立即訪問它們。 Microsoft 將很快開始向所有 Office 365 用戶推出它。

如何使用 XLOOKUP 函數

讓我們直接深入了解 XLOOKUP 的實際應用示例。 以下面的示例數據為例。 我們希望為 A 列中的每個 ID 從 F 列返回部門。

這是一個經典的完全匹配查找示例。 XLOOKUP 函數只需要三個信息。

下圖顯示了帶有六個參數的 XLOOKUP，但只有前三個是完全匹配所必需的。 因此，讓我們關注它們：

• Lookup_value：您要查找的內容。
• Lookup_array：在哪裡看。
• Return_array：包含要返回的值的範圍。

以下公式適用於此示例： =XLOOKUP(A2,$E$2:$E$8,$F$2:$F$8)

現在讓我們在這裡探討 XLOOKUP 相對於 VLOOKUP 的幾個優勢。

沒有更多的列索引號

VLOOKUP 臭名昭著的第三個參數是指定要從表數組返回的信息的列號。 這不再是一個問題，因為 XLOOKUP 使您能夠選擇要返回的範圍（本示例中的 F 列）。

並且不要忘記，XLOOKUP 可以查看所選單元格左側的數據，這與 VLOOKUP 不同。 更多關於這下面。

插入新列時，您也不再遇到公式損壞的問題。 如果在您的電子表格中發生這種情況，則返回範圍會自動調整。

完全匹配是默認值

在學習 VLOOKUP 時，為什麼必須指定精確匹配總是令人困惑。

幸運的是，XLOOKUP 默認為完全匹配——這是使用查找公式的更常見的原因）。 這減少了回答第五個參數的需要，並確保公式新手的錯誤更少。

所以簡而言之，XLOOKUP 比 VLOOKUP 提出的問題更少，更人性化，也更耐用。

XLOOKUP 可以向左看

能夠選擇查找範圍使 XLOOKUP 比 VLOOKUP 更通用。 使用 XLOOKUP，表列的順序無關緊要。

VLOOKUP 受限於搜索表的最左側列，然後從指定數量的列返回到右側。

在下面的示例中，我們需要查找一個 ID（E 列）並返回該人的姓名（D 列）。

下面的公式可以實現： =XLOOKUP(A2,$E$2:$E$8,$D$2:$D$8)

如果找不到怎麼辦

查找函數的用戶非常熟悉 #N/A 錯誤消息，當他們的 VLOOKUP 或他們的 MATCH 函數無法找到所需的內容時，該錯誤消息會向他們打招呼。 這通常有一個合乎邏輯的原因。

因此，用戶快速研究如何隱藏此錯誤，因為它不正確或無用。 當然，也有辦法做到這一點。

XLOOKUP 帶有自己的內置“如果未找到”參數來處理此類錯誤。 讓我們在前面的示例中查看它的實際效果，但輸入錯誤的 ID。

以下公式將顯示文本“Incorrect ID”而不是錯誤消息： =XLOOKUP(A2,$E$2:$E$8,$D$2:$D$8,"Incorrect ID")

使用 XLOOKUP 進行範圍查找

雖然不如精確匹配那麼常見，但查找公式的一種非常有效的用途是在範圍內查找值。 舉個例子。 我們希望根據花費的金額返還折扣。

這次我們不是在尋找一個特定的值。 我們需要知道 B 列中的值在 E 列範圍內的哪個位置。這將決定獲得的折扣。

XLOOKUP 有一個可選的第五個參數（記住，它默認為完全匹配），命名為匹配模式。

您可以看到 XLOOKUP 具有比 VLOOKUP 更強大的近似匹配功能。

可以選擇查找小於 (-1) 或大於 (1) 所查找值的最接近匹配。 還有一個選項可以使用通配符 (2)，例如 ? 或者 *。 默認情況下，此設置不像 VLOOKUP 那樣啟用。

如果未找到完全匹配，此示例中的公式將返回小於查找值的最接近的值： =XLOOKUP(B2,$E$3:$E$7,$F$3:$F$7,,-1)

但是，單元格 C7 中有一個錯誤，返回 #N/A 錯誤（未使用“如果未找到”參數）。 這應該返回 0% 的折扣，因為花費 64 不符合任何折扣的標準。

XLOOKUP 函數的另一個優點是它不需要像 VLOOKUP 那樣按升序排列查找範圍。

在查找表底部輸入新行，然後打開公式。 通過單擊並拖動角來擴大使用範圍。

該公式立即糾正了錯誤。 範圍底部有“0”不是問題。

就個人而言，我仍然會按查找列對錶格進行排序。 底部有“0”會讓我發瘋。 但是公式沒有被打破的事實是輝煌的。

XLOOKUP 也替換了 HLOOKUP 函數

如前所述，XLOOKUP 函數也是在這裡替代 HLOOKUP。 一個功能代替兩個。 優秀的！

HLOOKUP 函數是水平查找，用於沿行搜索。

不像它的兄弟 VLOOKUP 那樣廣為人知，但對於下面這樣的示例很有用，其中標題位於 A 列，數據位於第 4 行和第 5 行。

XLOOKUP 可以在兩個方向上查看 - 向下列和沿行。 我們不再需要兩個不同的功能。

在此示例中，該公式用於返回與單元格 A2 中的名稱相關的銷售值。 它沿著第 4 行查找名稱，並從第 5 行返回值： =XLOOKUP(A2,B4:E4,B5:E5)

XLOOKUP 可以自下而上查看

通常，您需要查找一個列表以查找第一次（通常是唯一一次）出現的值。 XLOOKUP 有一個名為搜索模式的第六個參數。 這使我們能夠將查找切換到從底部開始並查找列表以查找最後一次出現的值。

在下面的示例中，我們希望在 A 列中找到每種產品的庫存水平。

查找表按日期順序排列，每個產品有多個庫存檢查。 我們希望返回上次檢查時的庫存水平（最後一次出現的產品 ID）。

XLOOKUP 函數的第六個參數提供了四個選項。 我們有興趣使用“從後到先搜索”選項。

完整的公式如下所示： =XLOOKUP(A2,$E$2:$E$9,$F$2:$F$9,,,-1)

在這個公式中，第四個和第五個參數被忽略了。 它是可選的，我們想要完全匹配的默認值。

圍捕

XLOOKUP 函數是 VLOOKUP 和 HLOOKUP 函數備受期待的繼承者。

本文使用了各種示例來展示 XLOOKUP 的優勢。 其中之一是 XLOOKUP 可以跨工作表、工作簿以及表格使用。 文章中的示例保持簡單，以幫助我們理解。

由於動態數組即將引入 Excel，它還可以返回一系列值。 這絕對是值得進一步探索的東西。

VLOOKUP 的日子屈指可數。 XLOOKUP 就在這裡，並將很快成為事實上的查找公式。