FILTER FORMULA

[uk747]

Hi

I would like help with the following 2 formulas
Filter A:B where D IS PIG OR D ISNT Hamster or Squirrel

Result would be:

a	n
c	p
e	r
g	t
i	v
k	x
l	y
m	z

Then Filter A:B where D IS PIG OR D ISNT Hamster or Squirrel AND C Doesn’t Contain ca

Result would be:

c	p
e	r
i	v
k	x
m	z

Data is in Columns A2:D14

a	n	cabbage	pig
b	o	can	Hamster
c	p	x	dog
d	q	x	Squirrel
e	r	x	Rabbit
f	s	x	Squirrel
g	t	scan	cat
h	u	x	Hamster
i	v	x	pig
j	w	x	Hamster
k	x	x	dog
l	y	can	pig
m	z	x	cat

Thanks

 

[kweaver]

For the first case, how about: =FILTER(A2:B14,(D2:D14="pig")+((D2:D14<>"Hamster")*(D2:D14<>"Squirrel")))

 

[kweaver]

Second case, how about this: =FILTER(A2:B14,((D2:D14="pig")+((D2:D14<>"Hamster")*(D2:D14<>"Squirrel")))*ISERROR(FALSE=(SEARCH("ca",C2:C14))))

 

[uk747]

For the first case, how about: =FILTER(A2:B14,(D2:D14="pig")+((D2:D14<>"Hamster")*(D2:D14<>"Squirrel")))

Thanks that one works. I had the + and * wrong way round

Do you know how I can also Exclude anything that included ca

I tried below but didnt work
=FILTER(A2:B14,(D2:D14="pig")+((D2:D14<>"Hamster")*(D2:D14<>"Squirrel"))*ISNUMBER(SEARCH("<>*ca*",C2:C14)))

 

[uk747]

Second case, how about this: =FILTER(A2:B14,((D2:D14="pig")+((D2:D14<>"Hamster")*(D2:D14<>"Squirrel")))*ISERROR(FALSE=(SEARCH("ca",C2:C14))))

Thanks. was posting my atempted solution which didn't work just after you. was close but no cigar