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 '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.
    – MattKing
    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


$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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.