当我们使用VLOOKUP函数进行查找时，如果查找值在查找列中多次出现，VLOOKUP默认只会返回第一次匹配到的数据。
那么，我们如何突破这一限制，实现一对多的查询效果呢？如下图：
步骤详解
1、构造辅助列：在数据旁边添加一列作为辅助列，用于计算每个姓名出现的次数。这可以通过在辅助列的第一个单元格中输入公式=COUNTIF($B$2:B2,$I$2)，并向下拖动填充）来实现。每次改变姓名时，辅助列会自动计算并显示序号。
2、构造查找值：VLOOKUP函数的第一参数
现在不能只单元查找如“杜磊”这个名字，要将需要查找的姓名与序号组合起来，形成新的查找值。比如，“杜磊”的查找值要变成是“杜磊1”、“杜磊2”，“杜磊3”，“杜磊4”等。这可以通过=$I$2&ROW(1:1)的公式，来把杜磊变成多个值。
为什么要用ROW(1:1)呢? 如果你在单元格里输入=ROW(1:1)，返回的值是1，同样=ROW(2:2)返回值是2，以些类推。为了公式向下拉时，序号能自动变成2，3，……，不需要手动更改。
这样就可以确定VLOOKUP第一参数：（查找值的公式）$I$2&ROW(1:1)
3、构造查找区域：VLOOKUP第二参数将包含姓名、序号和分数的数据区域准备好。确保姓名和序号已经组合成唯一标识，并且分数列紧挨着它们。
VLOOKUP第二参数：IF({1,0},$B$2:$B$13&$A$2:$A$13,$C$2:$C$13)
4、使用VLOOKUP进行查找：VLOOKUP第三、四参数
VLOOKUP第三参数与第四参数，见下图的标示。
VLOOKUP最终的公式是=IFNA(VLOOKUP($I$2&ROW(1:1),IF({1,0},$B$2:$B$13&$A$2:$A$13,$D$2:$D$13),2,0),"")
前面加上IFNA函数，是因为有些姓名重复个数是2，有些是3个，等等，公式向下拉时会出现#N/A,为了显示效果。加IFNA函数进行判断。
总结
通过上述步骤，我们可以有效地使用VLOOKUP函数实现一对多的查询效果。虽然过程稍显复杂，但一旦掌握了这种方法，你将能够更灵活地处理Excel中的多值查询问题。记得，关键在于构造出唯一的查找值和动态的查找区域。
评论 (0)