Isnumber error

[skuddyb]

Hi Guys,

I have a list of 26,000 small retailers in column K. I need to exclude any that are symbol groups such as Nisa, CostCutter etc. I used this to return Yes or No i column AX as to whether they are suitable or not.

It returns #VALUE!

=IF(ISNUMBER(SEARCH("(spar)",K2)),"Yes","No"), IF(ISNUMBER(SEARCH("(budgens)",K2)),"Yes","No"), IF(ISNUMBER(SEARCH("(londis)",K2)),"Yes","No"), IF(ISNUMBER(SEARCH("(mccoll)",K2)),"Yes","No"), IF(ISNUMBER(SEARCH("(one stop)",K2)),"Yes","No"), IF(ISNUMBER(SEARCH("(thorntons)",K2)),"Yes","No"), IF(ISNUMBER(SEARCH("(costcutter)",K2)),"Yes","No"), IF(ISNUMBER(SEARCH("(mace)",K2)),"Yes","No"), IF(ISNUMBER(SEARCH("(majestic)",K2)),"Yes","No"), IF(ISNUMBER(SEARCH("(post office)",K2)),"Yes","No"), IF(ISNUMBER(SEARCH("(hospice)",K2)),"Yes","No"), IF(ISNUMBER(SEARCH("(hospital)",K2)),"Yes","No"), IF(ISNUMBER(SEARCH("(wine)",K2)),"Yes","No"), IF(ISNUMBER(SEARCH("(victoria)",K2)),"Yes","No")

However if i use just one section for example "spar" it works.

Please help.

 

[skuddyb]

create a list of the relevant search words in a range and name this range keywords. Once done that, invoke:

=if(isnumber(lookup(9.99999999999999e+307,search(keywords,k2))),"yes","no")

god bless your soul