本文介绍 VLOOKUP 函数的单条件匹配和多条件匹配，IF 函数运算逻辑，CHOOSE 函数用法，以及数组表达式的用法。

系列教程索引和配套练习文件，请点这里。

设想一个简单的任务，给定数据源表格（其中最左上角坐标 A1）：

在如下的查询表格中填空（其中最左上角坐标 F1）：

毋庸置疑，使用 VLOOKUP 函数。

VLOOKUP

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

• lookup_value：查询值；
• table_array：作用区域，其中第一列作为搜索查询值的查询列；
• col_index_num：引用列的序号，从 1 开始，1 表示作用区域的第一列；
• range_lookup：TRUE 为模糊查询，FALSE 为精确查询。

函数返回值为，在作用区域中的第 x 行，第 y 列的值，如果找到的话。其中 x 为作用区域第一列中，由上至下首个与查询值相等的值，在作用区域中的行数；y 由 col_index_num 指定。

说白了就是，在 table_array 第一列中，搜索 lookup_value，若找到，则在 table_array 中找到 lookup_value 的这一行里，选取给定的第 col_index_num 列的值。

则，在张三的班级处（G2），填写公式

=VLOOKUP(F2, $A$1:$D$6, 2, FALSE)

注意

• table_array 采用了绝对引用，因为在将 G2 的公式粘贴到其他单元格中的时候，作用区域始终是 A1:D6 这个范围，不随着填空行列的变化而变化。
• col_index_num 值为 2，因为班级这一列在作用区域中是第 2 列；同理在张三的年龄空白处（H2），应将 VLOOKUP 的 col_index_num 参数修改为 3，因为在作用区域中，年龄是第 3 列。

将 G2 和 H2 处的公式向下填充三行，即可获得如下的结果：

这就是 VLOOKUP 函数的最基本应用，单条件匹配。

如果有两个人叫张三怎么办？首先假设两人在不同的班级（左上角坐标 A1）：

可以发现，此时必须至少凭借姓名和班级（或者年龄、性别）两个值才能唯一确定张三到底是指哪个人。习惯上，姓名和班级是已知信息，年龄和性别作为填空信息出现。

例如，在如下查询表中填空（左上角坐标 F1）：

如何解决？思路有二。

其一，在源数据表中插入一列，另这一列的内容是两个识别值的加总，例如“张三1、张三4”或，或者“张三-1、张三-4”等，形式不限；额外在查询表中插入一列，将两个查询值按同样的格式合并。这样实现了降维的处理，使多条件查询变回了单条件查询，直接应用 VLOOKUP 即可。但是这样的操作除了显得繁琐，还会破坏表格的结构，留下了冗余的信息。

其二，用 VLOOKUP 函数进行多条件匹配。

然而 VLOOKUP 并不支持多条件查询。怎么办呢？使用 CHOOSE 函数和数组表达式构建出 VLOOKUP 的数据结构，然后进行普通查询，听着好像欺骗了 VLOOKUP 一样。

Excel 公式中，数组用大括号表示。数组内的元素，既可以是值，也可以是数组，即嵌套数组。而数组整体被当做一个元素来看待，这使得数组成为沟通单个参数与多个数据值的桥梁。类似 Python 中的嵌套列表。

对于数据结构

a = {100, 200, 300, 350}
b = {a, 400}
c = {"a", 500}

来说，有

a[1] = 100
a[3] = 300

b[1] = a = {100, 200, 300, 350}
b[1][4] = a[4] = 350
b[2] = 400

c[1] = "a"
c[2] = 500

遗憾的是 Excel 的公式里无法直接这样使用数据，但是针对这个样子的数据，它的运算逻辑是完善的。所以在使用数组时务必要做到对数据结构心中有数。

另外注意，Excel 中的索引下界是 1，不是 0。即数组中的第一个元素索引是 1， 第二个是 2，以此类推。

利用 CHOOSE 函数和数组表达式构建出 VLOOKUP 所使用的数据结构。

CHOOSE

CHOOSE(index_num, value1, [value2], ...)

• index_num：要返回的值的索引；
• value1/2/...：第几个值。

函数返回值为，value1、value2、……中的第 index_num 个。

回过头来看 VLOOKUP 的 table_array 参数，这个参数指定一个作用区域，其实就是一个二维表格，其中第一列存放着要搜索的信息，其余列存放着要取用的信息。那么我们就可以用 CHOOSE 函数配合数组表达式，构建出这张二维表的数据结构，另其第一列为姓名和班级拼接后的条件。从过程上看是把手动的显式合并变为自动的隐式合并，从结果上看是将多条件匹配降维到单条件匹配。

使用如下语句构建合并多个条件后的作用区域数据结构：

CHOOSE({1, 2}, $A$2:$A$7&$B$2:$B$7, $C$2:$C$7)

