excel查找问题

丰广磊

求助：excel查找问题，见附件，谢谢

624134745

=TOROW(UNIQUE(FILTER($B$2:$B$16,$A$2:$A$16=D2)))  WPS最新版有效。 低版本不会

丰广磊

624134745 发表于 2024-2-8 13:47
=TOROW(UNIQUE(FILTER($B$2:$B$16,$A$2:$A$16=D2)))  WPS最新版有效。 低版本不会

非常感谢，公司电脑只有excel，没有WPS

qline302

用VBA的代码可实现，要吗？

tmplinshi

数组公式

1. =IFERROR(INDEX($B:$B,SMALL(IFERROR(1/(MATCH($A$2:$A$16&"@"&(1/($A$2:$A$16=$D2)*$B$2:$B$16),$A$1:$A$16&"@"&$B$1:$B$16,0)=ROW($B$2:$B$16))*ROW($B$2:$B$16),""),COLUMN(A$1))),"")

复制代码

祝洪忠-

=IFERROR(INDEX($B:$B,1+MATCH(1,(COUNTIF($D2:D2,$B$2:$B$16)=0)*($A$2:$A$16=$D2),)),"")

mykitami

=TRANSPOSE(UNIQUE(FILTER($B$2:$B$16,$A$2:$A$16=D2)))

一杯绿茶来了

E2
=IFERROR(INDEX($B:$B,MIN(IF(($A$2:$A$16=$D2)*(COUNTIF($D2:D2,$B$2:$B$16)=0),ROW($2:$16),4^8))),"")

yellowhawk

=IFERROR(IF(SMALL(UNIQUE(IF($A$2:$A$16=$D2,$B$2:$B$16,99)),COLUMN(A1))>MAX($B$2:$B$16),"",SMALL(UNIQUE(IF($A$2:$A$16=$D2,$B$2:$B$16,99)),COLUMN(A1))),"")
没想到过年了，还有这么多问题。WPS实测正确

pgp5866

=TOROW(UNIQUE(FILTER(B:B,A:A=D2)))