工作中经常遇到两个excel表中互相提取和匹配数据的需求
1、单列匹配
输入公式:
=INDEX(B2:B8,MATCH(D2,A2:A8,))
MATCH(D2,A2:A8,)部分找到D2单元格内容“爱知趣教育”在单元格区域A2:A8中的位置6;
INDEX函数返回单元格区域B2:B8中对应位置
2、区间等级查找
输入公式:
=INDEX(F$11:F$14,MATCH(B11,E$11:E$14,1))
MATCH(B11,E$11:E$14,1)部分使用了模糊查找,查找小于或等于查找值的最大值,注意查找区域要按升序排列。
3、通配符查找
输入公式:
=INDEX(B$20:B$26,MATCH("*"&D20&"*",A$20:A$26,))
星号(*)匹配任意一串字符,字符间用&连接。
4、带“~”的查找
输入公式:
=INDEX(B29:B35,MATCH(D29,A29:A35,))
公式没错,为什么结果会返回错误值#N/A呢?
业务员“雨~夜”中带有波形符(~),波形符(~)作为通配符,在查找包含其本身的值时,需在“~”前键入“~”,用SUBSTITUTE函数将“~”替换成“~~”。
输入公式:
=INDEX(B29:B35,MATCH(SUBSTITUTE(D29,"~","~~"),A29:A35,))
5、多列数据匹配
输入公式:
=INDEX(B38:B43,MATCH($F$38,$A$38:$A$43,))
MATCH($F$38,$A$38:$A$43,)部分找到F38单元格内容“小玉”在单元格区域A38:A43中的位置5;
公式向右填充,单元格区域由B38:B43变C38:C43变D38:D43,INDEX函数返回单元格区域中对应位置5的单元格的值。
6的单元格B7的值,即返回15000。