Excel一对多查询的思路和方法汇总

查找函数是Excel函数中重要的一类函数，比如Vlookup, Lookup,Hlookup，无论是精确查找还是模糊查找，它们都有一个共同的特征，那就是只能返回一个查找结果，新函数Xlookup倒是可以有多个返回值，但是它主要针对于返回多列的数值，即一个查找值对应于多列数据，该函数可以返回多个值 。

如果一对多存在于一列多行数据里面，该如何根据查找值返回多个结果呢？现在有了新函数Filter()的加持，这个已经不是问题，可以用该函数直接得到结果，因为Filter()函数的原理就是根据查找值在查找范围内找到符合条件数据所对应的位置（可能是多个），再返回同位置下结果列（可能是多列）所对应的结果，所以Filter函数与其说是一个查找函数，还不如说是一个筛选函数。但是如果用的是旧函数，要实现一对多查找，还是得用组合公式，以下就是根据一个案例来解释一对多查找的思路和方法。

如上所示，单元格区域B1:C8为源数据，E1:F1为查找条件，现在要返回所有符合条件的姓名，如果用Filter函数，则可以一步到位，根据班级在B列中查找其对应的位置（多个），然后返回同位置下C列中的值 。

如果是旧的EXCEL版本则可以用以下的思路：

1. 用动态的查找值 。比如单元格B6:B8都是“古龙班”，看起来是一样的，但是实际上还是不同，单元格B6是符合条件的第1返回值 ，B7,B8分别是第2，3个，可以利用这个差异来做文章，这里可以使用row()函数，比如第一行返回是的“1”，往下拉，第2行返回的就是“2”，以此类推。

2.用动态的查找范围。如果查找值相同，我们可以用动态的查找范围，比如单元格范围B6:B7可以返回第1个符合条件的（只能返回1个值 ），B7:B8可以返回第2个符合条件的，B8:B8则是第3个。

3. 用动态的返回值 。这里就可以用到index+small+if+row函数组合了，后面详解。基于以上思路，除了Filter()函数外，再列出5种使用传统函数的方法：

方法2: 辅助列法

首先在A列中输入以下公式：

=COUNTIFS($B$2:B2,$F$1)

然后输入以下公式：

=IFERROR(VLOOKUP(ROW(A1),A:C,3,0),””)

它的好处在于，用辅助列可以判断B2:B8中每个单元格是否等于“古龙班”，如果是的，则进一步返回它是第几次出现的，后面就返回符合条件的第1个，第2个…. 

方法3：同方法2，但不用创建辅助列

完整的公式是：

=IFERROR(VLOOKUP(ROW(A1),IF({1,0},COUNTIF(OFFSET($B$2,,,ROW($1:$7)),$F$1),$C$2:$C$8),2,0),””)

它的原理就在于先用OFFSET($B$2,,,ROW($1:$7)),$F$1)创建7个动态的范围，类似于上面的辅助列，它是以单元格B2为基准，向下，向右偏移都是0，但是高度是动态的，如果是“１”，则表示范围是B2:B2,如果高度是“２”，则表示B2:B3，以此类推，然后再嵌套countif函数进行计数，后面再嵌套if{1,0},人类创建一个二维的数组，最后用Vlookup函数进行查找。

方法4：Index+small+If+Row组合法

完整的公式：

=INDEX(C:C,SMALL(IF($B$1:$B$8=$F$1,ROW($1:$8),4^8),ROW(1:1)))&””

先用If函数将B1:B8中的数值依次同查找条件进行比较，如果相同，返回1-8中的一个（第1个返回1，第2个返回2…)， 如果不同则返回一个很大的值 ，然后嵌套small+row函数，第一行返回符合条件中第1个最小的，第二行返回第2个最小的,以此类推，然后再嵌套index函数即可得到结果。

方法5：Indirect方法

=VLOOKUP($F$1,INDIRECT(“B”&SMALL(IF($B$1:$B$8=$F$1,ROW($1:$8),4^8),ROW(1:1))&”:$C$8″),2,0)

原理同方法4，主体依然是small+if+row,假如该做公式组合的第1个结果是“6” （即符合条件的是数据范围中的第6行） 然后嵌套indirect函数创建一个数据范围，即“B”列第6行，至单元格C8，即B6:C8,然后在些范围内查找“古龙班”，返回第2行。

方法6：Vlookup+Offset方法

=IFERROR(VLOOKUP($F$1,OFFSET($B$1:$C$1,SMALL(IF($B$1:$B$8=$F$1,ROW($1:$8)-1,2^8),ROW(A1)),,8),2,0),””)

这里面就是利用offset函数创建一个动态的查找范围， 主体依然是small+if+row, 假如该组合公式的第1个结果是6，则以单元格区域B1:C1为基准，向下偏移6个单元格，向右不偏移，高度固定为8，即单元格区域B6:C13,在些范围内用VLOOKUP进行查找，同理，下一个查找范围为B7:C14,再下一个范围为B8:C15,VLOOKUP每次只能返回范围内的第1个值 ，但由于我们的查找范围是动态的，所以每次都能返回不同的值直到所有符合条件的值都返查找出来。