Microsoft Excel 中的 XLOOKUP VS INDEX-MATCH

众所周知，在 Microsoft Excel 数据管理领域，有两个强大的函数在搜索和检索信息方面表现出色：XLOOKUP 和 INDEX-MATCH。这两个函数是 Microsoft Excel 用户寻求高效且灵活的数据查找解决方案不可或缺的工具。

XLOOKUP：一种现代方法

XLOOKUP 是 Excel 相对较新的一个功能，随 Excel 365 和 Excel 2019 一起推出。该函数旨在简化在范围内搜索特定值并返回相应结果的复杂任务。XLOOKUP 特别擅长处理精确匹配和近似匹配，使其成为各种场景的通用选择。

该函数的力量在于其简洁的方法，为用户提供了一种直观的查找方式。它主要接受查找值、搜索该值的范围以及从中返回结果的相应范围。可选参数允许用户指定如果找不到查找值时要返回的值或采取的操作。XLOOKUP 函数能够轻松处理近似匹配，这增加了它的吸引力，尤其是在涉及数字范围或数据范围时。

INDEX-MATCH：经典组合

众所周知，INDEX-MATCH 函数主要依赖于两个成熟函数的组合。MATCH 函数首先确定查找值在指定范围内的位置，然后 INDEX 函数从另一个范围检索相应的值。这种组合提供了更精细级别的控制，特别是在处理无法进行直接垂直查找的数据集时。此外，有效处理非连续数据的能力是 INDEX-MATCH 组合的标志，使其成为用户熟悉的灵活选择。

然而，在 XLOOKUP 和 INDEX-MATCH 之间做出选择通常取决于手头任务的具体需求。XLOOKUP 因其简洁易用而受到青睐，使其成为优先考虑直接查找方法的用户的绝佳选择。其对近似匹配的支持在处理大型数据集或范围时尤其有益，在这些情况下，精确匹配可能并不总是可行的。

INDEX-MATCH 通常在需要更复杂方法的情况下表现出色。它的灵活性使用户能够轻松地导航复杂的数据结构，并执行其他方法可能难以进行的查找。

Microsoft Excel 中的 XLOOKUP 函数是什么？

XLOOKUP 是 Microsoft Excel 中一个相对较新的功能，于 2020 年推出，它革新了用户在给定数据集中搜索特定值的方式。与之前的函数不同，XLOOKUP 提供了在表中垂直和水平搜索的灵活性，允许用户在行和列中查找搜索值左侧或右侧的结果。这种新获得的功能在用户需要根据各种标准检索信息的情况下特别有用。

在我们提供的示例中，公式 =XLOOKUP (E3, A2: A6, C2: C6) 在单元格 F3 中用于定位和提取数据。分解来看，单元格 E3 代表搜索值（在本例中是学生的相应学号），单元格范围 A2:A6 是 Microsoft Excel 搜索该值的数组（通常是包含学号的列）。单元格范围 C2:C6 是通常从中检索相应结果的数组（包含分数的列）。结果 49 在此实例中，代表具有学号 1003 的相应学生获得的分数。

此外，此公式本质上告诉 Microsoft Excel：“在 A 列中查找学号，然后返回 C 列中的相应分数。”XLOOKUP 的引入简化了此类搜索操作，使其成为专业和个人环境中处理数据集的用户的强大工具。

列出使用 Microsoft Excel 中 XLookup 的优点。

众所周知，XLOOKUP 被认为是 Microsoft Excel 中的一项变革性功能，它超越了 VLOOKUP 或 HLOOKUP 等传统查找函数的限制，提供了大量优势，可以简化数据检索和分析。因此，让我们深入了解使 XLOOKUP 成为电子表格应用程序中强大功能的关键优势。

