LOOKUP函数多条件查询的用法

有位朋友的问题是这样的:要查找出客户名称中包含“扬名”且商品为“杜鹃”的最后一次记录的单价,结果如I3单元格所示。符合条件的记录已用绿色标出,只取最后一次记录的单价。

这是个多条件查找的问题,而且是查找最后一次的记录,我们可以用lookup函数来完成。在I3单元格输入公式=LOOKUP(,-FIND(G3,A3:A20)/(H3=B3:B20),C3:C20),完成。
下面来解释下这个公式的意思。FIND(G3,A3:A20)这部分用find函数在A列的客户名称中查找G3的“扬名”,如果能找到返回一个数字,否则返回错误值。如下图D列所示。
H3=B3:B20这部分用来判断B列的商品是否等于H3的“杜鹃”,如果相等返回TRUE,否则返回FALSE。如下图E列所示。
如果要同时满足这2个条件,D列必须为数字且E列必须为TRUE,下图中已经用红线标出。让这两列做相除的运算,用D列除以E列,前面再加个负号,也就是-FIND(G3,A3:A20)/(H3=B3:B20)这部分,结果如F列所示,可以看到同时满足这2个条件的返回一个负数,否则返回错误值。

只要找到最后一个负数的位置,就找到了最后一次满足条件的记录。最后的查找公式为=LOOKUP(,-FIND(G3,A3:A20)/(H3=B3:B20),C3:C20)。

其中lookup的第1参数省略相当于0,由于0比第2参数中所有的负数都大,所以会找到最后一个负数,并返回第3参数中对应位置的单价。

PS:
常规的lookup多条件查找的套路公式是=lookup(1,0/((条件区域1=条件1)*(条件区域2=条件2)),返回区域)
本例中2个条件之间能用除法,是因为find函数返回的结果只有正数和错误值。find()/(条件区域=条件),同时满足条件的返回数字,不同时满足条件的返回错误值。
文件链接:

https://pan.baidu.com/s/1ASPWGKaY4BKvADbw4MOqzQ

提取码:jjx5
(0)

相关推荐