如何使用VLOOKUP或其他公式处理重复查找值以返回对应不同行数据？

解决方法 #

前提约定 #

我们默认：

• 存储源数据的工作表名称为Sheet1，表1的Outcome、Type、Cost三列数据范围为A2:C9（表头在第1行，数据从第2行到第9行）
• 待填充的目标表工作表名称为Sheet2，已填好的Outcome字段从A2单元格开始向下排列，需要填充的Type列为B列、Cost列为C列

方案1：适用于Excel 365 / Excel 2021及以上版本（最简单） #

如果你不需要提前固定目标表的Outcome行顺序，只是要提取所有Outcome为a、c、d、e的源数据记录，直接在Sheet2任意空白单元格输入以下公式，回车即可自动溢出所有匹配结果，直接得到你要的表3效果：

=FILTER(Sheet1!A2:C9, (Sheet1!A2:A9="a")+(Sheet1!A2:A9="c")+(Sheet1!A2:A9="d")+(Sheet1!A2:A9="e"))

如果你已经提前在Sheet2的A列填好了所有Outcome值（包括重复的两个c、两个e），按以下步骤操作：

1. 加辅助列：在Sheet2的D2单元格输入公式=COUNTIF(A$2:A2,A2)，下拉到所有数据行，作用是统计当前行的Outcome是第几次出现
2. 填充Type列：在B2单元格输入以下公式，下拉到所有行

=INDEX(Sheet1!B$2:B$9,SMALL(IF(Sheet1!A$2:A$9=A2,ROW(Sheet1!A$2:A$9)-ROW(Sheet1!A$2)+1),D2))

3. 填充Cost列：在C2单元格输入以下公式，下拉到所有行

=INDEX(Sheet1!C$2:C$9,SMALL(IF(Sheet1!A$2:A$9=A2,ROW(Sheet1!A$2:A$9)-ROW(Sheet1!A$2)+1),D2))

方案2：适用于Excel 2019及更早版本 #

操作步骤和方案1的第二种场景完全一致，唯一区别是输入完INDEX+SMALL组合的数组公式后，需要按Ctrl+Shift+Enter三键组合触发数组计算，否则会返回错误值。

原理说明 #

VLOOKUP的逻辑是匹配到第一个符合条件的结果就停止检索，因此遇到重复匹配值只能返回第一条结果。上述方案通过IF函数筛选出当前Outcome对应的所有源数据行号，再通过SMALL函数结合辅助列的计数，取出第N次匹配的行号，最后用INDEX返回对应位置的字段值，即可实现重复Outcome对应返回所有匹配结果的需求。

内容的提问来源于stack exchange，提问作者Kuro