Help : if(search(x);y;z) does not assign else value to cell but #VALUE!


Posted by Gert Harmsen on December 22, 2000 4:24 AM

My problem in Excel 97.
Cell A1 contains a textstring with :
"i am very poor in working with excel"
Cell A2 contains a textstring with :
"i am very good in working with excel"

I want a formula which assigns to cell B1 the string "OK" if "poor" is part of cell A1 otherwise it must fill B1 with "NOK". Same for B2 B3 etc

The formula in B1
=if(search("poor";A1;1)>0;"OK";"NOK") gives OK

The formula in B2
=(if(search("poor";A2;1>0;"OK";"NOK") gives #VALUE! but i want NOK

What is the correct formula which i have to use which gives me my NOK and which i can expand with more strings to check a1 for.

Any help is appreciated



Posted by cpod on December 22, 2000 5:13 AM


=IF(ISERROR(SEARCH("poor",A2)),"NOK","OK")