今天讲的是一个关于匹配的案例,提到匹配vlookup 、hlookup这一类函数出现的频率是非常高的。但只适合纵向或者横向的匹配,如果有出现需要满足行列交叉条件的这里函数就不适合了。index+match函数组合可以很好地解决这样的问题。

Excel中index+match组合函数使用方法和实例详解-天天办公网

根据图中的上方的信息,找出每款产品的返利点并计算出返利金额,重点是如何找到返利点,正确的定位返利点有两个条件:

1.找到产品在返利点中的行数

2.找到产品对应的日期列表

解决方法已经有了

index(返利点表格区域,产品在表格区域的行数,产品日期在表格区域的列表)

套公式 index(A3:M18 , 行数?,列数?) 行数和列数如何确定呢?就需要用到它的搭档match来嵌套。match函数作用是在一个区间内返回目标所在的位置,只能返回单行或单列 match(查找目标,区域,区域内行数或列数),因为返回值只能是单行或单列,所以选择的区域是行就返回行,是列就返回列。

完善公式,参数替代

index(A3:M18 , match(产品名,A3:A18),列数?)

为什么列数不用match呢?因为日期是控制列的,而日期这里有一项是如果是日为空按照当月计算。所以先要确定的是产品的生产日期。日期返回对应的列只需要用到月份,观察图片中的表格信息1-12月都是按顺序横排的,只需要确定是几月就能知道在月份区域中的的第几列是

继续完善公式

index(A3:M18 , match(产品名,A3:A18),if(产品日期空,返回当前月份,实际月份)) 返回日期的月份可以用month函数,返回当前月份就是返回今天日期所在的月份 ,而获取今天日期刚好有一个函数today,所以当前月份就是month(today()),产品所在月份就是month(产品日期)

再次替换公式里的参数

index(A3:M18 , match(产品名,A3:A18),if(产品日期空,month(today()),month(产品日期))

现在可以在单元格里进行公式编辑了

Excel中index+match组合函数使用方法和实例详解-天天办公网

D25单元格的公式=INDEX($A$3:$M$18,MATCH(A25,$A$3:$A$18),IF(B25=””,1+MONTH(TODAY()),1+MONTH(B25)))

$符号是用来锁定区间的,公式需要往下填充

IF(B25=””,1+MONTH(TODAY()),1+MONTH(B25)) 后半截if是返回日期的月份,最终作用是返回index函数中月份所在的列,看【2022春季轻奢-连衣裙】这款产品,日期不为空 2022/1/1,month(B25)返回结果是1,if函数的结果也是1,在A列到M列这个区间内,1就是A列显然是有问题的,应该是从B列由月份开始计算,所以把占用的A这1列给加上去,1+month(B25)。

最后来看返利金额所在的单元格D25出来的是个返利点并不是金额,只需要再乘金额就可以了。最终完整公式如下

=INDEX($A$3:$M$18,MATCH(A25,$A$3:$A$18),IF(B25=””,1+MONTH(TODAY()),1+MONTH(B25)))*C25

Excel中index+match组合函数使用方法和实例详解-天天办公网

以上就是今天的案列说明,可以多练习几次,举一反三熟悉index+match组合。