1. 双向搜索能力：XLOOKUP 以其垂直和水平搜索能力而脱颖而出，这种双向搜索灵活性超越了 VLOOKUP 或 HLOOKUP 的单向限制，使用户能够有效地浏览和提取数据，而无论数据集的结构如何。
2. 数组返回功能：一项重大进步是 XLOOKUP 返回整个数组值的能力。与仅检索第一个匹配项的 VLOOKUP 不同，XLOOKUP 通常通过一次操作提供全面的输出范围。当用户寻求基于单个查找标准的多个数据点时，这非常宝贵，可以简化复杂的任务。
3. 统一处理匹配类型：XLOOKUP 在单个函数内统一处理近似匹配和精确匹配。这消除了对 VLOOKUP 或 HLOOKUP 等单独函数的需求，从而提高了公式创建的效率，并最大限度地减少了由于管理多个函数以处理不同匹配场景而导致的错误。
4. 主动错误处理：一项有影响力的功能是 XLOOKUP 指定在找不到查找项时要返回的值的能力。这种主动方法简化了对丢失或不存在的数据点的管理，增强了电子表格模型的整体稳健性，并确保了更准确可靠的结果。
5. 直观的列引用：此外，Microsoft Excel 中的 XLOOKUP 函数消除了手动计数 VLOOKUP 中“col_index_num”参数的列的需求。这种直观的方法减少了由于计数错误而导致的错误可能性，提高了用户友好性和可访问性。
6. 通配符支持以增强灵活性：XLOOKUP 引入了另一层灵活性，支持通配符字符。此功能使用户能够在搜索值时匹配模式，从而解决了部分匹配或特定模式至关重要的情况。通配符支持增强了数据检索的精度和范围，提供了有效信息提取的细致方法。

此外，XLOOKUP 被认为是双向搜索、数组返回功能、统一匹配类型处理、主动错误处理、直观的列引用以及通配符支持，这些共同使其成为一个不可或缺且用途广泛的查找函数。随着用户处理各种复杂的数据集，XLOOKUP 成为了一个强大的盟友，简化了在 Excel 中查找和检索数据的过程，同时显著提高了整体电子表格的效率。

XLOOKUP 函数

Microsoft Excel 中 XLOOKUP 函数的语法如下：

此语法关联的参数如下：

• lookup_value：这是必需参数；它是我们将从 return_array 中指定的数组检索结果的查找值。
• lookup_array：这是搜索的数组或单元格范围，并且是必需参数。
• return_array：这是从中返回值的数组或单元格范围，这也是必需参数。
• [if_not_found]：如果未在 return_array 中找到精确匹配，则需要打印的文本值。如果此参数为空，则在没有匹配项时，我们将收到 #N/A 错误，这是一个可选参数。
• [match_mode]：这主要指定了匹配的类型，参数是可选的，值为：

• [search_mode]：这里，我们通常指定如何搜索这些参数，这也是可选的。值为：

如何使用 Microsoft Excel 中的 XLOOKUP 函数？

众所周知，XLOOKUP 函数是 VLOOKUP 函数的改进版本。在这里，它可以搜索一个单元格范围而不是单个列。此外，我们可以手动在任何单元格中输入该函数，或通过 Microsoft Excel 功能区使用。

1. 手动在 Excel 中输入 XLOOKUP

为此，我们可以有一个 Excel 工作表，其中通常包含太阳系中所有八大行星的名称以及它们总的月球数量。

在这个例子中，让我们找出木星的月球数量。

1. 为此，我们必须查找任何行星然后查看其月球数量。我们可以使用行星名称作为查找值；查找范围从单元格 B2 到 B9，返回数组从单元格 C2:C9 作为参数。在单元格 F4 中输入公式。这里，第一个参数是查找值的引用。
   
2. 接下来，我们需要选择查找范围。如本例所示，它将是 B 列中的行星。之后，我们需要选择单元格范围从 B2 到 B9。
   
3. 第三个参数是 B 列中的返回数组，其中包含月球数量。然后，我们需要选择单元格范围从 C2 到 C9。
   
4. 现在，在执行上述步骤后，我们需要关闭括号，然后按“Enter”键。我们在单元格 F4 中得到木星的月球数量，即 80。请注意，我们没有指定其他可选参数，因为我们执行的是简单的搜索和检索。
   

