REGEXMATCH to match cell value in more than one other cell

Christian Velev 0
Viewed 30

I used this formula

=if(REGEXMATCH(K11, "BN11|BT11|BZ11|CF11|CL11"),"YES", "NO")

to find if a word in BN11 OR BT11 OR BZ11 OR CF11 OR CL11 is mentioned in cell K11, but it's not working.
What am I doing wrong?

google-sheets-formula google-sheets regex
3 Answers

Try-

=IF(REGEXMATCH(LOWER(K11), "bn11|bt11|bz11|cf11|cl11"),"YES", "NO")

enter image description here

Harun24hr0

EDIT

=IF(REGEXMATCH({C2&F2&I2&L2&O2},LOWER(B2)),"YES","NO")

enter image description here

Original answer

Try this formula

=INDEX(IF(ISNUMBER(SEARCH(H11,I11:K11)),TRUE))

enter image description here

marikamitsos0

Use JOIN with pipe | (Regex OR):

=IF(REGEXMATCH(K11, "(?i)"&JOIN("|",BN11,BT11,BZ11,CF11,CL11)),"YES", "NO")
TheMaster0
Related