Quote:
Originally Posted by whatsup
Oh Bruno, 
I didn't expect anything else from you 
But your formula is great !!! It doesn't look at parts of a value, as the other formula does, but that's just a detail we don't really care for, isn't it? |
Your right, I supposed it was a non-sense to look at parts of a value,
but if you like:
Case 1
====
In A1:A10 we have "abc"
in E1:E10 we have "abc"
=(IF(ISERROR(MATCH(A1,$E$1:$E$10,0)),"XXX",A1))
and you get "abc"
Case 2
====
In A1:A10 we have "abc"
in E1:E10 we have "ZZabcKKK"
{=IF(SUM((IF(ISERROR(FIND(A1,$E$1:$E$10)),0,1)))=1 ,A1,"XXX")}
if you want to get "abc"
{=IF(SUM(IF(ISERROR(FIND(A1,$E$1:$E$10)),0,FIND(A1 ,$E$1:$E$10))),INDEX($E$1:$E$10,MATCH(SUM(IF(ISERR OR(FIND(A1,$E$1:$E$10)),0,FIND(A1,$E$1:$E$10))),(F IND(A1,$E$1:$E$10)))),"XXX")}
if you want to get "ZZabcKKK"
Why don't you help me in simplifying the last one?
It looks so heavy!
Bruno