I've used this with a great deal of success:
=IF(NOT(ISERROR(MATCH(A1:A6,B1:B6,0))),A1:A6,"")

It is an array function, so you must enter it using Ctrl + Shift + Enter,
not just Enter.

Changes the ranges to suit your needs...


Regards,
Ryan--

--
RyGuy


"Brotherharry" wrote:

I have a list of company names, some of which were dummy companies set
up for testing.
those names are often things like 'dummy company' or 'test no.2
company' etc.

I'm currently doing a long manual workaround which involves doing
multiple iterations of custom autofilter e.g. contains 'test' delete
rows, contains 'demo' delete rows.

I'm trying to create a formula that output of which will be a single
value which I can autofilter against and then delete the offending
rows all in one hit.

i.e. check the text in this cell for the presence of any of the
keywords in this list and if you find one return the value 'found
one'.

I've tried using an array formula e.g.

{=FIND(Keywords!B2:B7,A2)}

and my list looking like
"test"
"demo"
"dummy"

but just get the error #VALUE?

help gratefully received.