如何ABC出现的三个数字在DEF的各五个数字给标出来

zhangzejay

sayhi95

1. =IFERROR(INDEX({"D","E","F"},MATCH(,MMULT(-ISERR(FIND(A1:C1,TRANSPOSE(D1:F1))),Z1:Z3-1),)),"")

复制代码

数组公式这样简短一点

1. =IFERROR(MID("DEF",MATCH(,MMULT(-ISERR(FIND(A1:C1,TRANSPOSE(D1:F1))),Z1:Z3-1),),1),"")

复制代码

1. =MID("DEF",IFNA(MATCH(,MMULT(-ISERR(FIND(A1:C1,TRANSPOSE(D1:F1))),Z1:Z3-1),),9),1)

复制代码

scyss

用了个笨办法，前提 3个中，只有1个符合，多个符合就不适合了

=IFERROR(CHOOSE(1*(SUMPRODUCT(9-LEN(SUBSTITUTE(D1,A1:C1,"")))>2)+2*(SUMPRODUCT(9-LEN(SUBSTITUTE(E1,A1:C1,"")))>2)+3*(SUMPRODUCT(9-LEN(SUBSTITUTE(F1,A1:C1,"")))>2),"D","E","F"),"")

下拉

江苏永能朱兴华

=IFERROR(MID("DEF",MATCH(3,MMULT(1-ISERR(FIND(A1:C1,TRANSPOSE(D1:F1))),{1;1;1}),),1),"")

zhangzejay

sayhi95 发表于 2018-11-19 15:56
数组公式这样简短一点

如果同时间出现两个或者两个以上能不能算出来呢

sayhi95

zhangzejay 发表于 2018-11-19 17:08
如果同时间出现两个或者两个以上能不能算出来呢

要标记多个的话公式就会长一些

micch

有没有可能结果不唯一？有没有ABC三个数字相同的情况？

1. =IFERROR(CHAR(SUM(IF(MMULT({1,1,1},1-ISERR(FIND(TRANSPOSE(A1:C1),D1:F1)))=3,{68,69,70}))),"")

复制代码

最后一个模拟错误吧

laowuwyx

1. =IFERROR(LOOKUP(1,0/(MMULT(1-ISERR(FIND(A1:C1,TRANSPOSE(D1:F1))),{1;1;1})=3),{"D";"E";"F"}),"")

复制代码

1. =IFERROR(CHAR(67+MATCH(3,MMULT(1-ISERR(FIND(A1:C1,TRANSPOSE(D1:F1))),{1;1;1}),)),"")

复制代码

数组。

micch

1. =LOOKUP(SUM(IF(MMULT({1,1,1},1-ISERR(FIND(TRANSPOSE(A1:C1),D1:F1)))=3,{1,3,5})),{0,"";1,"D";3,"E";4,"DE";5,"F";6,"DF";8,"EF";9,"DEF"})

复制代码

如果考虑结果可能是两个甚至三个的情况，只好用常量了

我走我流

=IFERROR(LOOKUP(1,0/(MMULT(TRANSPOSE(N(ISNUMBER(FIND(TRANSPOSE(A1:C1),D1:F1)))),ROW(1:3)^0)=3),CHAR(65+ROW($3:$5))),"")数组下拉最后1个模拟错误，答案提示E