绝对干货 excel中一对多查找问题解决思路汇总 附公式模型

公式模型一：预备知识：数组，逻辑值的概念，if函数的较好掌握，small函数的基本用法，index的基本用法。

f3的公式：

=IFERROR(INDEX($C:$C,SMALL(IF($B:$B=$E3,ROW(:),99),COLUMN(A1))),"")

数组公式，输入或者复制公式后，按住ctrl和shift键按回车，公式自动出现大括号，向右向下拉即可。结构分析：这个公式的核心结构是index（结果范围，small（if（条件范围=条件值，row（），一个超过结果范围个数的数字），column（））），加了个iferror是为了将错误值显示为空。

必须理解这个公式的思路，才能够灵活套用这个结构去解决问题。拿这个例子来说，b列是条件列，c列是结果列，e列是具体的条件，最终的结果需要横着拉（针对同一产品而言）。

这个公式的核心是if数组的运用，$B:$B=$E3可以得到一系列的逻辑值，而仅当b列中为"A"的项目对应的项才是true，从而返回ROW(:)这个数组的对应数字，而对于其他不是"A"的项目，对应的就是99，具体来看，这里的if的结果就是{1;99;99;4;99;99;99;99;9}，可以发现，这个数组里不是99的只有3个，1,、4和9，而这三个数字对应在$B:$B这个范围里，正好就是"A"对应的位置，为了依次得到这三个数字，就需要small这个函数了，因为公式需要横向拉动，所以用了column作为small的第二参数。到这一步，index的结果就不需要多讲了。

通过这个思路的分析，需要特别注意的几个地方就是数组维度的对应，也就是if里面的第一参数这个条件数组不需要多说，第二个参数的row包含的个数应该和条件范围是一样多的（并且应该绝对引用），第三参数这个在本例来说用10都可以，因为数据源只有9个，如果多的话可以用9^9（9的9次方=387420489）这个应该是足够大了。当然在if里面用column效果也是一样的，只不过row看起来简洁一点，千万不要和small的第二参数混淆了。

思路解释起来是很绕口的，更好的方法是模拟一个少一点的数据源，不超过10行的都可以，然后使用公式求值或者f9功能一步一步看看公式结果是怎么变化的，从而去了解公式的计算过程。

公式模型一：INDEX(返回区域,SMALL(IF(查找区域=查找值,ROW(查找区域),99,COLUMN(A1)))套用时需要看清案例一的特点：查找值在查找区域内是间隔出现的。

公式模型二：预备知识：IF、ROW、COUNTIF、OFFSET、MATCH等函数的基本用法。

首先观察这个题目和第一个例子有什么区别？查找值在查找区域是连续的，结果是向下拉的，也许还有别的差异。那么可以用公式模型一来完成吗？答案是肯定的，E2公式如下：

=INDEX($B:$B,SMALL(IF($A:$A=$D,ROW(:),99),ROW(A1)))，下拉即可，注意还是数组的，可以结合这个例子再去领会一下公式模型一的思路。

其实这个例子完全可以不用数组公式来处理，因为和例一最重要的一个差别就是a列的重复值是连续的，可以看做例一的特殊情况。把上面这个公式下拉多行会发现有乱码出现，如果要排错的话，还是可以用iferror，当然也可以用if+countif来实现，排错公式如下（都是数组哦）：=IFERROR(INDEX($B:$B,SMALL(IF($A:$A=$D,ROW(:),99),ROW(A1))),"")或者=IF(ROW(A1)>COUNTIF($A:$A,$D),"",INDEX($B:$B,SMALL(IF($A:$A=$D,ROW(:),99),ROW(A1))))

用if+countif来排错看起来是长一点，但是这个思路需要明白，关键就是row和countif的值做比较，而这个应用也是很有用的，下面就来看公式模型二，这个公式不需要数组哦：=IF(ROW(A1)>COUNTIF(A:A,D),"",OFFSET(B,MATCH(D,A:A,0)-3+ROW(A1),))其实这个公式的核心在于offset的作用了，尤其是offset的第二个参数，使用了match和row来共同得出行的偏移量，如果明白了offset在这里的作用，那么公式模型二也就没什么难以理解的了。

公式模型二：IF(ROW()>COUNTIF(),"",OFFSET(,MATCH(),,,)  关键在于offset第二参数的构造。注意:此公式不好之处在于查找区域必须排序,好处在于是普通公式运算速度快。

公式模型三：对于一对多查找的问题，基本就几种情况，如果不用辅助列的话，使用公式模型一都可以解决，如果可以排序的话，推荐使用非数组的模型二来解决，但是方法不仅限于这两种，如果可以使用辅助列的话，仅用vlookup都可以实现，而且无需数组，还是用实例二的数据，但是在数据源里我们加一个辅助列，如下

图：

辅助列的公式为，A3：=COUNTIF($B$3:B3,$E$3)，结果列公式为，F3：=IFERROR(VLOOKUP(ROW(A1),$A$3:$C$19,3,0),"")无需排序，无需数组，会用vlookup就能解决这类问题，推荐初学者学习掌握。

公式模型四：还是用模型二里用到的例子，在介绍一种方法，使用INDIRECT、SMALL、IF、ROW这几个函数来完成。=INDIRECT("b"&SMALL(IF($A$3:$A$19=$D$3,ROW($3:$19),99),ROW(A1)))数据源不需要排序，是数组公式，这里是把模型一的index换成了indirect函数，了解两个函数引用区别的话自然也就明白模型四的原理了。

这里需要介绍另一种排除错误的方法，就是t函数，上面的公式可以变成=T(INDIRECT("b"&SMALL(IF($A$3:$A$19=$D$3,ROW($3:$19),99),ROW(A1))))三键结束下拉即可=INDIRECT("b"&SMALL(IF($A$3:$A$19=$D$3,ROW($3:$19),99),ROW(A1)))&""而在公式后面加&""也是一种排错的方法。可以在前面的公式里使用一下这两种排错方法，需要说明一点的是，一般是结果为文本型的这两个方法时候可以用。

当然，对于发烧友级别的朋友来说，还有其他的思路，不过相对就很难理解了，这里不去一一介绍了，能够理解并且能够套用上面说的一个或者几个公式模型就已经很不错了，由于水平有限，以上解释中难免有不足之处，对于发现的问题希望各位表友可以予以指正，这里不胜感激。同时对于解释不甚理解的朋友也可以加入讨论群进行询问

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。