因此，当手动输入函数时，它简单易用。

2. 通过 Excel 功能区在 Excel 中输入 XLOOKUP

XLOOKUP 的快捷方法是通过 Excel 功能区输入。然后，我们需要将光标放在我们实际需要插入公式的单元格中。

• 为了获得 XLOOKUP 快捷方式，我们需要转到 Microsoft Excel 功能区，然后单击“公式”选项卡。
• 在“函数库”下，我们需要选择“查找与引用”。然后选择 XLOOKUP 选项。
  

我们将弹出一个窗口，我们可以在其中输入参数。

XLOOKUP 函数可以以多种方式使用，如下例所示。

示例

XLOOKUP() 函数可以凭借其多功能性和易用性高效地取代 VLOOKUP 函数。下面的示例演示了如何使用该函数查找给定范围内的任何特定值。

示例 #1

让我们看看该函数如何进行近似匹配。在下面的 XLOOKUP 示例中，我们有一些员工的姓名（A 列）和他们的薪水（B 列）。根据薪水，将确定他们的奖金百分比（C 列）。范围 F5:G11 包含不同薪资范围的奖金详细信息。

步骤 1

• 年收入在 4000 美元到 5000 美元之间的员工将获得 0% 的奖金。年收入在 5000 美元到 6000 美元之间的员工将获得 6% 的奖金，依此类推。
• 要查找近似匹配，我们需要将第五个参数输入为 -1。然后，它将搜索指定值，如果找不到匹配项，它将返回下一个较小的值。
• 此外，年薪为 5500 美元的员工将找到并匹配 5000 美元（下一个较低的值），并返回其相应的奖金 6%。

之后，我们需要将公式 =XLOOKUP(B2,$F$7:$F$11,$G$7:$G$11, -1) 输入到单元格 C2 中，然后按 Enter。

步骤 2：然后，我们需要将自动填充柄从单元格 C2 拖到单元格 C9，以便有效地获得所有员工的奖金。

示例 #2

现在，让我们考虑下表，其中包含超市中不同商品名称及其单价。该超市通常会对某些商品提供折扣，这些商品分别列在 D 列和 E 列中。然后，我们将应用折扣百分比并计算其折扣价。

通常，B 列和 G 列已格式化为货币和百分比，然后我们需要将折扣百分比应用于 A 列中的商品，然后高效地计算其折扣价格。

步骤 1：现在，在这里，我们正在寻找第一个商品 Grapes 的折扣。然后，我们需要分别在单元格 C2 中输入公式。

步骤 2：现在，在此步骤中，我们可以看到 E 列包含商品名称，F 列包含折扣百分比。然后，我们只需要分别选择 E2:E6 和 F2:F6 单元格范围作为 lookup_array 和 return_array。在这里，我们将为这两个数组添加“$”以使其成为绝对引用。

步骤 3：现在，我们需要在 C 列中获得折扣价格。

• 为此，我们将用单元格 B2 中的单位价格乘以函数的输出值。
• 然后，我们将其从初始价格 B2 中减去，以获得折扣价格。

之后，我们需要应用上述更改；我们在单元格 C2 中输入公式：

现在，我们需要将自动填充柄从 C2 到 C8 的范围拖动，以获得每件商品的折扣价格。

步骤 4：我们应该已经注意到了 #N/A 错误。通常，它主要出现在 D 列和 E 列中未指定折扣的商品上。为避免此错误，我们需要将公式的第四个参数指定为 0。

步骤 5：现在，根据上一步，我们只需要从键盘按下 Enter 键。将自动填充柄从 C2 拖到 C8 以获得每件商品的折扣价格。同样，由于我们需要将零作为第四个参数，因此未在折扣列表中找到的商品在折扣后价格相同。

Microsoft Excel 中的 INDEX MATCH 函数

