Excel 中的 XLOOKUP 函數是什麼？如何使用它

如果您經常使用 Excel，那麼您可能經常使用 LOOKUP 函數。如果您不熟悉，LOOKUP 函數用於搜索單個行或列以在另一行或列中查找特定值。

當處理 Excel 工作表中的大量數據時，此功能會派上用場。在 Excel 中，有多種類型的 LOOKUP 函數。

在本文中，我們將告訴您有關 Microsoft Excel 中 XLOOKUP 函數的所有信息，以及一些有用的示例。

Excel中的XLOOKUP函數是什麼？

XLOOKUP 替換了舊函數，如 HLOOKUP、VLOOKUP 和 LOOKUP。它支持垂直和水平查找。

換句話說，XLOOKUP 函數允許您在給定的數據集中水平和垂直地快速查找值，並在不同的行或列中返回其對應的值。

有很多方法可以使用 XLOOKUP 函數來充分利用它。我們將看一些 XLOOKUP 示例，以便您更好地理解它。

如何訪問 XLOOKUP 函數

遺憾的是，XLOOKUP 不適用於運行 Office 2010、2013、2016 和 2019 版本的用戶。它僅適用於 Microsoft Office 365 套件。

如果您的計算機上未使用 Microsoft 365 套件，則可能需要升級到 Microsoft 365才能訪問 XLOOKUP 功能。如果您已經在使用 Microsoft 365，那麼您會發現該選項已在 Excel 中啟用。XLOOKUP 也可在Office 365 Online 上使用。

相關：Office 2021 與 Microsoft 365：您應該使用哪一個？

XLOOKUP 函數的語法

XLOOKUP 函數的語法類似於 VLOOKUP 和 HLOOKUP。如果您曾經使用過它們，您會發現使用 XLOOKUP 更方便。以下是 Excel 中 XLOOKUP 函數的語法：

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

XLOOKUP 函數最多支持六個參數，這裡是它們的值。

1. lookup_value（必需）：您要查找的值。
2. lookup_array（必需）：要查找查找值的數組。
3. return_array（必需）：找到查找值時要從中獲取和返回值的數組。
4. [if_not_found]（可選）：當未找到匹配項時，這是要返回的值。
5. [match_mode]（可選）：此參數可讓您指定要查找的匹配類型。有不同的值來指定它：
   • 0 - 它尋找精確匹配，並且該值應該與lookup_array 中的值完全匹配。未提及時也將其設置為默認值。
   • -1 - 查找完全匹配，一旦找到，返回到下一個較小的值。
   • 1 - 它尋找完全匹配，一旦找到就返回下一個更大的值。
   • 2 - 它使用通配符進行部分匹配，其中* , ? , 和~有特殊含義。
6. [search_mode]（可選）：用於在lookup_array中指定XLOOKUP的搜索模式。有不同的值來指定相同的：
   • 1 - 從第一項開始執行搜索。當沒有指定任何內容時，它被設置為默認值。
   • -1 - 從最後一項開始執行反向搜索。
   • 2 - 在數據需要按升序排序的 lookup_array 中執行二分搜索。如果數據沒有排序，可能會產生錯誤或錯誤的結果。
   • -2 - 在 lookup_array 中執行二進制搜索，其中數據需要按降序排序。如果數據沒有排序，可能會產生錯誤或錯誤的結果。

相關：Excel 中的通配符是什麼？如何使用它們

Excel中XLOOKUP函數的優缺點

XLOOKUP 仍然是一個比 VLOOKUP 和 INDEX/MATCH 有其優勢的函數。但它也有它的缺點。

XLOOKUP 功能的優點

• 它可以垂直和水平工作。
• 需要三個參數，而不是 VLOOKUP 和 INDEX MATCH 函數中的四個參數。
• 始終默認為完全匹配。
• 可以使用通配符執行部分匹配查找。
• 可以按降序執行查找。
• 在 INDEX MATCH 中使用一個函數而不是兩個函數。

XLOOKUP 函數的缺點

• 可選參數對於初學者來說可能看起來很複雜。
• 選擇兩個範圍時會消耗更多時間，並且電子表格中的單元格過多。
• 當查找和數組返回的長度不同時返回錯誤。
• 需要記住查找和返回範圍。

如何在 Excel 中使用 XLOOKUP 函數

XLOOKUP 函數的工作方式類似於 Excel 中的 LOOKUP 函數。您可以通過簡單地選擇函數應該運行的單元格引用來使用 XLOOKUP。

或者，您也可以使用位於頂部的“公式欄”框；並在那裡插入 XLOOKUP 函數語法。

XLOOKUP 函數示例

如果這一切都讓您感到困惑，我們將嘗試通過以下示例進行解釋。這些示例將使您更好地了解 XLOOKUP 函數的工作原理。

示例 1

假設您有一個看起來像這樣的數據集（在下圖中），並且您想在 F 和 G 單元格中獲得 Matthew 的科學標記。

為了獲取這些數據，我們將在 F2 單元格中使用以下 XLOOKUP 函數：

=XLOOKUP(F2,A2:A15,B2:B15)

該公式將導致下一個單元格中馬修的科學分數。這是因為我們將查找值指定為 F2，在本例中為 Matthew。

示例 2

讓我們採用相同的數據集，但這次的名稱在右側。同樣，我們仍在尋找馬修的科學分數。

這是我們將在這種情況下使用的 XLOOKUP 函數：

=XLOOKUP(F2,D2:D15,A2:A15)

此函數的結果數據也將相同。即使結果相同，lookup_array和return_array值也會在此處發生變化。

XLOOKUP 函數解決了 VLOOKUP 函數的主要問題。您可以以任何方式移動行和列，XLOOKUP 仍然可以工作。但是 VLOOKUP 會返回錯誤。

示例 3

您還可以同時水平和垂直獲取多個結果。所以如果我們想得到馬修所有科目的分數，那也是可以的。在我們的例子中 XLOOKUP 函數是：

=XLOOKUP(F2,D2:D15,A2:C15)

這個函數的結果是一樣的。

在這種情況下，XLOOKUP 函數執行了水平和垂直搜索，結果完全準確。在這裡，相應地修改了參數值。

相關：如何在 Excel 中使用 AVERAGEIF 函數

使用 XLOOKUP 而不是 VLOOKUP

XLOOKUP 在查找功能方面是一個很大的改進。XLOOKUP 函數的唯一缺點是它不向後兼容。如果您與舊版本的 Microsoft Office 套件共享電子表格，您可能會遇到錯誤。

但是，您可以從 Office 365 Online 訪問 XLOOKUP。XLOOKUP 功能仍處於早期階段，因此 Excel 用戶需要一些時間來採用它。