Filter a Range where Value Doesn't appear in a separate list/range

[uk747]

Hi

Have table below in cells B1:C7
I have a separate list in H2:H4 which contains the cities Leeds, Liverpool and Manchester

I want to Filter B2:C7 where C2:C7(Cities) aren't Contained in H2:H4 (List of cities Leeds, Liverpool, Manchester)

Result should be 3 below as their city isn't in H2:H4. I know I could write is as =FILTER(B2:C7,(C2:C7<>"Leeds")*(C2:C7<>"Liverpool")*(C2:C7<>"Manchester"))
but wanted something more dynamic as these wont always be the same cities and there maybe more or less than 3 in a range
Bob Dublin
Bill Belfast
Anna Glasgow

Name	City
Bob	Dublin
Bill	Belfast
Allan	Liverpool
Ted	Manchester
Mary	Leeds
Anna	Glasgow

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H
1		Name	City
2		Bob	Dublin		Bob	Dublin		Leeds
3		Bill	Belfast		Bill	Belfast		Liverpool
4		Allan	Liverpool		Anna	Glasgow		Manchester
5		Ted	Manchester
6		Mary	Leeds
7		Anna	Glasgow
8
Sheet4

Cell Formulas
Range	Formula
E2:F4	E2	=FILTER(B2:C10,ISNA(XMATCH(C2:C10,H2:H10)))
Dynamic array formulas.

 

[uk747]

Thanks Fluff.

 

[Fluff]

You're welcome & thanks for the feedback.