求助一公式（尾数相同数据提取前三个）

三票

求助一公式（尾数相同数据提取前三个）

jivy1212

1. =IFERROR(SMALL(IF(MOD($B4:$U4,10)=$AI4,$B4:$U4),COLUMN(A1)),AN4)

复制代码

数组公式右拉下拉

w4275

1. =IFERROR(INDEX(4:4,SMALL(IF(MOD($B4:$U4,10)=$AI4,COLUMN($B:$U)),COLUMN(A1))),"")

复制代码

jo良jo影

=IF(SMALL(IF(RIGHT($B12:$U12,1)*1=$AI12,$B12:$U12,999999),COLUMN()-40)=999999,SMALL(IF(RIGHT($B12:$U12,1)*1=$AI12,$B12:$U12,999999),COLUMN()-41),SMALL(IF(RIGHT($B12:$U12,1)*1=$AI12,$B12:$U12,999999),COLUMN()-40))
放在AO 数组三键

hbzql

=IFERROR(INDEX($B4:$U4,1,SMALL(COLUMN($B:$U)+(MOD($B4:$U4,10)<>$AI4)*10^8,COLUMN(A1))-1),AN4)

Meteor-渣渣

Hontyu

..........

vamiii

=IFERROR(IFERROR(INDEX($B4:$U4,,SMALL(IF(MOD($B4:$U4,10)=$AI4,COLUMN($A$1:$T$1)),COLUMN(A1))),INDEX($B4:$U4,,SMALL(IF(MOD($B4:$U4,10)=$AI4,COLUMN($A$1:$T$1)),COLUMN(A1)-1))),"未找到")

锅仔335

office365

ao4=LET(a,TAKE(FILTER(B4:U4,RIGHT(B4:U4)-AI4=0),,3),EXPAND(a,1,3,MAX(TAKE(a,,-1))))

秃头发师

=IFERROR(OFFSET($A4,,SMALL(IF(--RIGHT($B4:$U4,1)=$AI4,COLUMN($B4:$U4),""),COLUMN(A:A))-1,),AN4)