VLOOKUP&LOOKUP双雄战（五）：野马崛起！
作者：小花来源：部落窝教育发布时间：2019-05-15 09:52:52点击：6678
编按：
哈喽，大家好！经过4天紧张激烈的角逐，VLOOKUP与LOOKUP终于迎来了最后的较量——缺失查询和多条件查询！胜负在此一举，查询双雄，究竟谁才是最后的王者呢？
作为《VLOOKUP&LOOKUP双雄战》系列文章的收官之战，LOOKUP(1,0/(条件)...结构将纵横查询沙场，所向披靡；而作为对手，VLOOKUP将如何应对？闲话少说，直入正文！
ROUND 07 缺失查询
在本系列开篇时，VLOOKUP和LOOKUP就在如何通过人物简称查询全称的模糊包含查询问题上有过一番较量，彼时，VLOOKUP略胜一筹。本回合，它们将面对包含查询的逆命题——缺失查询。下图中，我们已知人物全名是特拉法尔加·罗，要查询他所拥有的果实名称。这本来是一个很简单的问题，但是出于某种原因，果实对照表中的姓名并不是全称，而是简称，OH MY GOD，不会真尴尬！！！
图7.1：缺失查询——全称查简称
LOOKUP函数：信手拈来，花样百出
这类问题对LOOKUP非常简单，特别是在我们讲解了1/0结构以后。
=LOOKUP(1,0/FIND(A2:A9,D3),B2:B9)
图7.2：缺失查询——LOOKUP信手拈来
公式说明
FIND(A2:A9,D3)用于判断A2:A9中的每一个简称是否被D3全称所包含。若包含，则返回简称在全称中出现的位置，否则公式返回错误值#VALUE!。于是0/FIND(A2:A9,D3)即返回一组由数字0和错误值组成的有序数组，数字0在数组中的位置即查询返回值所在行。接着LOOKUP函数开始发挥作用，忽略错误值，返回小于且最接近于目标值1的查询区域值所对应的值，即最后一个0值所对应的B6的值。详细解读如下:
彩蛋1：你发现了吗？如果LOOKUP的查询值足够大(实际上，大于D3的字符数即可)，那么我们大可把FIND函数前“0/”去掉，使用=LOOKUP(100,FIND(A2:A9,D3),B2:B9)这样的公式也可以完成模糊包含查找。
图7.3：缺失查询——"0/"可去除
彩蛋2：你发现了吗？彩蛋1的公式竟然和本系列第一篇中的LOOKUP模糊包含查询公式如出一辙，差别仅在于FIND函数的两个参数互换位置。没错，你没看错，这就是神奇的LOOKUP函数，高手的挚爱。
图7.4：缺失查询——本系列第一篇LOOKUP模糊包含查询
彩蛋3：你发现了吗？LOOKUP结构中“0/”的作用其实是将任何结果都转化为0和错误值，该结构的核心技术是LOOKUP忽略错误值的特性、二分法(默认升序并返回最大的小于等于目标值的值)和自带数组运算。所以，有时我们也可以用LOOKUP(2,1/(条件)...或LOOKUP(0.1,0/(条件)...等等结构来替代，只需确保第一个参数总比第二个参数返回的数组中的最大值大即可！
图7.5：缺失查询——1/0结构的变形
VLOOKUP函数：最后的倔强
在模糊缺失查询问题上，VLOOKUP本该缴械投降的。但作为大众情人的VLOOKUP一身傲骨，死也要站着死！
=VLOOKUP(D3,IF({1,0},IFERROR(VLOOKUP(T(IF({1},"*"&A2:A9&"*")),D3,1,0),1),B2:B9),2,0)
图7.6：缺失查询——VLOOKUP的倔强
公式说明
一个强行使用两个VLOOKUP的嵌套函数，其大致意思是将A2:A9中被D3包含的文本用D3替换，其余保持不变；然后将替换后的A2:A9和不做改变的B2:B9组成一个新的查询区域；最后再用VLOOKUP的基本套路完成查询。
问：具体运算过程如何理解？
答：不用理解，如遇此类问题，请用LOOKUP！
问：为什么要用两次VLOOKUP，而不使用其他函数替代？
答：丢分不丢人，这是VLOOKUP最后的倔强！
ROUND 08 多条件查询
在多数Excel实操问题上，往往需要多个条件才能准确指向目标，于是有了多条件求和函数SUMIFS，有了多条件计数函数COUNTIFS。但是没有多条件查询函数VLOOKUPS和LOOKUPS，这是为什么呢？因为这两个函数本身就能实现多条件查询！！！如下例，我们需要通过职位和性别来找到名单中唯一的女性船长并返回她的明细，该怎么做？
图8.1：多条件查询——哪来的女船长？
VLOOKUP：合纵连横，无往不利
在逆向查询中，我们学习了如何利用IF{1,0}结构构建列序交换的虚拟数组来完成逆向查询，在这里使用连接符&，我们用它来配合VLOOKUP函数完成多条件查询。
{=VLOOKUP(E3&F3,IF({1,0},C2:C8&B2:B8,A2:A8),2,0)}
图8.2：多条件查询——VLOOKUP的连接符
公式说明
首先用连接符"&"将E3和F3组合起来，形成新的查询值，即“船长女”；接下来要做的就是构建一个以职位和性别组成的首列并与姓名列组成新的查询范围。我们还是用&来完成，将C2:C8和B2:B8连接起来，形成新的查询列“职位+性别”列，即{"船长男";"航海士女";"船长男";"剑士男";"船长女";"考古学家女";"船长男"}，然后使用IF{1,0}结构将新的查询列与姓名列A2:A8组成新的查询范围，最后VLOOKUP发挥功能，完成查询。此时如果你只是单纯地用Enter来完成计算，可能就要吃瘪了，因为这是一个数组运算，需按Ctrl+Shift+Enter才能完成计算，小伙伴们慎之慎之！！！
LOOKUP：1/0结构真正的战场
在上一篇中，小花花了很大篇幅来讲解LOOKUP(1,0/(条件)......结构，当然不只是为了完成逆向查询这种简单问题！多条件查询，才是LOOKUP(1,0/(条件)......结构真正的战场！
=LOOKUP(1,0/((E3=C2:C8)*(F3=B2:B8)),A2:A8)
图8.3：多条件查询——LOOKUP的1/0结构
公式说明
该公式使用了LOOKUP(1,0/(条件)......结构的复杂版——LOOKUP(1,0/((条件1)*(条件2)...*(条件n)),结果列)。它将LOOKUP(1,0/(条件)......结构中的单一条件升级为多条件相乘的形式(条件1)*(条件2)...*(条件n)，利用乘法运算中TRUE=1、FALSE=0的原理，使得只有当所有条件都满足而返回TRUE时，TRUE*TRUE=1*1=1，此时0/1等于0；但凡任何一个条件不满足返回FALSE， 0乘以任何数都等于0，此时0/0返回错误值#DIV/0!。于是LOOKUP函数的第二参数查询区域成为一组由0和#DIV/0!组成的有序数组{#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;0;#DIV/0!;#DIV/0!}。如前述，LOOKUP可以忽略错误值并返回最后一个小于或等于目标值的二分位值所对应的结果，于是最后一个0所对应的值A6“邦尼”就是公式的返回值。
于是有的小伙伴就要纳闷了，既然VLOOKUP可以用连接符"&"连接多个条件，LOOKUP可以用乘号"*"串联多个条件，它们都能完成多条件查询，那是不是这一回合双方打平啊？当然不是，在多条件查询领域，LOOKUP具有绝对的优势，它不仅能完成和VLOOKUP一样的精确性比对匹配，还能完成区间条件的查询。简单的说，VLOOKUP连接法的多条件查询只能判别查找值和查询区域中的值是否相等，而LOOKUP的0/1结构还能完成大于或小于这类非精确查找的条件判别。
=LOOKUP(1,0/((E3=B2:B7)*(C2:C7<10000)),A2:A7)
图8.4：多条件查询——VLOOKUP不知道的LOOKUP
公式说明
该公式与上一公式的区别在于，第二个条件的逻辑判断符号不再是等号，而是小于号"<"，这是VLOOKUP用连接符所无法完成的复杂工作。这就是为什么有人说，如果要评价函数圈最大的发现，那LOOKUP的两分法绝对能独占鳌头！诚然！
结束语
本文，小花介绍了包含查询和多条件查询这两个难度较高的应用情境，至于多值查询、批量查询等诸多查询高难度用法，还有待小伙伴们进一步去挖掘、去深究！希望经过本文的学习，小伙伴们能够更加深刻地了解、使用VLOOKUP和LOOKUP这两个高频函数的用法！
系列结束语
本系列，我们从VLOOKUP和LOOKUP这两个函数的基础用法开始，逐步扩展到包含查询、交叉查询、区间查询、横向查询、逆向查询，直至本文讲解的缺失查询和多条件查询，全程深入对比了VLOOKUP和LOOKUP这两个函数，并详细说明了每一个公式的计算原理，重点剖析了VLOOKUP+MATCH、LOOKUP的两分法、LOOKUP(1,0/(条件)......结构等重要知识点。八个回合的较量中，我们发现，常规查询VLOOKUP更具优势，但一旦查询难度上升时，LOOKUP的优势便慢慢显露出来。VLOOKUP像是LOOKUP的便捷版，使用起来更加便利但也有更多限制，LOOKUP则使用起来更加自由但更难把握。至此， VLOOKUP&LOOKUP双雄战就此画上句号，千里良驹VLOOKUP，荒原野马LOOKUP，你更喜欢哪个？
本文配套的练习课件请加入QQ群：264539405下载。
做Excel高手，快速提升工作效率，部落窝教育《一周Excel直通车》视频和《Excel极速贯通班》直播课全心为你！
扫下方二维码关注公众号，可随时随地学习Excel：
相关推荐：