check text in cell for at least one keyword from a list on anothersheet

[Brotherharry]

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.

 

[Ron Coderre]

This formula returns TRUE for items containing a keyword:

=COUNT(INDEX(SEARCH(Keywords!$B$2:$B$7,A2),0))>0


Does that help?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[ryguy7272]

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--

 

[Brotherharry]

Nope, just get a column of FALSEs...

This formula returns TRUE for items containing a keyword:

=COUNT(INDEX(SEARCH(Keywords!$B$2:$B$7,A2),0))>0

Does that help?
Post back if you have more questions.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)

 

[Brotherharry]

also get just a column of blanks....?
chances of two independent approaches both producing the same lack of
effect are remote, so wonder if it's my settings somewhere?
using Excel 2007