美轮美奂的深圳人才公园
在《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表哥之家
联系客服