众所周知，INDEX MATCH 是一个 Excel 函数，它组合了 INDEX 和 MATCH 公式，使用户能够进行高级查找。前者根据列号和行号在给定表中获取值。相反，后者返回值在行或列中的相对位置。此外，它可以跨单元格范围从左到右和从右到左查找值。因此，它被认为是 VLOOKUP 函数的更合适的替代方案。

此外，例如，我们可以使用 Microsoft Excel 中的 INDEX MATCH 函数，使用下面提到的数据，根据员工 ID 查找员工姓名。

首先，我们将创建单元格 D2（在本例中为 11）的引用，其中包含员工 ID。接下来，我们将尝试在单元格 E2 中获取员工姓名，只需输入公式即可。

INDEX 函数根据 MATCH 函数为单元格 D2 中的员工 ID 11 提供的行数，在单元格 B2 到 B10 的范围内查找员工姓名。该函数有效地返回表数组中 ID 为 11 的员工姓名“RAM”。

语法 - INDEX + MATCH 函数

我们都知道 INDEX MATCH 通常是 INDEX 和 MATCH 公式的一个组合。因此，我们可以使用它来获取查找值的期望结果。

那么，让我们有效地逐一了解这两个函数的语法。

#1 - INDEX 函数

• Reference：它可以被视为单个单元格范围或非相邻单元格范围的集合。
• Row_Number：来自数组的第一个参数，我们需要从中获取确切的行号值。
• [Column Num]：这是来自数组的可选参数，我们可以使用它从指定行获取列值。
• [Area_Num]：再次，这是一个可选参数，我们可以将其用于多个范围。我们也可以通过选择所有可用范围的引用来提供区域编号。

#2 - MATCH 函数

MATCH 函数主要有三个参数。下面是相同的图片。

• Lookup_Value：我们要查找其在查找数组（第二个参数）中位置的值，它是必需参数。
• Lookup_Array：通常，这将是我们要查找查找值的范围或表数组，它也是必需参数。
• [Match_type]：这是一个可选参数，是我们必须为查找值定义的匹配标准。它可以是：

1 = 它将主要查找小于或等于我们提供的查找值，并返回近似匹配。它还需要查找数组按升序排序（从低到高或 A 到 Z）。

0 = 它将查找并返回查找值的精确匹配，而不考虑数据如何排列或排序。

-1 = 这将查找大于或等于我们提供的查找值，并返回近似匹配。它需要查找数组按降序排序（从高到低或 Z 到 A）。

如何使用 Index + Match Excel 函数？

INDEX 函数根据提供的行号和列号在数组内搜索值。

• 例如，下面是不同美国各州不同年份的销售数据。

现在，我们将找出 2018 年 NH 州的销售额。

以下是获取结果的步骤：

步骤 1：首先，我们将在单元格 K3 中输入 INDEX 函数。

步骤 2：我们需要为数组参数选择表数组，即范围从单元格 B2:I10。

步骤 3：现在，我们将输入行号来获取单元格值。在这里，我们试图找出 NH 州的值，因此行号将是 4（Excel 中的第 5 行）。

步骤 4：我们也在寻找特定年份的值。因此，我们需要为表数组提供列号。在这里，我们试图找出 2018 年的值，列号也将是 4。

这完成了 INDEX 公式。现在，关闭括号并按 Enter 键以获取结果，即 NH 的 4523。

我们在上述步骤中手动为 Index 公式提供了行号和列号。众所周知，MATCH 函数可以获取给定行或列中值的 {位置}。首先，我们将使用 MATCH 函数查找 NH 州的行号。

步骤 1：首先，我们需要在单元格 K4 中输入 MATCH 函数。

步骤 2：然后，对于 MATCH 函数，查找值将是“NH”。

步骤 3：现在，在此步骤中，我们将有效地选择 A2:A10 的单元格范围作为查找数组。

步骤 4：最后一部分是匹配类型。对于此，我们将选择 0，因为我们正在查找精确匹配。

