如何在 Excel 中使用 XLookup

處理大型數據集總是很棘手。 Excel 是可用於操作、格式化和分析大量信息的最強大的工具之一。

有幾個眾所周知的函數可以幫助您在數據集中查找特定匹配項，例如 VLookup。

但像 VLookup 這樣的函數有一定的局限性，因此微軟最近將 XLookup 函數引入了 Excel。 它提供了一種更可靠的方法來查找數據集中的特定值。

了解如何有效地使用它可以大大減少您在 Excel 中分析數據所花費的時間。

XLookup 在 Excel 中做什麼？

Excel 中 XLookup 的優勢

XLookup 所需參數

XLookup 可選參數

如何在 Excel 中使用 XLookup

Excel 中 XLookup 的最佳實踐

XLookup 在 Excel 中做什麼？

XLookup 的核心功能是能夠在數據范圍或數組中搜索特定值。 當您運行該函數時，將從另一個範圍或數組返回相應的值。

最終，它是 Excel 數據集的搜索工具。

根據您的參數，XLookup 將顯示具有多個匹配結果的實例中的第一個或最後一個值。

其他查找函數，如 VLookup（垂直查找）或 HLookup（水平查找）都有局限性，例如只能從右向左搜索。 用戶需要重新排列數據或找到復雜的解決方法來緩解這種情況。

XLookup 是一種更加靈活的解決方案，允許您獲取部分匹配、使用多個搜索條件以及使用嵌套查詢。

查看此 XLookup Excel 教程以了解其實際效果：

Excel 中 XLookup 的優勢

Excel 中 XLookup 的主要優點是它可以節省時間。 例如，您無需操作數據定位即可獲得搜索結果。

與其他 Excel 查找公式一樣，它可以節省大量時間，否則就必須手動滾動瀏覽行和數據行。

當與其他查找函數進行比較時，XLookup 的靈活性是關鍵。 例如，您可以使用通配符來搜索部分匹配項。

這有助於減少總體錯誤，因為您可以使用它來查找可能拼寫錯誤或輸入錯誤的數據。

XLookup 的參數也比 VLookup 更簡單，使其更快、更易於使用。 例如，XLookup 默認為完全匹配，而您必須在 VLookup 參數中指定這一點。

XLookup 的整體功能非常優越，因為它可以一次返回多個結果。 此用例的一個示例是搜索特定數據集中的前五個值。

XLookup 所需參數

您的 XLookup 函數需要三個參數：

• Lookup_value：這是您在數組中搜索的值。
• Lookup_array：這是您想要顯示返回值的單元格範圍。
• Return_array：這是您希望函數搜索值的單元格範圍。

因此，XLookup 的簡單版本如下所示：

=XLOOKUP(查找值、查找數組、返回數組)

XLookup 可選參數

XLookup 函數還有幾個可選參數，您可以將它們用於更複雜的場景或縮小搜索範圍：

• Match_mode：這決定了要使用的匹配類型，例如精確匹配或通配符匹配以獲得部分匹配。
• Search_mode：這決定了您的函數是否應該從左到右搜索，反之亦然。
• If_not_found：該參數告訴函數如果根本不匹配則應返回什麼值。

包含可選參數後，XLookup 函數如下所示：

=XLOOKUP(查找值、查找數組、返回數組、[if_not_found]、[匹配模式]、[搜索模式])

如何在 Excel 中使用 XLookup

1.打開 Excel 和要使用 XLookup 函數的數據表。

2.選擇要放置 XLookup Excel 公式的單元格。

3.在該單元格中鍵入“=”，然後鍵入“XLookup”。 單擊下拉列表中出現的“XLookup”選項。

4.左括號將自動生成。 在左括號後，按正確的順序輸入所需的參數：lookup_value、lookup_array 和 return_array。

這意味著為每個參數選擇單元格範圍，並在繼續下一個參數之前放置一個逗號。

5.如果您使用任何可選參數，請在必需參數之後輸入它們。

6. XLookup 參數完成後，在按 Enter 鍵之前添加右括號。

7. XLookup 的結果應顯示在您輸入函數的單元格中。

在此 Excel XLookup 示例中，我們使用 XLookup 查找特定學生的成績。 因此，lookup_value 是學生的姓名（“Ruben Pugh”）。

Lookup_array 是 A 列下的學生姓名列表。最後，lookup_return 是 C 列下的學生成績列表。使用該公式，該函數返回學生成績的值：C-。

但是如何將 XLookup 與可選參數一起使用呢？

使用與上面相同的數據集，假設我們想要找到姓“Smith”的學生的出勤率。

在這裡，我們告訴 Excel 如果無法檢索該值，則返回消息“Not Found”，表明學生“Smith”不在此列表中：

現在，假設我們想要確定是否有學生的出勤率達到 70%。 但如果數據集中不存在 70%，我們還想知道下一個最接近的出勤率。

如果 70 不存在，我們將使用 match_mode 參數下的值“1”來查找下一個最大的項目：

由於沒有學生的出勤率達到 70%，因此 XLookup 返回下一個最大值“75”。

Excel 中 XLookup 的最佳實踐

使用描述性參考文獻

最好對您正在使用的單元格使用描述性引用，而不是像 A1:A12 這樣的通用範圍。

當需要調整公式時（您經常使用 XLookup 等函數執行此操作），描述性參考可以讓您更輕鬆地理解您最初使用該公式執行的操作。

如果您將電子表格傳遞給需要快速理解公式引用的新用戶，這也很有用。

盡可能使用精確匹配

在公式中使用精確匹配默認值（而不是通配符）有助於確保您不會在返回中得到意外的值。

通配符對於識別部分匹配更有用，但精確匹配是確保公式按預期工作的最佳方法。

保持參數範圍相同大小

將參數放在一起時，請確保 return_array 和 Look_up 數組使用相同數量的單元格。 如果不是，您將收到錯誤，並且 Excel 將僅在單元格中返回 #VALUE。

測試你的配方

使用簡單的 XLookup 解決問題並不困難。 但是，如果您將嵌套函數或 XLookup 與其他公式結合使用，請確保您正在測試所有內容。

如果您不這樣做並收到錯誤，則向後工作並確定函數出錯的位置可能會很困難。

入門

作為在 Excel 中使用查找功能的一種新的改進方法，XLookup 在多個方面優於經典的 VLookup。

雖然該函數的基礎知識很容易掌握，但以更複雜的方式使用 XLookup 可能需要一些練習。 但通過一些練習數據和測試場景，您將很快掌握 XLookup。