第2篇 计算数据
这3个公式也能得出正确的结果，CHOOSE函数构建内存数组的效果同IF{1,0}，这 里不再详细讲解。
职场点睛：困扰很多用户的反向查找问题就这样解决了，强烈建议用户多尝试一下这 个函数套路，学会这一招，今后在工作中遇到类似问题无需每次再构建辅助列，一个公式 就能搞定。
4．解决方案2
使用IF{1,0}构建内存数组的方法很经典，但理解有一点难度。查找的问题并非一定要
F5 单元格录入如下公式： 通过 VLOOKUP 函数来解决，使用其他函数也能轻松搞定。在
MATCH 函数，作用是“定位”，在单元格区 公式解析：对于这个公式，里层是一个
域中搜索指定项，然后返回该项在单元格区域中的相对位置（返回值是一个数值）。公式 各参数解释如表5.2所示。
表5.2 MATCH函数3个参数的理解
参数顺序 第1参数 第2参数 第3参数 参数含义 要查找的值 查找的区域 查找模式 0 E5 如第3参数是0则区域第1列不需要排序；如是1需要 B2:B10 对区域的第1列升序排序；如是-1则需要对对区域的第 1列降序排序 为0时精确匹配，如为1或者-1则是模糊匹配 上例中的取值=INDEX(A2:A10,MATCH(E5,B2:B10,0)) =VLOOKUP(E5,IF({0,1},A2:A10,B2:B10),2,) =VLOOKUP(E5,CHOOSE({1,2},B2:B10,A2:A10),2,) =VLOOKUP(E5,CHOOSE({2,1},A2:A10,B2:B10),2,)
注 意 事 项 ，按 F9 键观 读者可以在编辑栏中选中公式的 MATCH 部分“ MATCH(E5,B2:B10,0)”察返回值，返回结果如图5.7所示。
图5.7 INDEX+MATCH函数运算过程
公式的外层是一个INDEX函数，我们可以把INDEX函数的第1参数理解为一个矩形
2 区域，函数的结果是返回矩形区域中的某个值，具体返回哪一个值则由该函数的第 、 3
·132 ·
第5章 海量数据分析的利器——初识查找引用函数
参数决定。第2、3参数告诉Excel，返回值在区域中的第几行和第几列。
本例中INDEX函数的第1参数A2:A10仅有一列，可以省略第3参数，根据第2参数 即MATCH函数计算的结果来决定返回值的结果。
读者可在E1单元格的数据有效性下拉列表中任意选择学生姓名，观察F1单元格公式 结果的变化。
5．函数套路——INDEX+MATCH解析
INDEX+MATCH函数组合相当于把原来VLOOKUP一个函数的工作分配给了两个函 数来完成。虽然没有能完成。
回到上一节中使用VLOOKUP精确查找的进销存案例，使用INDEX+MATCH函数组 合也能完成，公式如下： =INDEX($H$1:$H$15,MATCH(A2,$G$1:$G$15,0)) VLOOKUP那么惊艳，但这个函数组合能完成的工作比单一的
VLOOKUP函数要多很多，至少VLOOOKUP能做的工作，INDEX+MATCH函数组合都
这个公式的含义是先在 G 列的“货号”区域 $G$1:$G$13 中查找 A2 单元格中的货号， 然后在H列“销售汇总”区域$H$1:$H$13返回货号对应的销售额。
职场点睛：这个函数套路给我们的启示是：解决问题不要仅局限在一种方法上，多掌 握几个基础的函数对日常工作绝对是有帮助的。
5.2.2 IF{1,0}与INDEX+MATCH方法比较
两种方法都很经典，对于初学者，建议多尝试一下INDEX+MATCH函数的组合，思 路不要总局限在 VLOOKUP 函数上。
对于IF{1,0}，使用时要考虑是否需要使用数组公式（即按Ctrl+Shift+Enter三键结束）。
IF 函数的第 2 和第 3 参数仅是引用了工作 本节的示例是不需要使用数组公式结束的，因为 表中的单元格区域，并没有计算，如在引用的同时还有计算，就需要使用数组公式结束。
本节示例文件：《5.2 突破VLOOKUP函数反向查找的限制.xlsx》。
5.3 轻松应对多条件查找
本章前两节中讲解的示例都属于单条件查找，实际工作中更多的是需返回满足多个条 件的对应值。相对于单条件查找，多条件查找肯定不能简单应用VLOOKUP函数。
5.3.1 多条件查找的两种解决方案
先看一个多条件查找的经典案例，原始数据在
A:C 列， E2 单元格中设置了数据有效
性，取值为产品代码，F2单元格中取值为部门号，需要在G2单元格中设置公式，要求G2
·133·
第2篇 计算数据
单元格中返回值随E2、F2单元格取值的变动而变动，如图5.8所示。
图5.8 一个多条件查找的示例
1．问题分析
这里需要返回满足两个条件时对应的销售额，如果把这两个条件连接起来变成一个条 件，就能回到上两节讲解的单条件查找的问题中去了。如是多个条件方法也一样。
2．解决方案1
使用IF{1,0}构建内存数组，然后使用VLOOKUP函数，在G2单元格中输入如下公式， 并按 Ctrl+Shift+Enter 三键结束：
公式解析：公式中使用了连接符 & ，将两个查找条件 E2 和 F2 单元格连接起来，在 IF 函数构建的查找区域中，也使用连接符&将两个数据区域连接。将IF函数的返回结果放入 单元格区域中，如图5.9所示。
=VLOOKUP(E2&F2,IF({1,0},A2:A9&B2:B9,C2:C9),2,)
图5.9 IF{1,0}中使用连接符&的返回结果
使用连接符&把多条件查找的问题转换成了单条件查找，连接两个条件是关键的一步。 3．解决方案2
连接了两个条件，将多条件查找变成了单条件查找，后续的问题就简单了，INDEX+ MATCH 函数组合也可以解决，在 G2 单元格中录入如下公式，并按三键结束：
=INDEX(C2:C9,MATCH(E2&F2,A2:A9&B2:B9,)) ·134 ·
第5章 海量数据分析的利器——初识查找引用函数
公式解析：这个公式还是典型的INDEX+MATCH组合，公式没有悬念，需要注意的 是应按三键结束，给Excel传递一个信息，这里有多重运算。
5.3.2 特殊的多条件查找
多条件查找还有两种特殊的情况，展现的外观不同于之前介绍的例子，但实质上就是 多条件查找。
1．双向查找
查找的条件分别散布在行和列上，这种问题一般称其为“双向查找”。沿用上例的数 据，需要查找的条件分别在第1行和第E列，要求在F2单元格中写一个公式，向下向右 复制完成，如图5.10所示。
图5.10 双向查找的示例
1）问题分析
条件不在同一行中，实质上还是多条件，使用连接符&连接两个条件，将多条件变成 单条件是最常用的思路。
2）解决方案
在F2单元格中录入如下公式，按三键结束，并向下向右复制完成。 3）公式解析
=VLOOKUP($E2&F$1,IF({1,0},$A$2:$A$9&$B$2:$B$9,$C$2:$C$9),2,) 公式本身没有悬念，关键要注意 VLOOKUP 函数第 1 个参数中单元格的引用类型。对 于E2单元格，公式复制后引用的E列是不变的，引用的行会改变，所以对于E2单元格， 需要使用行相对列绝对的混合引用方式$E2；对于引用的F1单元格判断依此类推。
示例文件中给出了另两种思路的公式如下，注意使用INDEX+MATCH函数组合需要 按三键结束。
职场点睛：这个案例的启示是，要学会分析问题，将复杂的问题简单化并归纳到已知 的套路中去。函数问题如此思考，实际工作也是一样的。
·135·
=INDEX($C$2:$C$9,MATCH($I2&J$1,$A$2:$A$9&$B$2:$B$9,)) =LOOKUP(1,0/(($A$2:$A$9=$M2) *( $B$2:$B$9=N$1)),$C$2:$C$9) 第2篇 计算数据
2．多列查找
还有一种特殊的多条件查找，需要用一个公式解决多个查找问题，如图5.11所示，在 B9 单元格录入一个公式，向右复制后能在 A9 单元格姓名变动的情况下返回对应的性别、 年龄和职务。
图5.11 多列查找的示例
1）问题分析
使用 VLOOKUP 函数一个单元格设置一个公式很简单，但不符合批量操作的要求。要 批量操作就要找到这些操作之间的内在规律。先写出单独解决B9、C9、D9单元格的公式 如下：
可以发现这3个单元格中公式的差异仅是VLOOKUP函数的第3个参数不一样，分别 是数字2、3、4。如果有一个函数在B9单元格中返回2，公式向右复制时能变成3、4，这 个问题就解决了。
Excel中就有这样一个函数——COLUMN。 2）解决方案1
在B9单元格中录入如下公式，并向右复制完成。 3 ）公式解析
这个公式中的关键是COLUMN函数。COLUMN函数返回参数指定单元格引用的列 号，在省略参数的情况下返回COLUMN函数所在单元格的列号，如图5.12所示。
=VLOOKUP($A$9,$B$2:$E$6,COLUMN(),) =VLOOKUP(A9,B2:E6,2,) =VLOOKUP(A9,B2:E6,3,) =VLOOKUP(A9,B2:E6,4,)
图5.12 COLUMN函数的返回值
在 B C D 列的 在图 5.12 中，列出了在省略参数的情况下函数公式“ =COLUMN()”、 、
返回值分别是2、3、4，也就是第9行中VLOOKUP函数第3个参数的取值。
·136 ·