anyone know why this function will return SBA as a value but never CRE? =IF(SEARCH(“SBA”,AB2,1),“SBA”,IF(SEARCH(“CRE E*”,AB2,1),“CRE”,IF(SEARCH(“REAL”,AB2,1),“CRE”)))
If SBA isn’t found this formula returns #VALUE and never goes to the nested if statements. Try this instead: IF(iserror(SEARCH(“SBA”,AB2,1))=false,“SBA”,IF(iserror(SEARCH(“CRE E*”,AB2,1))=false,“CRE”,IF(iserror(SEARCH(“REAL”,AB2,1)=false),“CRE”)))
Ah, those elegant Excel if statements. It’s like admiring Lascaux cave art. (no personal attack intended on anyone, I’m just saying that doing complex IFs in Excel is always a big clunky mess).
If SBA is not in AB@, you will never execute the successive tests you intend because the Search function will immediately return #VALUE. See it here: put “A” in AB2 and type =SEARCH(“SBA”,AB2,1) You’ll get #VALUE. what we want to have is 1 or 0 returned so the IF statement can operate. How about this: =IF(NOT(ISERR(SEARCH(“SBA”,AB2,1))),“SBA”,“CREE”) This works but you’ll have to wrap the NOT, ISERR around the successive IFs etc. I’d do something like this =IF(NOT(ISERR(SEARCH(“SBA”,AB2,1))),“SBA”,(IF(OR(NOT(ISERR(SEARCH(“CRE E*”,AB2,1))),NOT(ISERR(SEARCH(“REAL”,AB2,1)))),“CRE”))) it will return “false” if AB2 contains none of CRE E*, REAL or SBA. If you want it to return the value in AB2 where there is no match, just do =IF(NOT(IF(NOT(ISERR(SEARCH(“SBA”,AB2,1))),“SBA”,(IF(OR(NOT(ISERR(SEARCH(“CRE E*”,AB2,1))),NOT(ISERR(SEARCH(“REAL”,AB2,1)))),“CRE”)))),AB2) honestly, these nested IFs are a pain, I usually write some quick code. HTH
Went with this: >>I’d do something like this =IF(NOT(ISERR(SEARCH(“SBA”,AB2,1))),“SBA”,(IF(OR(NOT(ISERR(SEARCH(“CRE E*”,AB2,1))),NOT(ISERR(SEARCH(“REAL”,AB2,1)))),“CRE”))) Thanks ppl.