Return all items in a table column not equal multiple criteria

[john-paul]

Hi,

I want to return all products from a column in a table where the colour is not Red or Blue.

The table name is in cell D1. I tried the below but couldn’t get it to work for two or more colours.

=FILTER(INDIRECT($D$1&"[Product]"),INDIRECT($D$1&"[Colour]")<>{"Red","Blue"},"")

So for the below, I would like to return Bus and Truck.

Product	Colour
Bike	Red
Car	Blue
Bus	Yellow
Cycle	Blue
Skateboard	Red
Truck	Green

Thanks
John

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Try

=FILTER(INDIRECT($D$1&"[Product]"),(INDIRECT($D$1&"[Colour]")<>"Red")*(INDIRECT($D$1&"[Colour]")<>"Blue"),"")

 

[nemmi69]

Try

 

[john-paul]

Thank you Fluff and nemmi69 for your help, really appreciated.

 

[Fluff]

Glad we could help & thanks for the feedback

 

[nemmi69]

Glad to help out