Google Sheet REGEXMATCH with range

0
votes

I want to search specific text in an entire column, the text to be searched is in another column. I am using below formula:

=ARRAYFORMULA(if(isblank(A2:A),"",IFERROR(if(REGEXMATCH(UPPER(A2:A), B2:B4),"Yes","No"),"Not Found")))

This function is comparing cell A2 with B2, A3 with B3 and so on.... I want to compare A2 with B2:B4, A3 with B2:B3...

Below is the sheet link: https://docs.google.com/spreadsheets/d/164kxDO9aWZzr5qXjvtRlk_tiRoKU1W7Xc-Ig9VH8qzE/edit#gid=495498161

Any help on above will be greatly appreciated....

1
google-sheetsgoogle-sheets-formula

1 Answers

1
votes

Change your formula to

=ARRAYFORMULA(if(isblank(A2:A),"",IFERROR(if(REGEXMATCH(UPPER(A2:A), TEXTJOIN("|", 1, B2:B4)),"Yes","No"),"Not Found")))

Or, in case you want an exact match:

=ARRAYFORMULA(if(isblank(A2:A),"",IFERROR(if(REGEXMATCH(UPPER(A2:A), ".*"&TEXTJOIN("|", 1, B2:B4)&".*"),"Yes","No"),"Not Found")))

Also see the duplicated sheet in the spreadsheet you shared.

See if that works?