How do I get REGEXMATCH to look for one term across a range of cells in Google Sheets?

Publish date: 2024-05-14

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:

BCD
test offers testlorem ipsumspring
lorem ipsumtest offers testspring
lorem ipsumlorem ipsum0

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)) 
1

2 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