0

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.

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

1 Answer 1

0

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().

5
  • I know I’m late, but is there a way to ignore the capital letters in your search function?
    – AZOM
    Feb 18, 2020 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, 2020 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, 2020 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, 2020 at 19:18
  • Thanks a lot, you’ve helped me a lot. :)
    – AZOM
    Feb 19, 2020 at 19:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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