该函数主要返回 NH 州的行号 4。同样，我们可以为任何年份（如 2018 年）执行相同操作以获取列号。

该函数将分别返回 2018 年的列号 4。

现在，我们可以结合使用 MATCH 函数和 INDEX 函数来动态获取行号和列号。因此，让我们找出 VB 州和 2021 年的销售额。然后，我们将分别在单元格 K7 中输入状态名称 VB，在单元格 L6 中输入年份 2021。

接下来，我们将使用 MATCH 和 INDEX 函数自动获取 VB 州和 2021 年的行号和列号。

示例

现在，让我们看一些在 Microsoft Excel 中使用 INDEX MATCH 函数的高级示例。

示例 #1：VLOOKUP 函数的替代方案。

众所周知，VLOOKUP 是一个高级查找函数，它有其局限性。因此，我们可以使用 INDEX 和 MATCH 函数作为 Microsoft Excel 中 VLOOKUP 函数的替代方案。

• 例如，下表考虑了家庭成员的年龄数据。

在这个例子中，我们将尝试通过使用单元格引用 D2 来查找“Peter John”的年龄。在这里，查找值（年龄）位于数组的右侧，而 VLOOKUP 无法从右向左提取数据。

因此，在这种情况下，我们必须使用 INDEX MATCH 函数来克服 VLOOKUP 的局限性。

步骤 1：首先，我们需要在单元格 E2 中输入 INDEX 函数。

步骤 2：现在，之后，我们需要为 Array 参数选择年龄列数组，即 A2:A10。

步骤 3：现在，在此步骤中，我们只需要分别输入 MATCH 函数中的行号参数。

步骤 4：对于查找值，我们必须选择单元格 D2。

步骤 5：对于查找数组，我们必须选择单元格范围，即 B2:B10。

步骤 6：最后一个参数是选择匹配类型。为此，我们将选择精确匹配，即 0。

步骤 7：关闭括号。它将动态返回结果 17，这是 Peter John 的年龄。

因此，我们可以有效地使用 MATCH 公式作为 INDEX 公式的支持函数，作为 VLOOKUP 函数的替代方案。

XLookup 和 Index Match 函数之间有哪些主要区别？

XLookup 和 Index Match 函数之间的主要区别如下：

1. 语法

• `XLOOKUP` 语法更简单，主要设计为更用户友好。
• `INDEX-MATCH` 涉及使用两个单独的函数 `INDEX` 和 `MATCH`，这可能显得不那么直接。

2. 方向

• `XLOOKUP` 可以高效地让我们使用单个函数在垂直和水平方向上查找值。
• `INDEX-MATCH` 通常需要组合 `INDEX` 和 `MATCH` 函数来进行垂直查找，并且通常需要额外的设置来进行水平查找。

3. 默认行为

• `XLOOKUP` 具有近似匹配的默认行为，与 `VLOOKUP` 类似，我们需要明确指定是否要精确匹配。
• `INDEX-MATCH` 允许对匹配类型进行更多控制，从而在精确匹配、近似匹配或通配符匹配之间提供灵活性。

4. 错误处理

• `XLOOKUP` 具有内置错误处理。如果找不到匹配项，它会返回错误，但我们可以指定一个默认值或自定义错误消息。
• `INDEX-MATCH` 可能需要 `IFERROR` 等附加函数来有效处理错误。

5. 数组处理

• `XLOOKUP` 可以原生处理数组，从而更轻松地处理数据范围。
• `INDEX-MATCH` 可能需要数组公式来处理数组。

6. 兼容性

• `XLOOKUP` 函数在 Microsoft Excel 365 和 2019 版本中可用，但在早期版本中可能不可用。
• `INDEX-MATCH` 是一个函数组合，在更广泛的 Microsoft Excel 版本中都可用。

可以得出结论，XLOOKUP 函数更直接、用户友好，并具有内置错误处理；相比之下，INDEX-MATCH 提供了更大的灵活性，并且与更广泛的 Microsoft Excel 版本兼容，使其成为特定场景的首选。