实例:一对多查询
如下所示:A:B列是长虹电视的部分型号数据,每个尺寸段都有多个型号,如何在G列输入电视机的尺寸,便跳出所有的电视型号?效果如下所示:
本节介绍两种常用的方法VLOOKUP法和INDEX+SMALL+IF万金油公式。
方法一
VLOOKUP+辅助列法
我们知道VLOOKUP只能一对一查询,所以对于查找的数据必须是唯一的,在E列建立辅助列,公式输入:=C2&COUNTIF(C2:$C$2,C2)对C列单元格进行累计计数。
得到的结果便是43的第1个,表示为431
55的第1个,表示为551
43累计第2个,表示为432
55的第2个,表示为552
....依次累计转换成独一无二的值,便于查找
在F列建立辅助查找项,输入公式=$G$2&ROW(A1)
通过这种方法,查找的项目也变得不唯一
即查找43的第1个,第2个,第3个....
分别为431 432 433 434....
这样就转换成了一个逆向查找匹配数据的问题了
再通过VLOOKUP函数+IF({1,0})重构数据源,就得到结果了
=IFERROR(VLOOKUP(F2,IF({1,0},$E$1:$E$8,$D$1:$D$8),2,0),"")
如果熟练一点的话,就可以不用F列辅助项,将公式更改为:
=IFERROR(VLOOKUP($G$2&ROW(A1),IF({1,0},$E$1:$E$8,$D$1:$D$8),2,0),"")
如果再熟练一点,可以将E列辅助项去掉,将公式更改为数组公式,计算时按Ctrl+Shift+Enter三键
=IFERROR(VLOOKUP($G$2&ROW(A1),IF({1,0},$C$1:$C$8&COUNTIF(INDIRECT("C1:$C"&ROW($1:$8)),$G$2),$D$1:$D$8),2,0),"")
方法二
INDEX+SMALL+IF
本节直接给出公式:
=INDEX(D:D,SMALL(IF($C$1:$C$8=$G$2,ROW($C$1:$C$8),1000),ROW(A1)))&""
该公式为数组公式,输入完成后需要按三键Ctrl+Shift+Enter。