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.

• Welcome. Please add a brief description of your search/research efforts as is suggested in How to Ask. – Rubén Feb 6 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 at 16:20
• Please edit the question to show what you tried. – Rubén Feb 6 at 16:22
• I’ve edited it. – AZOM Feb 6 at 17:04
• If you share a sample (editable) google sheet link, I can show you how to do it. – MattKing Feb 6 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 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 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 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 at 19:18
• Thanks a lot, you’ve helped me a lot. :) – AZOM Feb 19 at 19:20