上述语句形成如下数据结构中的 A&B 和 C 两部分，左边三列辅助理解。

|Row|A|B|A&B|C|
|:-:|:-:|:-:|:-:|:-:|:-:|
|2|张三|1|张三1|18|
|3|张三|4|张三4|19|
|4|李四|3|李四3|17|
|5|王五|2|王五2|20|
|6|周六|1|周六1|19|
|7|赵七|2|赵七2|17|

说明

• CHOOSE 函数的第一个参数 index_num 只允许一个元素，而我们需要让 CHOOSE 函数的返回值作为 VLOOKUP 的 table_array，应该是一个表格，所以采用了数组的形式，返回一个两列的表格。
• {1, 2} 中，1 表示取用 value1 的值，而 value1 本身也是数组，其值是将 A 列和 B 列拼接起来的 A&B 列的内容，这部分作为搜索区域；2 表示取用信息的区域，也即年龄列。
• & 符号用来横向拼接两列，A2&B2 的值是“张三1”，A3&B3 的值是“张三4”；A2:A7&B2:B7 这种左右都是区域的用法，是对每一行进行横向拼接后，再放到一起作为一列，即 A&B 列中的样子。
• 此处绝对引用的原因与前文相同。

最终，CHOOSE 函数所生成的数据结构如下（不包含第一行）：

此时，VLOOKUP 的 table_array 参数所需的数据结构已经构建完毕，可以直接按照单条件匹配的语法用 VLOOKUP 了。

在 1 班张三的年龄（H2）处，填写如下公式：

=VLOOKUP(F2&G2, CHOOSE({1, 2}, $A$2:$A$7&$B$2:$B$7, $C$2:$C$7), 2, FALSE)

来获得该同学的年龄。

注意

• VLOOKUP 的第一个参数 lookup_value 也采用了 & 符号拼合的方式，因为要把搜索值和搜索区域的格式相统一。
• CHOOSE 函数生成了 table_array 所指代的作用区域，也即上文的数据表，其中第一列用于搜索 lookup_value。
• col_index_num 的值为 2，也即在 CHOOSE 所生成的表中，取用第二列的信息。

将 H2 处的公式向下填充三行，即可获得最终结果：

这里特别说明，网上盛传使用 IF 函数来构建作用区域：

IF({1, 0}, $A$2:$A$7&$B$2:$B$7, $C$2:$C$7)

这种做法本质上与 CHOOSE 函数是一样的，IF 函数原型为

IF

IF(logical_test, [value_if_true], [value_if_false])

• logical_test：逻辑表达式；
• value_if_true：logical_test 为真时的返回值；
• value_if_false：logical_test 为假时的返回值。

函数返回值如上所述。

所以 {1, 0} 中的 1 表示把 value_if_true 部分的数据作为返回值元素的第一项，也即二维表格的第一列，搜索区域；0 表示把 value_if_false 部分的数据作为返回值元素的第二项，也即二维表的第二列，取用区域。

我不推荐这种写法，因为：意思表达不直观；1 和 0 逆序排列容易引起疏忽性错误；最多只支持返回两列的表格，而 CHOOSE 可以返回更多列的表格。

说明一点，在公式中使用数组与编写数组公式不同，直接按 Enter 键应用公式即可，不需要 Ctrl + Shift + Enter 创造数组公式。当然，写成数组公式也能生效，就是没必要罢了。

最后，把这些技术拿到案例中来，通过练习文件实操一下。

打开 Example 1.xlsx，我们的目标是将 Template 工作表中的一级分类和二级分类补齐，根据 Genre 工作表里的分类体系进行匹配。

Example 1.xlsx 的 Tamplate 工作表初始状态

由于三级分类在设计时已经保证了每一条的唯一性，所以可以直接用来作为搜索值。这里有一个小细节值得思考，为什么单凭三级分类一个条件就可以唯一确定一个分类，却还是要用到 CHOOSE 函数来构建作用区域呢？因为 VLOOKUP 函数固定地把作用区域第一列当做搜索区域，而在对 Genre 工作表中分类的引用中，三级分类排在第三列，第一列的一级分类被当做搜索区域，这自然是无法成功匹配的。所以此处使用 CHOOSE 实际上是调整三级分类的位置，从数据结构上把它挪到第一列去，充当搜索区域。

Example 1.xlsx 的 Genre 工作表

在 Template 工作表的 A3 位置输入公式

=VLOOKUP($C3, CHOOSE({1, 2}, Genre!$C$2:$C$121, Genre!A$2:A$121), 2, FALSE)

在 Template 工作表的 A3 位置填写公式

将其填充满 A3:B8 区域，即可看到一级分类和二级分类被自动补齐。注意这里几处相对引用、绝对引用和部分相对部分绝对引用的组合。

最终效果

如此便完成了 Example 2.xlsx。

下一课中，将介绍如何让表格支持分类标准的动态增减。