编按
Hello小伙伴们~在表格中常见的单条件查找目标值相信大家都会,用过VLOOKUP函数的小伙伴可以说是很轻松就能解决单条件查找的问题,但是当单条件变成多条件的时,你还会用函数查找吗?今天小编整理了常见的二维数据中多条件查找的公式大全送给大家。
Hello,大家好!这里是部落窝教育,我是花花,很高兴和你一起学习Excel教程,今天我们来学习函数解决多条件查找的多种方法,相信总有一种适合你的。下图效果是当条件①和条件②变化时,会自动在左边的数据区域中查找出业绩。
在讲解公式之前,我们先在I3、K3单元格分别输入“姓名”和“月份”,选中I4单元格,点击【数据】选项卡下的“数据验证功能”。在弹出来的对话框中,在 “允许”的下拉列表中选择“序列”。
在“来源”文本框中输入=$C$3:$G$3,单击“确定”按钮。K4单元格中的月份操作相同,不同的只是需要在“来源”文本框中输入=$B$4:$B$9。
返回工作表中,单击设置了数据验证单元格,在单元格右侧会出现一个下拉按钮,单击该按钮可选择指定序列内容。这个很简单,我们就不多赘述啦,现在正式进入多条件查找公式讲解部分。
1.VLOOKUP+MATCH多条件查找
在K6单元格中输入公式=VLOOKUP(K4,B:G,MATCH(I4,B3:G3,0),0)
公式语法解释:
VLOOKUP函数语法:(查找值,查找区域,返回值的列数,精确查找或模糊查找)
K4单元格是对应条件②的月份值,查找区域为B:G列,查找区域中的列数使用MATCH函数进行判断。
MATCH函数的语法:(查找对象,指定查找的范围或者数组,查找方式)
MATCH的对象就是条件①的姓名,查找范围就是B3:G3,查找方式选择0为精确查找。
2.HLOOKUP+MATCH多条件查找
在K6单元格中输入公式=HLOOKUP(I4,3:9,MATCH(K4,B3:B9,0),0)
公式语法解释:
HLOOKUP函数语法:(查找值,查找范围,返回值所在的行数,查找模式)
3.INDEX+MATCH+MATCH多条件查找
在K6单元格中输入公式=INDEX(B3:G9,MATCH(K4,B3:B9,0),MATCH(I4,B3:G3,0))
公式语法解释:
INDEX函数语法:(查找区域或数组常量,返回值所在区域的行号,返回值所在区域的列号)
4.SUMPRODUCT多条件查找
在K6单元格中输入公式=SUMPRODUCT((B4:B9=K4)*(C3:G3=I4)*C4:G9)
公式语法解释:
SUMPRODUCT函数语法:((条件1=条件区域1)*(条件2=条件区域2)*……*(求和区域))
5.SUMIF+OFFSET+MATCH多条件查找
在K6单元格中输入公式=SUMIF(B:B,K4,OFFSET(B:B,0,MATCH(I4,C3:G3,0)))
公式语法解释:
SUMIF函数语法:(条件区域,指定的条件,需要求和的区域)
OFFSET函数语法:(起始单元格,移动的行数,移动的列数,高度,宽度)
6.DSUM多条件查找
前面介绍的五种方法基本都使用了函数嵌套才完成了多条件查找,DSUM函数对多条件查找就不需要嵌套那么麻烦了,在K6单元格中输入公式=DSUM(B3:G9,I4,K3:K4)即可。
公式语法解释:
DSUM(数据区域,求和的列数,条件区域)
7.XLOOKUP多条件查找(office365专属函数)
在K6单元格中输入公式=XLOOKUP(K4,B4:B9,XLOOKUP(I4,C3:G3,C4:G9),0)
公式语法解释:
XLOOKUP函数语法:(要查找的值,查找的区域,返回的区域)
8.INDEX+FILTER+MATCH多条件查找
在K6单元格中输入公式: =INDEX(FILTER(B3:G9,B3:B9=K4),MATCH(I4,B3:G3,0))
FILTER也是Office 365专属函数,INDEX和MATCH这两个函数前面几种方法已经出现很多次了,相信大家一定不陌生了,在条件查找中MATCH函数具有“名配角”的美称。
联系客服