I'm looking for a way to search a word in a range B2:G10 so it would return yes if the word is in the range, or no if isn't.

Ex: the formula searches in the ranges B1, B2 and B3 each with a different word (ex: hi, hello, goodbye) and when the word good is searched, it gives me no, and if the word is hello, it gives me yes.

I've tried searching on this: Google Sheets formula for "if contains", but when I try the formulas, they are not working.

– Rubén
Feb 6 '20 at 16:17
• I’ve seen some people try do do this, but a lot use substring or formulas that doesn’t work when I try it.
– AZOM
Feb 6 '20 at 16:20
• Please edit the question to show what you tried.
– Rubén
Feb 6 '20 at 16:22
• I’ve edited it.
– AZOM
Feb 6 '20 at 17:04
• If you share a sample (editable) google sheet link, I can show you how to do it. Feb 6 '20 at 22:36

Here's a quick way to do it with if() and regexmatch() on a spreadsheet example

I used this formula

=if(
sum(ArrayFormula(
if(REGEXMATCH(\$B\$2:\$G\$10,B14),1,0))
)>0,"yes","no")

\$B\$2:\$G\$10 is the range where you search for a word in B14. It converts the range with false or true based on the match, then IF replace everything with 1 or 0. You could also use switch().

• I know I’m late, but is there a way to ignore the capital letters in your search function?
– AZOM
Feb 18 '20 at 20:13
• regexmatch() use a regex so it's capital sensitive. To solve this problem you can use lower() :=if( sum(ArrayFormula( if(REGEXMATCH(lower(\$B\$2:\$G\$10),lower(B14)),1,0)) )>0,"yes","no")
– pjmg
Feb 18 '20 at 21:35
• I’ve just tried it, and the only problem is that if I write like: hel and I have “hello” in the document, it will say yes, but it should say no.
– AZOM
Feb 19 '20 at 15:07
• @AZOM you can add operator ^ and \$ to indicate where to start and where to stop. Exemple for exactly hel you can type ^hel\$ OR directly in the formula, juste replace lower(B14) with lower("^"&B14&"\$"). This way, you'll always look for exactly what's in B14. I've updated the spreadsheet example.
– pjmg
Feb 19 '20 at 19:18
• Thanks a lot, you’ve helped me a lot. :)
– AZOM
Feb 19 '20 at 19:20