Find a text in a String in Excel

[swap_dj9]

Hello,

I have to find a text in a string in excel and if found return a yes or no

E.G

Sheet 1: Col1

1286532624 [B27C0094-8835-1CCD-8B83-F2612604A745:abc@xyz.com] [09/17/2013 10:00:21]
1286532624 [B27C0094-8835-1CCD-8B83-F2612604A745:abc@xyz.com] [09/17/2013 10:00:21]
1286536021 [C2E58BE5-604A-DFDC-3D15-5B7AA107167F:lmn@xyz.com] [09/17/2013 10:00:24]
1286536191 [C2E58BE5-604A-DFDC-3D15-5B7AA107167F:lmn@xyz.com] [09/17/2013 10:00:24
1286536191 [C2E58BE5-604A-DFDC-3D15-5B7AA107167F:lmn@xyz.com] [09/17/2013 10:00:24]
1286536192 [C2E58BE5-604A-DFDC-3D15-5B7AA107167F:pqr@xyz.com[09/17/2013 10:00:24]
1286536192 [C2E58BE5-604A-DFDC-3D15-5B7AA107167F:pqr@xyz.com] [09/17/2013 10:00:24]


Sheet 2 : Column 1
pqr@xyz.com
lmn@xyz.com
abc@xyz.com

So i need to find the text "abc@xyz.com" in (sheet 1:colunm1 whole range)
and if is present in any of the rows return "Yes" else "NO"

[ramananhrm]

Please try this file.

[Fotis1991]

Hi and welcome to the forum.

Perhaps 1 way could be this.

In column B of sheet 2 put this and copy down.

=IF(COUNTIF(Sheet1!A1:A1000,"*"&Sheet1!A1&"*"),"yes","no")