了解 Excel 中的 XLOOKUP：综合指南

Excel 的 XLOOKUP 函数已发展成为 VLOOKUP 和 HLOOKUP 的现代替代品，结合了它们的功能并提供了增强的功能。在本文中，我将探讨 Excel 中 XLOOKUP 的概念、语法及其实际应用。

Excel 中的 XLOOKUP 是什么？

XLOOKUP 是 Excel 365 和 Excel 2021 中引入的多功能且强大的函数，因此在早期版本中不可用。它代表“扩展查找”，解决了其前身 VLOOKUP 和 HLOOKUP 的许多限制。 XLOOKUP 函数允许用户搜索指定范围内的值，并从另一列或行返回相关值，而不管它们的相对位置如何。其动态特性、简单性和改进的错误处理使其成为数据分析和检索的强大工具。

XLOOKUP 函数的语法

XLOOKUP函数的语法如下：

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

lookup_value：要在lookup_array 中搜索的值。

lookup_array：包含要搜索lookup_value 的数据的单元格范围。

return_array：根据查找结果检索对应值的范围。

[if_not_found]：可选参数，允许您指定未找到匹配项时返回的值。如果省略，Excel 将返回错误或#N/A。

[match_mode]：可选参数，确定要执行的匹配类型。它可以采用以下值：0（完全匹配 - 默认）、-1（完全匹配或下一个较小的项目）和 1（完全匹配或下一个较大的项目）。

[search_mode]：定义搜索方向的可选参数。它可以采用以下值：1（从上到下搜索 - 默认）和 -1（从下到上搜索）。

使用 XLOOKUP 函数进行垂直查找

让我们根据 ID 号 45 查找年龄。我们可以使用 VLOOKUP 函数轻松完成此操作。但这里我们将尝试用 XLOOKUP 函数代替。

无论如何，这是查找公式：

=XLOOKUP(C12,A2:A10,D2:D10)

此公式返回 29，这是 ID 号 45 的年龄。

配方分解

C12：这是引用 ID 号 45 的查找值。

A2:A10：这是一个查找数组。这是公式搜索查找值的范围。

D2:D10：这是我们要检索数据的单元格范围。

使用 XLOOKUP 函数进行水平查找

现在，让我们根据 ID 号 70 查找 Biology 中的标记。我们可以使用 HLOOKUP 函数轻松完成此操作。但在这里我们将尝试使用 XLOOKUP 函数。

无论如何，这是查找公式：

=XLOOKUP(E8,B1:G1,B6:G6)

此公式返回 86，这是 ID 号 70 的生物学分数。

配方分解

E8：是指ID号70的查找值。

B1:G1：这是一个查找数组。这是公式搜索查找值的范围。

B6:G6：这是我们要检索数据的单元格范围。

使用 XLOOKUP 函数处理错误

XLOOKUP 函数带有一个默认功能，允许我们在发生特定错误时显示自定义消息。那么，让我们看看如何利用这个功能。

在下面的屏幕截图中，您可以看到 XLOOKUP 公式返回 #N/A 错误，因为搜索 ID 号 75 不存在。

现在，我们将使用 XLOOKUP 函数的第四个参数显示不同的消息，而不是此内置错误消息。

XLOOKUP 函数的第四个参数是 [if_not_found]，它是一个可选参数。现在，查看以下公式，我使用该公式显示自定义消息而不是 #N/A 错误。

=XLOOKUP(E8,B1:G1,B6:G6,"No Record Found!")

探索 Excel 中 XLOOKUP 函数的匹配模式

XLOOKUP 函数的第 5 个参数允许您选择 3 种匹配模式之一。那些是：

0：完全匹配|默认

-1：完全匹配或下一个较小的项目

1：完全匹配或下一个更大的项目

如果跳过此参数或设置为 0，XLOOKUP 将查找完全匹配。请参阅下面的屏幕截图：

将第五个参数设置为 -1 将首先查找完全匹配。如果未找到匹配项，它将查找紧邻的较小项。请参阅下面的屏幕截图中的示例：

这里，ID 号 65 不存在。它紧邻的较小 ID 号是 60。因此，XLOOKUP 根据 ID 号 60 返回生物学中的分数，即 94。

最后，将第 5 个参数设置为 1 将提示 XLOOKUP 查找完全匹配或比查找值直接更大的值。这是一个例子：

探索 Excel 中 XLOOKUP 函数的搜索模式

XLOOKUP 函数通过其第 6 个参数 [search_mode]（可选参数）提供 4 种搜索模式。以下是它的 4 种搜索模式：

1：从头到尾搜索

-1：从后到快搜索

2：二分查找（升序排序）

-2：二分查找（按降序排序）

假设我们必须在查找数组中有 2 个重复项。现在 XLOOKUP 函数将选择哪一个，第一个还是第二个？那么，seach_mode 参数解决了这个问题。

如果我们将此参数设置为 1，它将拾取第一个实体。这是一个例子：

将第 6 个参数设置为 -1 将拾取最后一个实体。看截图：

XLOOKUP 相对于 VLOOKUP 和 HLOOKUP 的优点

XLOOKUP 具有多项优势，使其成为优于 VLOOKUP 和 HLOOKUP 的首选：

双向查找：与VLOOKUP和HLOOKUP不同，XLOOKUP可以执行垂直和水平查找。它允许用户从数据集中的任何方向搜索和检索数据。

简单性：XLOOKUP 函数的简化语法使其更易于使用和理解，即使对于 Excel 经验有限的用户也是如此。

增强的错误处理：XLOOKUP 可以更有效地处理错误，并更好地控制未找到匹配项的处理情况，从而减少出现意外结果的可能性。

动态范围选择：XLOOKUP 在插入或删除列/行时自动调整范围，无需手动更新公式。

结论

XLOOKUP 彻底改变了数据专业人员在 Excel 中执行查找的方式。其多功能性、动态范围选择和改进的错误处理使其成为数据分析和检索的强大工具。通过了解其语法和实际应用，您可以充分利用 XLOOKUP 的潜力来简化与数据相关的任务，并将您的 Excel 体验提升到新的高度。