Searching multiple columns for text string

[DarkVignette]

HI everyone

I need to search two columns for a text string, and return a 'yes' or 'no'. The challenge I have is that the data is entered by a number of different people and they use a combination of lower and uppercase letters.

Currently I have this (thanks to a little worki on Google)

=IF(ISERROR(FIND(LOWER("discontinue"),LOWER(Z155),1)),"No","Yes")

But I need to extend this to search for the text string '"discontinue" on column Y too. I have tried

=IF(ISERROR(FIND(LOWER("discontinue"),LOWER(Y155:Z155),1)),"No","Yes")

but this doesn't return a correct response. I've also tried playing around with OR but can't seem to get this to function correctly, probably due to my limited skills.

Can anyone help?

P

 

[Andrew Poulsom]

Welcome to MrExcel.

Use SEARCH if you want to ignore case:

=IF(SUMPRODUCT(--ISNUMBER(SEARCH("discontinue",Y155:Z155))),"Yes","No")

 

[Rick Rothstein]

Give this formula a try...

=IF(COUNTIF(Y155:Z155,"*discontinue*"),"Yes","No")

 

[Momentman]

SEARCH is a non-case sensitive function as opposed to FIND, rather than using FIND&LOWER/UPPER, you can simply use SEARCH

 

[DarkVignette]

Brilliant,thanks Andrew - exactly what I needed.

Paul

 

[DarkVignette]

Thanks for all the replies -all really helpful. Still finding my way around Excel so sometimes I miss the obvious!