0x00 前言

在 Excel 里，有无数或简单或复杂的函数，有些函数如 COUNT 可能我们天天都会用到，有些函数则是一辈子都不会碰到的陌生人，爱情也是如此。

而在那些复杂一点的函数里，最广为人知的函数可能就是 VLOOKUP 函数了，这个函数的作用很简单也很强大，就是从表格里找出匹配的的数据。用严肃的语言来说就是：「在数组第一列中查找，然后在行之间移动以返回单元格的值」。

个人认为是否可以使用 VLOOKUP 与 HLOOKUP 函数，大抵是一个人是否对 Excel「略知一二」的分界线。

但 VLOOKUP 函数虽然简单、易用、强大，却仍然有一些呆板的地方，这也很正常，在计算机程序里，「简单易用」往往「灵活多变」的反义词。VLOOKUP 函数的「查阅值」必须位于单元格区域的第一列，这让我们在表格中使用 VLOOKUP 函数时，不得不按照自己的需求重新组织单元格区域的结构，以让 VLOOKUP 函数可以正常工作。

那有没有方法可以让我们在达到 VLOOKUP 函数功能的同时，又可以避开这个「查阅值必须在首列」的白璧微瑕呢？答案就是使用「INDEX 和 MATCH 函数的组合」，我将在下文具体说明这个技巧。

0x01 重温 VLOOKUP 函数

一言以蔽之，VLOOKUP 函数的语法为：

=VLOOKUP（要查找的值、要在其中查找值的区域、区域中包含返回值的列号、精确匹配或近似匹配 – 指定为 0/FALSE 或 1/TRUE）

也就是说，需要下面四条信息才能正确的使用 VLOOKUP 语法：

1. 要查找的值，也被称为查阅值。
2. 查阅值所在的区域。请记住，查阅值应该始终位于所在区域的第一列，这样 VLOOKUP 才能正常工作。例如，如果查阅值位于单元格 C2 内，那么您的区域应该以 C 开头。
3. 区域中包含返回值的列号。例如，如果指定 B2：D11 作为区域，那么应该将 B 算作第一列，C 作为第二列，以此类推。
4. （可选）如果需要返回值的近似匹配，可以指定 TRUE；如果需要返回值的精确匹配，则指定 FALSE。如果没有指定任何内容，默认值将始终为 TRUE 或近似匹配。

具体细节详见 Office 官方支持文档：https://support.office.com/zh-cn/article/vlookup-%E5%87%BD%E6%95%B0-0bbc8083-26fe-4963-8ab8-93a18ad188a1

0x02 INDEX 和 MATCH 函数简介

VLOOKUP 函数的执行过程这样的两步：在表格区域的一列中查找一个数据→定位到该数据所在行中的另一个数据并返回值，这两步之间紧密连接，易用的同时也造成了严格的语法要求。如果我们把这个步骤拆开，那就能让这个过程变得灵活许多。

也就是说，我们首先「在表格区域的一列中查找一个数据」，从而得到这个数据所在行的索引值，之后，我们再「定位到该行中的另一个数据」，并返回值即可。完成第一步的函数是 MATCH 函数，它可以在范围单元格中搜索特定的项，然后返回该项在此区域中的相对位置；完成第二步的函数是 INDEX 函数，它可以按照行列坐标定位并返回表格或区域中的值或值的引用。

下面我们先看一个具体的例子，至于 INDEX 和 MATCH 函数的语法是什么，可以在文章末的附录中查询到。

0x03 实际应用举例

编号	姓名	工资	科室
2005001	张三	2870	办公室
2005002	李四	2750	人事科
2005006	郑化	2680	供应科
2005010	屠刚红	2980	销售科
2005019	王五	2530	财务科
2005036	孟小庭	2200	工会

问题甲

比如说，我们现在有一个这样的 Excel 表格，里面记载了公司员工的工号、姓名、工资和科室，现在已知一个人的姓名是「王五」，需要查出他的工资。

即使我们用 VLOOKUP 函数，这个问题也很好解决，因为「查询值」姓名是处于「被查询内容」工资的左边的，我们可以直接选中 B1:D7 的区域，查询即可：

=VLOOKUP("王五", B2:D7, 2, FALSE)

然后就可以得到结果了，王五的工资是 2530 元：

如果用 INDEX 函数和 MATCH 函数的组合改写，改如何做呢？首先需要用 MATCH 函数查出「王五」到底在数据的第几行：

=MATCH("王五", B1:B7, 0)

得到结果，位于第 6 行：

那么，下一步就是在 A1:D7 的单元格范围内，找到第六行第 C 列的数据，那就是王五的工资，直接把 MATCH("王五", B1:B7, 0) 的返回值带入 INDEX 函数的第二个参数即可。

=INDEX(A1:D7, MATCH("王五", B1:B7, 0), 3)

也顺利得到了结果，王五的工资为 2530 元：

结果和上面用 VLOOKUP 得到的结果是一样的。这时我们可能觉得，这个和 VLOOKUP 函数效果一样，写起来却麻烦了不少，不太值当。但下一个例子 VLOOKUP 函数就无能为力了。

问题乙

