打开APP
userphoto
未登录

开通VIP,畅享免费电子书等14项超值服

开通VIP
Excel中如何实现多条件查找

美轮美奂的深圳人才公园

在《Excel中反向查找方法大全》中,讲解了实现单一条件查找的四种方法,遇到多条件查找需求怎么办?本文就主要来讲解多条件查找的十种实现方法。

如下B:D列是产品不同型号的单价信息,要求根据B13和C13两个单元格对应的产品和型号,在D13单元格查找对应的单价

实现非常之多,逐一介绍如下:

【1】VLookup函数:

D13单元格公式:

{=VLOOKUP(B13&C13,IF({1,0},B2:B9&C2:C9,D2:D9),2,FALSE)}

B13&C13 : 多条件构成的组合查询条件

B2:B9&C2:C9数组运算返回组合的查询区域

IF({1,0},B2:B9&C2:C9,D2:D9)返回组合的查询区域和结果区域

VLOOKUP按常规正向查询返回结果

特别提醒:

因有数组运算,必须按照数组公式输入,即同时按Ctrl Shift Enter完成数组公式输入。

【2】Lookup函数:

D13单元格公式:

=LOOKUP(1,0/((B2:B9=B13)*(C2:C9=C13)),D2:D9)

((B2:B9=B13)*(C2:C9=C13)) 符合多条件的返回1,否则返回0

0/((B2:B9=B13)*(C2:C9=C13))符合多条件的返回0,否则返回0/0的错误值

=LOOKUP(1,0/((B2:B9=B13)*(C2:C9=C13)),Lookup函数在查找的时候是忽略错误的,所以返回小于1的最大值位置,即符合条件的0值位置,从而返回D2:D9对应位置的单价。

理解0/((B2:B9=B13)*(C2:C9=C13))的目的就是将正确结果用0表示,其他的变成错误值,利用Lookup函数查找忽略错误这个特点完成查找。

【3】Index Match函数:

D13单元格公式:

{=INDEX(D2:D9,MATCH(B13&C13,B2:B9&C2:C9,0))}

B13&C13 : 多条件构成的组合查询条件

B2:B9&C2:C9 :数组运算返回组合的查询区域

MATCH(B13&C13,B2:B9&C2:C9,0)返回组合查询条件满足的行序号

INDEX(D2:D9,行序号)返回D2:D9对应行序号的单价

特别提醒:

因有数组运算,必须按照数组公式输入,即同时按Ctrl Shift Enter完成数组公式输入。

【4】Offset Match函数:

D13单元格公式:

{=Offset(D1,Match(B13&C13,B2:B9&C2:C9,0),)}

B13&C13 : 多条件构成的组合查询条件

B2:B9&C2:C9 :数组运算返回组合的查询区域

MATCH(B13&C13,B2:B9&C2:C9,0):返回组合查询条件满足的行序号

OFFSET(D1,行序号, )返回对应行序号的单价

特别提醒:

因有数组运算,必须按照数组公式输入,即同时按Ctrl Shift Enter完成数组公式输入。

【5】Indirect Match函数:

D13单元格公式:

{=INDIRECT('D' & (MATCH(B13&C13,B2:B9&C2:C9,0) 1))}

B13&C13 : 多条件构成的组合查询条件

B2:B9&C2:C9 :数组运算返回组合的查询区域

MATCH(B13&C13,B2:B9&C2:C9,0):返回组合查询条件满足的区域行序号

MATCH(B13&C13,B2:B9&C2:C9,0) 1:返回组合查询条件满足的D列行号

INDIRECT函数作用:根据字符串拼接得到的单元格引用返回单元格值。

特别提醒:

因有数组运算,必须按照数组公式输入,即同时按Ctrl Shift Enter完成数组公式输入。

【6】SUM函数:

D13单元格公式:

{=SUM((B2:B9=B13)*(C2:C9=C13)*D2:D9)}

(B2:B9=B13)*(C2:C9=C13)满足组合查询条件返回1

(B2:B9=B13)*(C2:C9=C13)*D2:D9:满足组合查询条件返回单价,否则返回0,满足条件的只有一条数据,求和即是单价

特别提醒:

因有数组运算,必须按照数组公式输入,即同时按Ctrl Shift Enter完成数组公式输入。

【7】SUMPRODUCT函数:

D13单元格公式:

=SUMPRODUCT((B2:B9=B13)*(C2:C9=C13)*D2:D9)

(B2:B9=B13)*(C2:C9=C13)满足组合查询条件返回1

(B2:B9=B13)*(C2:C9=C13)*D2:D9:满足组合查询条件返回单价,否则返回0,满足条件的只有一条数据,求和即是单价

【8】SUMIFS函数:

D13单元格公式:

=SUMIFS(D2:D9,B2:B9,B13,C2:C9,C13)

满足多条件的只有一条数据,求和即是单价

【9】MAX函数:

D13单元格公式:

{=MAX((B2:B9=B13)*(C2:C9=C13)*D2:D9)}

(B2:B9=B13)*(C2:C9=C13)*D2:D9:满足组合查询条件返回单价,否则返回0,满足条件的只有一条数据,求最大值即是单价

【10】MIN IF函数:

D13单元格公式:

{=MIN(IF((B2:B9=B13)*(C2:C9=C13),D2:D9))}

IF((B2:B9=B13)*(C2:C9=C13),D2:D9)满足组合查询条件返回单价,否则返回FALSE

MIN忽略逻辑值FALSE,满足多条件的单价只有一条数据,求最小值即是单价。

其实实现多条件查找的还远不止这十种方法,写到此,深感Excel函数功能还真是丰富和强大。


分享Excel实用技术,助力办公效率提升

欢迎加入Excel表哥之家

本站仅提供存储服务,所有内容均由用户发布,如发现有害或侵权内容,请点击举报
打开APP,阅读全文并永久保存 查看更多类似文章
猜你喜欢
类似文章
【热】打开小程序,算一算2024你的财运
多条件查询,这几种方法的确很管用!
收藏这一篇文章,足以搞定VLOOKUP这个迷人的小妖精
excel数据核对:3个公式让你完成数据核对问题
精通数组公式17:基于条件提取数据(续)
28如何用函数处理交叉表求和问题?
Excel多条件查找的10个公式!建议收藏!
更多类似文章 >>
生活服务
热点新闻
分享 收藏 导长图 关注 下载文章
绑定账号成功
后续可登录账号畅享VIP特权!
如果VIP功能使用有故障,
可点击这里联系客服!

联系客服