or and if statement

[shutls]

I want to make this formula work but i think i have the wrong syntax - can anyone help?

Basically I want it to look at cell b2 and if it contains specific text return the correct value.

=OR(IF(ISNUMBER(SEARCH("ows",B2)),"OWOSSO","error"),(IF(ISNUMBER(SEARCH("sag",B2)),"SAGINAW","error"),IF(ISNUMBER(SEARCH("DAV",B2)),"DAVISON","error"),IF(ISNUMBER(SEARCH("BAY",B2)),"BAY CITY","error"),IF(ISNUMBER(SEARCH("MON",B2)),"MONROE","error"),IF(ISNUMBER(SEARCH("WHI",B2)),"WHITMORELAKE","error")))

 

[lenze]

Try This!!

=IF(ISNUMBER(SEARCH("ows",B2)),"OWOSSO",IF(ISNUMBER(SEARCH("sag",B2)),"SAGINAW",IF(ISNUMBER(SEARCH("DAV",B2)),"DAVISON",IF(ISNUMBER(SEARCH("BAY",B2)),"BAY CITY",IF(ISNUMBER(SEARCH("MON",B2)),"MONROE",IF(ISNUMBER(SEARCH("WHI",B2)),"WHITMORELAKE","error"))))))

lenze

 

[Jonmo1]

=IF(ISNUMBER(SEARCH("ows",B2)),"OWOSSO",IF(ISNUMBER(SEARCH("sag",B2)),"SAGINAW",IF(ISNUMBER(SEARCH("DAV",B2)),"DAVISON",IF(ISNUMBER(SEARCH("BAY",B2)),"BAY CITY",IF(ISNUMBER(SEARCH("MON",B2)),"MONROE",IF(ISNUMBER(SEARCH("WHI",B2)),"WHITMORELAKE","error"))))))

 

[shutls]

can i add more criteria or is 6 the limit?

 

[Jonmo1]

in excel 2003 or lower, you can go up to 7.

in excel 2007, it's almost unlimited.

 

[lenze]

As jonmo1 says, you are limited to 7 nested IFs. But, see here
http://www.cpearson.com/excel/nested.htm
for a workaround. With this you can get up to 49, but the more you have, the SLOWER it will calculate. There is probably a better way using some form of LOOKUP>

lenze

 

[Jonmo1]

I also had another workaround here.

http://www.mrexcel.com/board2/viewtopic.php?t=250383&highlight=


Basically using concatenate to join multiple nested IF's together into 1.