How do I get REGEXMATCH to look for one term across a range of cells in Google Sheets?
![](https://cdn.statically.io/img/cdn.sstatic.net/Sites/stackoverflow/Img/apple-touch-icon@2.png)
In Google Sheets I have the following formula:
=IF(REGEXMATCH(B1;"offers");"spring";0)
If the cell B1 contains the text "offers" the output will be "spring", otherwise the output will be "0". This works fine but now I want the formular to look at B1 and C1 and if either of them contains "offers" the output should be "spring".
Example Output with formula in column D:
B | C | D |
---|---|---|
test offers test | lorem ipsum | spring |
lorem ipsum | test offers test | spring |
lorem ipsum | lorem ipsum | 0 |
I tried the obvious using
=IF(REGEXMATCH(B1:C1;"offers");"spring";0)
but it gives back a #VALUE!
In the second step I want to use this formula in a nested if function like here:
=IF(REGEXMATCH(B1;"offers");"spring";IF(REGEXMATCH(B1;"shop");"summer";0))
12 Answers
The solution seems to be:
=if(and(arrayformula(regexmatch(B1:C1; "(^| )offers( |$)"))); "spring"; "O")
As modified from user6655984's answer in this post. Note I altered the regex to ensure the pattern you are looking to match is preceded by the start of the line or a space, and is proceeded by a space or the end of the line which ensures it does not fall in the middle of a larger string and handles being at the start or end of the main string.
use:
=IF(REGEXMATCH(B1&C1; "offers"); "spring"; 0)
arrayformula:
=INDEX(IF(REGEXMATCH(B1:B&C1:C; "offers"); "spring"; 0))
ncG1vNJzZmirpJawrLvVnqmfpJ%2Bse6S7zGiorp2jqbawutJobm1sYGl%2Fc4OOoaawZZSkeqp5xp6rZqqVnLK5ucCtmqFlpKR6rbvOpGSfp6JivK%2BxjK2cq6VdlrCzu9KsZJplopa7qLGMqJ1mm5WhubR5yKdkoKefnLmmedI%3D