还是刚才那个表格，我们的问题变成，已知王五的名字，需要得到他的工号。

这下 VLOOKUP 函数就尴尬了，它要求「查询值」必须位于「选定区域」的首列，但因为工号列在姓名列的左侧，所以不管如何选择区域，姓名列都不可能处于首列。那我们需要重新剪切一下，把姓名列移动到工号列的左侧吗？确实可以这么做，不过有些麻烦，尤其是当表格的规模复杂、庞大后，牵一发而动全身，列与列之间的关系并不好调整。

但这时候，我们仍然可以用 INDEX 和 MATCH 函数，以着和「问题甲」中相同的过程解决这个问题。只需要把「问题甲」中的公式中 INDEX 函数的第三个参数换成 1 就行：

=INDEX(A1:D7, MATCH("王五", B1:B7, 0), 1)

然后就顺利得到了结果，王五的工号是 2005019：

一个对 VLOOKUP 函数而言有些棘手的问题，就这样游刃有余地解决了，这就是 INDEX 和 MATCH 函数组合的灵活强大之处。他们的组合还有许多用法，我这里不过是抛砖引玉的举了一个小例子，大家可以再结合附录中的详细语法尝试尝试。

0x04 附录：VLOOKUP、INDEX、MATCH 函数详细语法

他们的详细语法分别为：

VLOOKUP

语法

VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])

例如：

• =VLOOKUP(105,A2:C7,2,TRUE)

• =VLOOKUP("袁",B2:E7,2,FALSE)

参数名称	说明
lookup_value   （必需参数）	要查找的值。要查找的值必须位于 table-array 中指定的单元格区域的第一列中。 例如，如果 table-array 指定的单元格为 B2:D7，则 lookup_value 必须位于列 B 中。请参见下图。Lookup_value 可以是值，也可以是单元格引用。
Table_array   （必需参数）	VLOOKUP 在其中搜索 lookup_value 和返回值的单元格区域。 该单元格区域中的第一列必须包含 lookup_value（例如，下图中的 “姓氏”）。此单元格区域中还需要包含您要查找的返回值（例如，下图中的 “名字”）。 了解如何选择工作表中的区域。
col_index_num   （必需参数）	其中包含返回值的单元格的编号（table-array 最左侧单元格为 1 开始编号）。
range_lookup   （可选参数）	一个逻辑值，该值指定希望 VLOOKUP 查找近似匹配还是精确匹配： TRUE 假定表中的第一列按数字或字母排序，然后搜索最接近的值。这是未指定值时的默认方法。 FALSE 在第一列中搜索精确值。

MATCH 函数

MATCH(lookup_value, lookup_array, [match_type])

MATCH 函数语法具有下列参数：

• lookup_value    必需。要在 lookup_array 中匹配的值。例如，如果要在电话簿中查找某人的电话号码，则应该将姓名作为查找值，但实际上需要的是电话号码。lookup_value 参数可以为值（数字、文本或逻辑值）或对数字、文本或逻辑值的单元格引用。
• lookup_array    必需。要搜索的单元格区域。
• match_type    可选。数字 -1、0 或 1。match_type 参数指定 Excel 如何将 lookup_value 与 lookup_array 中的值匹配。此参数的默认值为 1

• 下表介绍该函数如何根据 match_type 参数的设置查找值。

Match_type	行为
1 或省略	MATCH 查找小于或等于 lookup_value 的最大值。lookup_array 参数中的值必须以升序排序，例如：...-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE。
0	MATCH 查找完全等于 lookup_value 的第一个值。lookup_array 参数中的值可按任何顺序排列。
-1	MATCH 查找大于或等于 lookup_value 的最小值。lookup_array 参数中的值必须按降序排列，例如：TRUE, FALSE, Z-A, ...2, 1, 0, -1, -2, ... 等等。

• MATCH 返回匹配值在 lookup_array 中的位置，而非其值本身。例如，MATCH("b",{"a","b","c"},0)返回 2，即 “b” 在数组 {"a","b","c"} 中的相对位置。
• 匹配文本值时，MATCH 函数不区分大小写字母。
• 如果 MATCH 函数查找匹配项不成功，它会返回错误值 #N/A。
• 如果 match_type 为 0 且 lookup_value 为文本字符串，您可在 lookup_value 参数中使用通配符 - 问号 (?) 和星号 (*) 。问号匹配任意单个字符；星号匹配任意一串字符。如果要查找实际的问号或星号，请在字符前键入波形符 (~)。

INDEX 函数

INDEX(array, row_num, [column_num])

INDEX 函数语法具有下列参数：

• Array    必需。单元格区域或数组常量。
  • 如果数组只包含一行或一列，则相对应的参数 Row_num 或 Column_num 为可选参数。
  • 如果数组有多行和多列，但只使用 Row_num 或 Column_num，函数 INDEX 返回数组中的整行或整列，且返回值也为数组。
• Row_num    必需。选择数组中的某行，函数从该行返回数值。如果省略 Row_num，则必须有 Column_num。
• Column_num    可选。选择数组中的某列，函数从该列返回数值。如果省略 Column_num，则必须有 Row_num。