可以使用INDEX+SMALL+IF+COLUMN组成的数组公式实现，外面再套一个IFERROR函数屏蔽错误值。
考虑到可能会有新手看见这个答案，我写得详细一点。
1、实现效果
2、示例公式
=IFERROR(INDEX($B$2:$B$16,SMALL(IF($D2=$A$2:$A$16,ROW($A$2:$A$16)-1),COLUMN(A:A))),"")
*输入到E2单元格，数组公式需要三键结束输入（CTRL+SHIFT+ENTER），输入完毕后再填充到E2:G7区域。
*公式中有相对、混合和绝对引用，输入公式时要注意。
3、公式分析
本示例使用的就是可以“根据单条件或多条件查询多值”的固定套路（INDEX+SMALL+IF+ROW或者INDEX+SMALL+IF+COLUMN），俗称“万金油”。
下面以从E2单元格“曹操的第1个项目”开始进行分步分析：
①判断条件
根据D2的条件“曹操”，生成条件数组，是“曹操”则返回TRUE，不是“曹操”则返回FALSE。
公式：
=$D2=$A$2:$A$16
*这个数组公式有15个结果。
*选中E2:E16区域，输入公式并三键结束输入（CTRL+SHIFT+ENTER），该数组公式的15个结果会依次填充到这15个单元格内。
*为了数据更清晰，图中手动标记了颜色。
只有第2、12、15行显示为TRUE。
②生成序号
根据条件数组，生成“曹操”对应的行号，是“曹操”则返回当前行号，不是“曹操”则返回FALSE。
公式：
=IF($D2=$A$2:$A$16,ROW($A$2:$A$16))
*选中E2:E16区域，输入公式并三键结束输入（CTRL+SHIFT+ENTER），该数组公式的15个结果会依次填充到这15个单元格内。
重点：我们得到了“曹操”对应的行号，但实际上我们需要的是“曹操”在目标区域A2:A16内对应的序号，因为目标区域是A2:A16，既第1个元素对应的行号是2、第2个元素对应的行号是3。。。第15个元素对应的行号是16，所以将“行号-1”就得到了对应的序号。
公式：
=IF($D2=$A$2:$A$16,ROW($A$2:$A$16)-1)
*选中E2:E16区域，输入公式并三键结束输入（CTRL+SHIFT+ENTER），该数组公式的15个结果会依次填充到这15个单元格内。
在目标区域A2:A16中，“曹操”对应的序号为1、11、14。
③输出序号
利用SMALL和COLUMN函数依次输出对应的序号
=SMALL(IF($D2=$A$2:$A$16,ROW($A$2:$A$16)-1),COLUMN(A:A))
*数组公式，需要三键结束输入（CTRL+SHIFT+ENTER）。
输入完毕后，将公式向右填充到F2:G2区域。
在E2单元格引用的COLUMN(A:A)实际值为1，填充到F2、G2时变为COLUMN(B:B)、COLUMN(C:C)，实际值分别为2、3。
再配合SMALL函数，E2、F2、G2分别返回了目标数组中第1、第2、第3个最小值1、11、14。
④输出结果
利用INDEX函数，输出目标序号所对应的内容。
=INDEX($B$2:$B$16,SMALL(IF($D2=$A$2:$A$16,ROW($A$2:$A$16)-1),COLUMN(A:A)))
*输入到E2单元格，三键结束输入（CTRL+SHIFT+ENTER），然后再填充到E2:G2区域。
在上一步骤中，E2、F2、G2输出的结果分别是1、11、14，这一步分别返回了目标区域（B2:B16）中第1、11、14个元素“项目1”、“项目11”和“项目14”。
⑤屏蔽错误
将上一步的公式填充到目标区域E2:G7。
右下角有三个位置没有匹配到对应的值，返回了错误值，这个错误值可以通过在外面套IFERROR函数来屏蔽。
=IFERROR(INDEX($B$2:$B$16,SMALL(IF($D2=$A$2:$A$16,ROW($A$2:$A$16)-1),COLUMN(A:A))),"")
*输入到E2单元格，三键结束输入（CTRL+SHIFT+ENTER），然后再填充到E2:G7区域。
目标效果达成。
4、特别说明
①以上公式全部是数组公式，需要三键结束输入（CTRL+SHIFT+ENTER）。
②公式中有相对、混合和绝对引用，输入公式时要注意。
③因为同一个条件的不同数据是向右依次输出的，所以使用的是COLUMN(A:A)，如果数据需要向下依次输出，则需要把COLUMN(A:A)换成ROW(1:1)。
④熟悉这个套路以后，还可以实现多条件查询多值功能。
有问题请留言。 |
