partial google sheets string cell comparison

Question

The Primary Sheet has basic info ( narrowed here ), including Column B - a flag to say "Yes" or "No/Stop" Column C - a Company Name

A Second sheet "stopphrases" will have a growing list of words and phrases in it. If one of these words match first sheet called "data" > Column C (Company Name), Column B will updated to a "No"

IE - 1) If stopphrases!sheet, range A1:A25 contains "UPS" 2) And the Primary Sheet has "UPS Shipping" This "partial" match should catch it and insert flag into data.Column B

=MATCH(""&stopphrases!A1:A10&"", C6,0)

I seem to be able to check a partial from a single cell and check the range, but not got the other way - get the partial words from multiple cells and check against a single cell.

I also tried (If Statement) =IF(COUNTA(IFERROR(FILTER(stopphrases!$A$1:$A$25,stopphrases!$A$1:$A$25=C2))),"Yes","No")

The results I am looking for are So again - IF StopPhrases!A1:A25 Range has a phrase "UPS" THen any entry in first sheet, Company Name, Column B that CONTAINS "UPS" - IE "UPS Shipping" should flag - this way I can run a condition.

The end result is if a match occurs a "stop" happens and that record doesn't get processed in other spreadsheets that are connected.

Aurielle Perlmann Aurielle Perlmann · Accepted Answer · 2016-05-18T01:33:14

You can use regexmatch or regexextract to do this:

first to make the dynamic list of stopphrases you use a JOIN function, an INDIRECT function so you can use COUNTA to allow for it to automatically add the correct number of phrases,

 =JOIN("|",INDIRECT("E2:E"&COUNTA(E2:E)+1))

Then you use arrayformula and regexmatch to dynamically match your list of stop words:

=ARRAYFORMULA(IF(ISTEXT(B1:B),if(REGEXMATCH(B1:B,$D$1),"STOP","GO"),))

=ARRAYFORMULA(IF(ISTEXT(B1:B),if(REGEXMATCH(LOWER(B1:B),$D$1),"STOP","GO"),))

partial google sheets string cell comparison

1 Answers