通常，第一次学习使用 Excel 时，人们多半会把 VLOOKUP 作为数据整形的主要方法来介绍。VLOOKUP 确实是一项强大的函数，能够处理诸多与数据处理相关的任务，这一点已在 在电子表格中使用 VLOOKUP 进行数据整形 中有所展示。在同一篇教程中，我们也讲到了 INDEX-MATCH 组合的基础知识，这是一种执行纵向表格查找的替代方法，相比 VLOOKUP 有多项优势，但代价是公式更为复杂。
这些优势包括以下特性：
- 动态列引用： 允许您在不影响查找函数的情况下，安全地在数据集中插入和删除列。
- 在大型数据集中的更高处理速度： 如果您的表格非常大，您很可能会注意到 INDEX-MATCH 比 VLOOKUP 更快，因为 INDEX-MATCH 只关心您要返回的列和进行查找的列。
- 查找值的位置： VLOOKUP 无法查找您在表格范围中所选第一列左侧的任何值，而 INDEX-MATCH 也可以横向查找，因此没有这个限制。
- 查找值长度限制： VLOOKUP 无法查找超过 255 个字符的值，但 INDEX-MATCH 可以。
此外，INDEX-MATCH 还支持基于多列值进行匹配，以及区分大小写的匹配。这在处理可能因大小写不同而区分的某些 ID 时非常有用。因此，本教程将深入探讨 INDEX-MATCH 组合的高级功能，并通过示例展示这些功能如何派上用场。
INDEX-MATCH 基础
在最简单的用法中，INDEX-MATCH 可以像 VLOOKUP 一样，用于基于共同键执行简单的纵向表格查找。其公式的基本结构如下：
=INDEX(column_range, MATCH(lookup_value, lookup_column_range, match_type))
- column_range： 您希望返回其值的列范围。
- lookup_value： 您要查找的值（键）。
- lookup_column_range： 包含您要匹配的键的列范围。
- match_type： 0 或 1。0 表示精确匹配，1 表示近似匹配。这与 VLOOKUP 中的 FALSE/TRUE 标志类似。
接下来，我们用两个示例表来说明 INDEX-MATCH 的工作方式。第一个表包含一些姓名和唯一键。第二个表包含相同的键（顺序不一定相同）以及一些描述。我们的任务是使用 INDEX-MATCH 将第二个表中的描述与第一个表中的姓名对应起来。如下所示：
如您所见，第一个表缺少描述。现在让我们用 INDEX-MATCH 将姓名与其描述关联起来：
很简单，对吧？如视频所示，第一步是在 INDEX 函数中选择包含我们要返回数据的 column_range。这里是 Description（M2:M8）。第二步是在 MATCH 函数中选择 lookup_value，即单元格 B2。最后，在 MATCH 函数中加入 lookup_column_range（L2:L8），并将 match_type 设为 0，因为我们需要对 lookup_value 进行精确匹配。剩下的就是按下回车并向下拖动公式，就能把姓名与描述对应起来。
与 VLOOKUP 一样，INDEX-MATCH 也可以用于连接位于不同工作表上的表。示例视频如下：
注意： 您可能注意到我在视频中选择的列范围通常带有 $ 符号。这样做是为了选择固定范围，使其在垂直或水平拖动公式时不会发生变化。本教程中我将持续使用这种表示法。
到目前为止，这些用法与简单的 VLOOKUP 非常相似，但稍微繁琐一些。然而，正如本教程开头所提到的，如果采用 INDEX-MATCH，有多项优势。其中最有用的一点就是动态列引用。也就是说，允许您在执行查找的表范围内添加列，而不会影响查找本身。
如果我们在示例中新增一列，并且使用的是 VLOOKUP，就必须重写左侧表格中的公式以引用描述列（它将变为第 3 列）。然而，如果您使用具有动态列引用的 INDEX-MATCH，就不需要这样做。下面的视频展示了这种对比：
上面的示例说明，当加入包含飞船类型的新列时，使用 INDEX-MATCH 进行查找的那一列能够自动调整，而使用 VLOOKUP 的那一列则无法做到。在处理大型 Excel 表（如业务销售报告）中的查找任务时，INDEX-MATCH 相对于 VLOOKUP 的这一优势尤为明显。换句话说，假设您在 Excel 中构建了多个业务看板，它们都引用同一张总数据表。如果您使用的是 VLOOKUP，而某天需要在中间插入一列以添加新的字段，那么所有依赖该表的看板中的 VLOOKUP 调用都得修改。但如果您从一开始就采用 INDEX-MATCH 作为查找方法，就无需面对这种麻烦。
高级 INDEX-MATCH：区分大小写的匹配
动态列引用非常实用，但这并不是 INDEX-MATCH 的全部优势。例如，我们可以在基本语法的基础上增强 INDEX-MATCH，实现区分大小写的查找。具体做法是在 MATCH 函数中加入 EXACT 函数。详细语法如下：
{=INDEX(data_range, MATCH(TRUE,EXACT(lookup_value, lookup_column_range), match_type), desired_column_number)}
- data_range： 包含查找列和目标返回列的表格范围。
- lookup_value： 您要查找的值（键）。
- lookup_column_range： 包含您要匹配的键的列范围。
- match_type： 0 或 1。0 表示精确匹配，1 表示近似匹配。这与 VLOOKUP 中的 FALSE/TRUE 标志类似。
- desired_column_number： 与 VLOOKUP 的列序号相同，即包含您要返回数据的列序号。
使用这种改造后的 INDEX-MATCH 进行区分大小写的匹配，在匹配包含字母且以字母大小写区分的键时非常有用（例如有两个不同的键，一个是 "00567UUp"，另一个是 "00567UUP"）。例如，在广泛使用的 Salesforce CRM 中匹配账户 ID 或销售机会 ID 时，就可能遇到这种情况。此处我们不使用 Salesforce 数据集，而是用下面视频中的改造版示例表来展示上述公式的实际效果。
注意： 上述函数组合被花括号包裹，这意味着它是一个数组公式，必须通过同时按下 Cntr+Shift+Enter 输入。
可以看到，这个公式的语法写起来可能比较长，但结果正如预期。它与普通的 INDEX-MATCH 一样进行查找，但会考虑字符的大小写。这里的关键是 EXACT 函数，它返回由 TRUE 和 FALSE 组成的数组。数组中为 TRUE 的位置表示存在大小写完全一致的值。之后，MATCH 函数会取得第一个 TRUE 出现的位置。
请注意，完成公式输入时务必按下 Cntrl+Shift+Enter，否则会得到一个难以排查的 #N/A 错误。
高级 INDEX-MATCH：基于多条件的匹配
在 Excel 中实现区分大小写的查找很酷，但还有一个更强大的功能：基于多条件的查找。考虑多个条件在 COUNTIFS 或 SUMIFS 等条件计数或求和函数中很常见，但在 Excel 的查找函数中则比较少见。然而，在某些场景下这非常有用。比如，您有一份人力资源（HR）Excel 文件，想根据员工的名和姓两者来查找该员工的薪资。如果只考虑其中一列，可能会因为重名而匹配到错误的员工；而同时基于名和姓来返回值，匹配到正确人的可能性就大得多。
话不多说，来看一下考虑多条件时的 INDEX-MATCH 结构：
{=INDEX(column_range, MATCH(1,(lookup_value_1 = lookup_column_range_1)*(lookup_value_2 = lookup_column_range_2)*...(lookup_value_n = lookup_column_range_n), match_type))}
大体而言，该公式与普通的 INDEX-MATCH 由完全相同的组件构成，但现在可以同时考虑 n 个查找值及其对应的 n 个查找范围。如果您需要回顾这些组件的含义，可以上滚至 INDEX-MATCH 的基本语法。MATCH 函数中之所以使用 "1"，是因为该公式会生成由 1 和 0 组成的数组，表示每个条件是否匹配。随后利用 MATCH 函数返回第一个 1 出现的位置。
与区分大小写的公式一样，这里也使用了花括号，意味着在完成公式输入时必须按下 Cntr+Shift+Enter，因为这是一个数组公式。
下面我们通过一个示例来看看该公式的实际效果：在视频中尝试选择属于某个种族且具有给定最大曲速的飞船：
搞定！现在您已经掌握了基于多个键进行匹配的工具。请注意，在这个小示例中我只使用了两个条件——键和最大曲速，但理论上您可以继续添加条件。回到 HR 的例子，您可以基于名、姓以及职位来匹配，以尽量减少得到错误员工薪资的可能性。
结语
恭喜您！您已经熟悉了 INDEX-MATCH，以及它在简单的纵向查找之外所能完成的多种任务。如果有一天您需要处理复杂的 Excel 报表，这一函数组合很可能会成为您的得力助手。和许多人一样，我在学校学的是 VLOOKUP，但当我认真地在 Excel 中处理诸如 Salesforce 账户 ID 的区分大小写查找等任务时，我意识到从长远看 INDEX-MATCH 是更好的选择，尽管编写公式更繁琐、也更容易出错。我鼓励您下次在 Excel 中进行纵向查找时试一试，也许您会像我一样喜欢上它。
如果您还想继续深入学习，欢迎查看 DataCamp 的 电子表格数据分析课程。
%22%3E%3Cpath%20d%3D%22m-369%200h408v79h-408z%22%20fill%3D%22%237933ff%22%2F%3E%3Cpath%20d%3D%22m-148%200h306v45h-306z%22%20fill%3D%22%23974dff%22%2F%3E%3C%2Fg%3E%3C%2Fsvg%3E)
%22%2F%3E%3C%2FclipPath%3E%3Cg%20clip-path%3D%22url(%23a)%22%3E%3Cpath%20d%3D%22m76%200h484v36h-484z%22%20fill%3D%22%23974dff%22%2F%3E%3Cpath%20d%3D%22m0%200h384v24h-384z%22%20fill%3D%22%2306bdfc%22%20transform%3D%22matrix(1%200%200%20-1%200%2060)%22%2F%3E%3C%2Fg%3E%3C%2Fsvg%3E)
通常，第一次学习使用 Excel 时，人们多半会把 VLOOKUP 作为数据整形的主要方法来介绍。VLOOKUP 确实是一项强大的函数，能够处理诸多与数据处理相关的任务，这一点已在 在电子表格中使用 VLOOKUP 进行数据整形 中有所展示。在同一篇教程中，我们也讲到了 INDEX-MATCH 组合的基础知识，这是一种执行纵向表格查找的替代方法，相比 VLOOKUP 有多项优势，但代价是公式更为复杂。
这些优势包括以下特性：
此外，INDEX-MATCH 还支持基于多列值进行匹配，以及区分大小写的匹配。这在处理可能因大小写不同而区分的某些 ID 时非常有用。因此，本教程将深入探讨 INDEX-MATCH 组合的高级功能，并通过示例展示这些功能如何派上用场。
INDEX-MATCH 基础
在最简单的用法中，INDEX-MATCH 可以像 VLOOKUP 一样，用于基于共同键执行简单的纵向表格查找。其公式的基本结构如下：
=INDEX(column_range, MATCH(lookup_value, lookup_column_range, match_type))接下来，我们用两个示例表来说明 INDEX-MATCH 的工作方式。第一个表包含一些姓名和唯一键。第二个表包含相同的键（顺序不一定相同）以及一些描述。我们的任务是使用 INDEX-MATCH 将第二个表中的描述与第一个表中的姓名对应起来。如下所示：
如您所见，第一个表缺少描述。现在让我们用 INDEX-MATCH 将姓名与其描述关联起来：
很简单，对吧？如视频所示，第一步是在 INDEX 函数中选择包含我们要返回数据的 column_range。这里是 Description（M2:M8）。第二步是在 MATCH 函数中选择 lookup_value，即单元格 B2。最后，在 MATCH 函数中加入 lookup_column_range（L2:L8），并将 match_type 设为 0，因为我们需要对 lookup_value 进行精确匹配。剩下的就是按下回车并向下拖动公式，就能把姓名与描述对应起来。
与 VLOOKUP 一样，INDEX-MATCH 也可以用于连接位于不同工作表上的表。示例视频如下：
注意： 您可能注意到我在视频中选择的列范围通常带有 $ 符号。这样做是为了选择固定范围，使其在垂直或水平拖动公式时不会发生变化。本教程中我将持续使用这种表示法。
到目前为止，这些用法与简单的 VLOOKUP 非常相似，但稍微繁琐一些。然而，正如本教程开头所提到的，如果采用 INDEX-MATCH，有多项优势。其中最有用的一点就是动态列引用。也就是说，允许您在执行查找的表范围内添加列，而不会影响查找本身。
如果我们在示例中新增一列，并且使用的是 VLOOKUP，就必须重写左侧表格中的公式以引用描述列（它将变为第 3 列）。然而，如果您使用具有动态列引用的 INDEX-MATCH，就不需要这样做。下面的视频展示了这种对比：
上面的示例说明，当加入包含飞船类型的新列时，使用 INDEX-MATCH 进行查找的那一列能够自动调整，而使用 VLOOKUP 的那一列则无法做到。在处理大型 Excel 表（如业务销售报告）中的查找任务时，INDEX-MATCH 相对于 VLOOKUP 的这一优势尤为明显。换句话说，假设您在 Excel 中构建了多个业务看板，它们都引用同一张总数据表。如果您使用的是 VLOOKUP，而某天需要在中间插入一列以添加新的字段，那么所有依赖该表的看板中的 VLOOKUP 调用都得修改。但如果您从一开始就采用 INDEX-MATCH 作为查找方法，就无需面对这种麻烦。
高级 INDEX-MATCH：区分大小写的匹配
动态列引用非常实用，但这并不是 INDEX-MATCH 的全部优势。例如，我们可以在基本语法的基础上增强 INDEX-MATCH，实现区分大小写的查找。具体做法是在 MATCH 函数中加入 EXACT 函数。详细语法如下：
{=INDEX(data_range, MATCH(TRUE,EXACT(lookup_value, lookup_column_range), match_type), desired_column_number)}使用这种改造后的 INDEX-MATCH 进行区分大小写的匹配，在匹配包含字母且以字母大小写区分的键时非常有用（例如有两个不同的键，一个是 "00567UUp"，另一个是 "00567UUP"）。例如，在广泛使用的 Salesforce CRM 中匹配账户 ID 或销售机会 ID 时，就可能遇到这种情况。此处我们不使用 Salesforce 数据集，而是用下面视频中的改造版示例表来展示上述公式的实际效果。
注意： 上述函数组合被花括号包裹，这意味着它是一个数组公式，必须通过同时按下 Cntr+Shift+Enter 输入。
可以看到，这个公式的语法写起来可能比较长，但结果正如预期。它与普通的 INDEX-MATCH 一样进行查找，但会考虑字符的大小写。这里的关键是 EXACT 函数，它返回由 TRUE 和 FALSE 组成的数组。数组中为 TRUE 的位置表示存在大小写完全一致的值。之后，MATCH 函数会取得第一个 TRUE 出现的位置。
请注意，完成公式输入时务必按下 Cntrl+Shift+Enter，否则会得到一个难以排查的 #N/A 错误。
高级 INDEX-MATCH：基于多条件的匹配
在 Excel 中实现区分大小写的查找很酷，但还有一个更强大的功能：基于多条件的查找。考虑多个条件在 COUNTIFS 或 SUMIFS 等条件计数或求和函数中很常见，但在 Excel 的查找函数中则比较少见。然而，在某些场景下这非常有用。比如，您有一份人力资源（HR）Excel 文件，想根据员工的名和姓两者来查找该员工的薪资。如果只考虑其中一列，可能会因为重名而匹配到错误的员工；而同时基于名和姓来返回值，匹配到正确人的可能性就大得多。
话不多说，来看一下考虑多条件时的 INDEX-MATCH 结构：
{=INDEX(column_range, MATCH(1,(lookup_value_1 = lookup_column_range_1)*(lookup_value_2 = lookup_column_range_2)*...(lookup_value_n = lookup_column_range_n), match_type))}大体而言，该公式与普通的 INDEX-MATCH 由完全相同的组件构成，但现在可以同时考虑 n 个查找值及其对应的 n 个查找范围。如果您需要回顾这些组件的含义，可以上滚至 INDEX-MATCH 的基本语法。MATCH 函数中之所以使用 "1"，是因为该公式会生成由 1 和 0 组成的数组，表示每个条件是否匹配。随后利用 MATCH 函数返回第一个 1 出现的位置。
与区分大小写的公式一样，这里也使用了花括号，意味着在完成公式输入时必须按下 Cntr+Shift+Enter，因为这是一个数组公式。
下面我们通过一个示例来看看该公式的实际效果：在视频中尝试选择属于某个种族且具有给定最大曲速的飞船：
搞定！现在您已经掌握了基于多个键进行匹配的工具。请注意，在这个小示例中我只使用了两个条件——键和最大曲速，但理论上您可以继续添加条件。回到 HR 的例子，您可以基于名、姓以及职位来匹配，以尽量减少得到错误员工薪资的可能性。
结语
恭喜您！您已经熟悉了 INDEX-MATCH，以及它在简单的纵向查找之外所能完成的多种任务。如果有一天您需要处理复杂的 Excel 报表，这一函数组合很可能会成为您的得力助手。和许多人一样，我在学校学的是 VLOOKUP，但当我认真地在 Excel 中处理诸如 Salesforce 账户 ID 的区分大小写查找等任务时，我意识到从长远看 INDEX-MATCH 是更好的选择，尽管编写公式更繁琐、也更容易出错。我鼓励您下次在 Excel 中进行纵向查找时试一试，也许您会像我一样喜欢上它。
如果您还想继续深入学习，欢迎查看 DataCamp 的 电子表格数据分析课程。