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.

 

[FormR]

Hi, here is an alternative you could try:

=IF(SUM(COUNTIF(K2,"*"&{"(spar)","(budgens)","(londis)","(mccoll)","(one stop)","(thorntons)","(costcutter)","(mace)","(majestic)","(post office)","(hospice)","(hospital)","(wine)","(victoria)"}&"*")),"Yes","No")

 

[mohammad2060]

hi
you should use this formula:
=IF(ISNUMBER(SEARCH("(spar)",K2)),"Yes",IF(ISNUMBER(SEARCH("(budgens)",K2)),"Yes",IF(ISNUMBER(SEARCH("(londis)",K2)),"Yes",IF(ISNUMBER(SEARCH("(mccoll)",K2)),"Yes",IF(ISNUMBER(SEARCH("(one stop)",K2)),"Yes",IF(ISNUMBER(SEARCH("(thorntons)",K2)),"Yes",IF(ISNUMBER(SEARCH("(costcutter)",K2)),"Yes",IF(ISNUMBER(SEARCH("(mace)",K2)),"Yes",IF(ISNUMBER(SEARCH("(majestic)",K2)),"Yes",IF(ISNUMBER(SEARCH("(post office)",K2)),"Yes",IF(ISNUMBER(SEARCH("(hospice)",K2)),"Yes",IF(ISNUMBER(SEARCH("(hospital)",K2)),"Yes",IF(ISNUMBER(SEARCH("(wine)",K2)),"Yes",IF(ISNUMBER(SEARCH("(victoria)",K2)),"Yes","No"))))))))))))))

 

[skuddyb]

Hi, here is an alternative you could try:

=IF(SUM(COUNTIF(K2,"*"&{"(spar)","(budgens)","(londis)","(mccoll)","(one stop)","(thorntons)","(costcutter)","(mace)","(majestic)","(post office)","(hospice)","(hospital)","(wine)","(victoria)"}&"*")),"Yes","No")

Hi, This doesn't return #VALUE! any more but only seems to search for "spar" and none of the other criteria?

 

[bosco_yip]

Or,

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

Regards

 

[skuddyb]

hi
you should use this formula:
=IF(ISNUMBER(SEARCH("(spar)",K2)),"Yes",IF(ISNUMBER(SEARCH("(budgens)",K2)),"Yes",IF(ISNUMBER(SEARCH("(londis)",K2)),"Yes",IF(ISNUMBER(SEARCH("(mccoll)",K2)),"Yes",IF(ISNUMBER(SEARCH("(one stop)",K2)),"Yes",IF(ISNUMBER(SEARCH("(thorntons)",K2)),"Yes",IF(ISNUMBER(SEARCH("(costcutter)",K2)),"Yes",IF(ISNUMBER(SEARCH("(mace)",K2)),"Yes",IF(ISNUMBER(SEARCH("(majestic)",K2)),"Yes",IF(ISNUMBER(SEARCH("(post office)",K2)),"Yes",IF(ISNUMBER(SEARCH("(hospice)",K2)),"Yes",IF(ISNUMBER(SEARCH("(hospital)",K2)),"Yes",IF(ISNUMBER(SEARCH("(wine)",K2)),"Yes",IF(ISNUMBER(SEARCH("(victoria)",K2)),"Yes","No"))))))))))))))

Hi Mohammad, cheers for this but again, only seems to search for "spar" and none of the other criteria?

 

[skuddyb]

Or,

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

Regards

Hi, same as the other suggestions, just returns it for spar?

 

[FormR]

Hi, This doesn't return #VALUE! any more but only seems to search for "spar" and none of the other criteria?

Hi, it seems to work OK for me.

Excel 2013
	K	L
2	dsasd (londis) dsa	Yes
3	dfs,'f,ds (wine) dsadsa	Yes
4	dsl;df (thorntons) dsad	Yes
5	sdadsa ds sda sd	No
6	dsads dsa dsda	No
7	dsadsa (hospital) sdasaa	Yes
Sheet1

Cell Formulas
Range	Formula
L2	=IF(SUM(COUNTIF(K2,"*"&{"(spar)","(budgens)","(londis)","(mccoll)","(one stop)","(thorntons)","(costcutter)","(mace)","(majestic)","(post office)","(hospice)","(hospital)","(wine)","(victoria)"}&"*")),"Yes","No")

 

[Aladin Akyurek]

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

 

[mohammad2060]

Hi skuddyb ;
But i exemine this formula with the other criteria,
and it works correctly.