New answers tagged

0

Alternative 1 Instead of using comparison like B2=Casula!$A:$A use NOT(ISNA(MATCH(B2,Casula!$A$2:$A,0))). Example =IF(NOT(ISNA(MATCH(B2,Casula!$A$2:$A,0))),"Casula",IF(NOT(ISNA(MATCH(B2,Miranda!$A$2:$A,0))),"Miranda",IF(NOT(ISNA(MATCH(B2,Parramatta!$A$2:$A,0))),"Parra",IF(NOT(ISNA(MATCH(B2,Leichhardt!$A$2:$A,0))),"Leich","NOT IN USE")))) NOTE: Using long ...


1

In C2 enter this formula : =filter( ArrayFormula(if(REGEXMATCH(A2:A,TEXTJOIN("|",true,B2:B)),,A2:A)), ArrayFormula(if(REGEXMATCH(A2:A,TEXTJOIN("|",true,B2:B)),,A2:A))<>"" ) Breakdown of the formula : =REGEXMATCH(A2,TEXTJOIN("|",true,B2:B)) Regexmatch returns TRUE or FALSE if A2 contains any of the words in column B. TEXJOIN ...


0

Try this in F10 for the tenth row : =textjoin(",",false,ArrayFormula(iferror(REGEXEXTRACT(A10,TRANSPOSE(ARRAYFORMULA(TRIM(SPLIT(E10, ",")))))))) Basically REGEXTRACT() accept arrays.


Top 50 recent answers are included