72个反向查找的公式套路，都看明白的就成精了！

Excel基础学习园地

公众号“Excel基础学习园地”是一个免费发布Excel基础知识、函数应用、操作技巧、学习方法等资讯的公众号，请点击上方“Excel基础学习园地”添加关注，方便我们每天向您推送精彩资讯。

题目：如下图所示的表中，要求根据“客户+型号”查找左侧的数量。结果如黄色单元格所示。

练习数据可以自行模拟后验证下列公式。

公式：（个别公式只适用于查找结果为数字的情况）

=VLOOKUP(H3,IF({1,0},D$3:D$14,C$3:C$14),2,)
=INDEX(C:C,MATCH(H3,D:D,))
=OFFSET(C$1,MATCH(H3,D:D,)-1,)
=INDIRECT('c'&MATCH(H3,D:D,))
=LOOKUP(,0/(D:D=H3),C:C)
=DSUM(C$2:D$14,1,H$2:H3)-SUM(N$2:N2)
=INDEX(C:C,MAX(IFERROR(IF(FIND(H3,$D$3:$D$14),ROW($D$3:$D$14)),)))
=SUM((D$3:D$14=H3)*C$3:C$14)
=SUMPRODUCT((D$3:D$14=H3)*C$3:C$14)
=SUMIF(D:D,H3,C$1)
=SUM(IF(H3=D$3:D$14,C$3:C$14))
=HLOOKUP(C$2,$2:$14,MATCH(H3,D:D,)-1,)
=VLOOKUP(H3,CHOOSE({1,2},D$3:D$14,C$3:C$14),2,)
=MMULT(TRANSPOSE(--(D$3:D$14=H3)),C$3:C$14)
=SUMIFS(C$3:C$14,D$3:D$14,H3)
=C:C OFFSET($1:$1,MATCH(H3,D:D,)-1,)
=SUBTOTAL(9,OFFSET(C$1,MATCH(H3,D:D,)-1,))
=INDEX(C:C,MATCH(1=1,H3=D:D,))
=VLOOKUP(H3,TEXT({1,-1},LEFT(D$3:D$14,FIND('-',D$3:D$14))&'!'&MID(D$3:D$14,FIND('-',D$3:D$14)+1,1)&SUBSTITUTE(MID(D$3:D$14,FIND('-',D$3:D$14)+2,9),0,'\0')&';'&SUBSTITUTE(C$3:C$14,0,'\0')),2,)
=INDIRECT('r'&MATCH(H3,D:D,)&'c3',)
=INDIRECT(ADDRESS(MATCH(H3,D:D,),3))
=OFFSET(C$2,VLOOKUP(H3,IF({1,0},D$3:D$14,ROW($1:$12)),2,),)
=MIN(IF(D$3:D$14=H3,C$3:C$14))
=SMALL(IF(D$3:D$14=H3,C$3:C$14,9^9),1)
=MAX(IF(D$3:D$14=H3,C$3:C$14,))
=C:C INDEX($1:$14,MATCH(H3,D:D,),)
=C:C INDIRECT(MATCH(H3,D:D,)&':'&MATCH(H3,D:D,))
=MAX((D$3:D$14=H3)*C$3:C$14)
=LARGE((D$3:D$14=H3)*C$3:C$14,1)
=SMALL((D$3:D$14=H3)*C$3:C$14,ROWS(3:14))
=SMALL((D$3:D$14=H3)*C$3:C$14,COUNT(C:C))
=MAXA((D$3:D$14=H3)*C$3:C$14)
=PRODUCT(IF(D$3:D$14=H3,C$3:C$14))
=OFFSET(INDIRECT('d'&MATCH(H3,D:D,)),,-1)
=HLOOKUP(H3,IF({1;0},TRANSPOSE(D$3:D$14),TRANSPOSE(C$3:C$14)),2,0)
=SUBTOTAL(5,OFFSET(C$1,MATCH(H3,D:D,)-1,))
=MAX(IFERROR(FIND(H3,D$3:D$14),)*C$3:C$14)
=SUM(IF(COUNTIF(H3,D$3:D$14),C$3:C$14))
=MAX(COUNTIF(H3,D$3:D$14)*C$3:C$14)
=MAXA(COUNTIF(H3,D$3:D$14)*C$3:C$14)
=SUM(COUNTIF(H3,D$3:D$14)*C$3:C$14)
=SUMPRODUCT(COUNTIF(H3,D$3:D$14)*C$3:C$14)
=INDEX(C:C,MAX(COUNTIF(H3,D$3:D$14)*ROW($3:$14)))
=INDEX(C:C,SUM(COUNTIF(H3,D$3:D$14)*ROW($3:$14)))
=INDEX(C:C,MAXA(COUNTIF(H3,D$3:D$14)*ROW($3:$14)))
=OFFSET(C$1,SUM(COUNTIF(H3,D$3:D$14)*ROW($3:$14))-1,)
=OFFSET(C$1,SUMPRODUCT(COUNTIF(H3,D$3:D$14)*ROW($3:$14))-1,)
=OFFSET(C$1,MAX(COUNTIF(H3,D$3:D$14)*ROW($3:$14))-1,)
=OFFSET(C$1,MAXA(COUNTIF(H3,D$3:D$14)*ROW($3:$14))-1,)
=INDIRECT('C'&SUM(COUNTIF(H3,D$3:D$14)*ROW($3:$14)))
=INDIRECT('C'&MAX(COUNTIF(H3,D$3:D$14)*ROW($3:$14)))
=INDIRECT('C'&SUMPRODUCT(COUNTIF(H3,D$3:D$14)*ROW($3:$14)))
=INDIRECT('C'&MAXA(COUNTIF(H3,D$3:D$14)*ROW($3:$14)))
=INDEX(C:C,MATCH(1,COUNTIF(H3,D$1:D$14),))
=INDEX(C:C,LOOKUP(99,1/COUNTIF(H3,D$1:D$14)*ROW($1:$14)))
=OFFSET(C$1,LOOKUP(99,1/COUNTIF(H3,D$1:D$14)*ROW($1:$14))-1,)
=INDIRECT('c'&LOOKUP(99,1/COUNTIF(H3,D$1:D$14)*ROW($1:$14)))
=PRODUCT(IF(COUNTIF(H3,D$3:D$14),C$3:C$14))
=PRODUCT(IF(IFERROR(SEARCH(H3,D$3:D$14),),C$3:C$14))
=PRODUCT(IF(ISNUMBER(SEARCH(H3,D$3:D$14)),C$3:C$14))
=AVERAGEIF(D:D,H3,C:C)
=CHOOSE(MATCH(H3,D$3:D$14,),C$3,C$4,C$5,C$6,C$7,C$8,C$9,C$10,C$11,C$12,C$13,C$14)
=MAX(--TEXT((D$3:D$14=H3)*C$3:C$14,'0;!0'))
=MAX(IFERROR(FREQUENCY(IF(D$3:D$14=H3,ROW($1:$12),13),ROW($1:$12))*C$3:C$14,))
=MMULT(COLUMN(A:L)^0,C$3:C$14*(D$3:D$14=H3))
=HLOOKUP(H3,TRANSPOSE(IF({1,0},D$3:D$14,C$3:C$14)),2,)
=MEDIAN(IF(D$3:D$14=H3,C$3:C$14))
=PRODUCT(IF(COUNTIFS(H3,D$3:D$14),C$3:C$14))
=MINA(IF(D$3:D$14=H3,C$3:C$14))
=AVERAGE(IF(D$3:D$14=H3,C$3:C$14))
=AVERAGEA(IF(D$3:D$14=H3,C$3:C$14))
=DAVERAGE(C$2:D$14,1,H$2:H3)*ROW(A1)-SUM(CB$2:CB2)

对于大多数人来说，能有一种方法解决问题就足够了，但是对于那些公式函数玩的很溜的人，总是希望能够用尽可能多的的公式来解决同一个问题，对别人来说是烧脑，对他们来说是干题，并且乐此不疲。

如果真的要把Excel用好，尤其是公式函数这部分，多烧烧脑肯定没坏处。即便自己能够想出来很有限的方法，哪怕只有两种，那也是一大进步，多看看其他人的思路，看多了自己的思路也会开拓很多。

如果你也想试试干题的乐趣，推荐一个地方：