Option for FILTER ISNA MATCH but for sensitive case and can be used on columns with different total-CodePudding

To access the Google Sheets for tests click here

Column A in Page Registered → total 10 rows:

Arsenal de Sarandi
Palmeiras
Vasco
Flamengo
Goiás
Barcelona B
Almeria

Columns A,B in Page News using =FILTER(A1:A,ISNA(MATCH(A1:A,Registered!A1:A,0))) in B1. total 6 rows:

Arsenal De Sarandi           Barcelona
Palmeiras
Vasco
Flamengo
Goiás
Barcelona

Expected Result in Page News:

Arsenal De Sarandi           Arsenal De Sarandi
Palmeiras                    Barcelona
Vasco
Flamengo
Goiás
Barcelona

I tried:

=FILTER(A1:A,REGEXMATCH(Registered!A1:A,A1:A)=FALSE)

Error:

FILTER has mismatched range sizes. Expected Row Count: 6, Expected Column Count: 1. Actual Row Count: 10, Actual Column Count: 1.

But when it has the same amount of rows, it's still wrong because it finds Barcelona in Barcelona B, so the column of values only returns Arsenal De Sarandi.

So I tried:

=FILTER(A1:A,EXACT(Registered!A1:A,A1:A)=FALSE)

Error:

FILTER has mismatched range sizes. Expected Row Count: 6, Expected Column Count: 1. Actual Row Count: 10, Actual Column Count: 1.

How to perfectly convert the FILTER ISNA MATCH to case sensitive without worrying about different sizes of total lines?

CodePudding user response：

try:

=FILTER(A1:A, REGEXMATCH(A1:A, TEXTJOIN("|", 1, Registered!A1:A))=FALSE)