|
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件 ★ 免费下载 ★ ★ 使用帮助★
公式一,数组公式ctrl+shift+enter,右拖下拉=IFERROR(IF(COLUMNS($K2:K2)>COUNTIFS($A$2:$A$23,$H2,$C$2:$C$23,$J2),"",INDEX($D$2:$D$23,SMALL(IF(($A$2:$A$23=$H2)*($C$2:$C$23=$J2),ROW($D$2:$D$23)-ROW($D$2)+1),COLUMNS($K2:K2)))),"")
公式二,用到了内置small的AGGREGATE函数,直接enter,右拖下拉
=IFERROR(IF(COLUMNS($K17:K17)>COUNTIFS($A$2:$A$23,$H17,$C$2:$C$23,$J17),"",INDEX($D$2:$D$23,AGGREGATE(15,6,(ROW($D$2:$D$23)-ROW($D$2)+1)/(($A$2:$A$23=$H17)*($C$2:$C$23=$J17)),COLUMNS($K17:K17)))),"")
|
|