突破 VLOOKUP 函数反向查找的限制

Excel

情报局

生产、搬运、分享Excel基础知识

用1%的Excel基础搞定99%的日常工作

┿Excel是门手艺，玩转需要勇气┾

坚信你所坚持的事必有回报

生产、搬运、分享Excel

办公提升微社区

Excel情报局推荐音乐，学习更轻松

爱，不是寻找一个完美的人，而是学会用完美的眼光，欣赏那个并不完美的人。每个人心中都住着一个孩子，每个女孩子心里都有一座城堡。我一直在躲避，但我终于找到要保护的人了，那就是你。因为爱你，只要你一个肯定，我就足够勇敢。

如下图所示，学生的学号与姓名是一一匹配的，如已知学号要查找姓名，可用VLOOKUP函数解决，F2 单元格公式如下：

=VLOOKUP(E2,A2:B10,2,0)

但反过来已知姓名要查找学号，直接用VLOOKUP函数就不行了。

1．问题分析

上图已知姓名查询学号

这里直接用VLOOKUP不行，原因在于在查找区域中需要返回的“学号”在“姓名”的左边，也就是不符合需要查找项目“姓名”在查找区域A2:B10的第一列的条件。

2．解决方案（1）

知道了问题的所在，解决就容易了。如果在“学号”的左侧有一列“姓名”，这个问题就迎刃而解了。一个笨方法是在A列“学号”之前插入一列，复制原B列“姓名”的内容，这样就可以直接使用VLOOKUP函数了。

这种方法思路是对的，但操作方法不可取。不建议随意更改数据源，用函数完全能达到插入一个辅助列，重构数据源的效果。在F5 单元格录入如下公式：

=VLOOKUP(E5,IF({1,0},B2:B10,A2:A10),2,)

3．公式解析

整个公式外层还是VLOOKUP函数，关于这个函数的用法不再赘述。

公式中使用了一个IF 函数作为VLOOKUP 函数的第2 参数，在编辑栏中选取这一段函数 “IF({1,0},B2:B10,A2:A10)”，按F9 键，返回结果为：

{"管莺菲","A001";"石永绍","A002";"越俊","A003";"崔亨","A004";"于成中","A005";"狄影淑","A006";"糜彩青","A007";"秋彩伊","A008";"裘健栋","A009"}

返回结果中的花括号代表数组，逗号表示同行不同列，分号表示换行。整个返回结果代表一个9行2 列的内存数组。如果直接用F9 键看不容易理解，还可以将这个IF函数的运算结果放到单元格中。选取H2：I10 单元格区域，录入公式“=IF({1,0},B2:B10,A2:A10)”，按“Ctrl+Shift+Enter”三键结束，返回结果如下图：

由上图可见，使用IF函数，返回了一个内存数组，相当于重构了一个区域，而这个重构的区域正好满足使用VLOOKUP查找时查找值位于被查找区域第一列的要求。

4．函数套路——IF{1,0}解析

这里IF函数的用法和常规不同。第一个参数中使用了一个常量数组{1,0}，第二、三参数各使用了一列数据区域。对于返回结果，可以理解为一个1*2 的数组与另一个9*1的数组的运算。返回的是一个9*2的数组。

如能理解数组运算的原理，就能明白为什么IF函数第一参数不能写成{1;0}，不要小看仅一个符号的差异，逗号表示横向，分号表示纵向，使用{1;0}，整个IF函数返回的结果相当于是一个2*1的数组与一个9*1的数组运算，因这两个数组方向相同大小不一样，部分返回值会出错，使用F9测试的结果是：

{"管莺菲";"A002";#N/A;#N/A;#N/A;#N/A;#N/A;#N/A;#N/A}

仅是一个9行1列的数组，如下图所示：

如果能够理解IF{1,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,)

这三个公式也能得出正确的结果，CHOOSE函数构建内存数组的效果同IF{1,0}

职场点睛：