Comparing lists to extract values not found while modifying the results

[rizzo93]

I want to spill the values in Table2 that are NOT in Table1.

Any value that has a "[C]" in it, I want to remove it leaving only the name.

I can achieve both objectives but can't figure out how to include the names that don't have "[C]" in them as they return #VALUE.

TABLE 1

Name

Clark Kent

Lois Lane

Adam Ant

Wonder Pup

TABLE 2

Name

Clark Kent

Lois Lane

Adam Ant

Wonder Pup

Mickey Mouse [C]

Donald Duck

Spilled Results
=FILTER(SORT(Table2[Name]),ISNA(XMATCH(SORT(Table2[Name]),Table1[Name])))
(these are in column G in my worksheet)

Donald Duck
Mickey Mouse [C]

Trying to remove the "[C]"
=IF(FIND("[",G5#),TRIM(TEXTBEFORE(G5#,"[")),G5#)

#VALUE!
Mickey Mouse

 

[shift-del]

Try this.

Mappe2
	G
2	Donald Duck
Tabelle1

Cell Formulas
Range	Formula
G2	G2	=SORT(FILTER(Table2[Name],(ISERROR(SEARCH("[C]",Table2[Name])))*(ISERROR(SEARCH(Table1[Name],Table2[Name])))))

 

[Fluff]

How about

=SORT(FILTER(TEXTBEFORE(Table2[Name]," [",,,1),ISNA(XMATCH(Table2[Name],Table1[Name]))))

 

[rizzo93]

How about

=SORT(FILTER(TEXTBEFORE(Table2[Name]," [",,,1),ISNA(XMATCH(Table2[Name],Table1[Name]))))

You nailed it, @Fluff ! Thank you!

@shift-del thanks for your input as well.

 

[Fluff]

Glad we could help & thanks for the feedback.