Look up a part of the content of a cell

[DaDa]

Hi there, who can help me with this challange??

In the example below you find two columns A and B. In Column C I would like to show (via "yes" or "no" whether the content of the cells in Column B can be retreived - even if it doesn't match the full content - in Column A.

e.g.:
The content of B4 is "ert", the value in C4 is "yes" as "ert" can be found in A6.

Is there a formula that I can use for this?

Row Column A Column B Column C
1 abc/def/efg def yes
2 abc/def/efg abc yes
3 abc/def/efg efg yes
4 abc/def/efg ert yes
5 abc/def/efg ade no
6 abc/def/efg/ert bc yes
7 abc/def/efg def yes
8 abc/def/efg efg yes
9 abc/def/efg zzz no
10 abc/def/efg c/e yes
11 abc/def/efg c/d yes

Thanks already for those who can help me!
Damien

[VBA Noob]

Hi,

Think this might work

=IF(COUNTIF(B:B,B1)>1,"Yes","No")

VBA Noob

[DaDa]

VBA Noob,

Thanks for your suggestion, unfortunately your solution is not returning the values I was expecting.

I might have been not too clear in my problem statement. After all this is what I am after:

If the cell content in column B1 matches - even partly - the content of any cell in column A, return a "yes", otherwise a "no".

Good luck!
damien

[VBA Noob]

Try

=IF(SUMPRODUCT(--(ISNUMBER(FIND(B1,$A$1:$A$11))))>=1,"Yes","No")

VBA Noob

[DaDa]

VBA Noob, You're my Hero!!!!

It works exactly how I wanted it!



Many Thanks!
damien

[VBA Noob]

Glad it helped

Thx for the feedback

VBA Noob