I have the following formula on a calculated field that searches the value funnel status for values, then sets as live or active. Rather than search does the field contain a specific value, I would like to be have a check if the field equals. How would the formula change?

=IF(ISNUMBER(SEARCH("EarlyEngagement",[funnel status])),"active",IF(ISNUMBER(SEARCH("DRFP (Active) ",[funnel status])),"active",IF(ISNUMBER(SEARCH("DRFP (Non-Active) ",[funnel status])),"active",IF(ISNUMBER(SEARCH("Post Proposal Submission ",[funnel status])),"active",IF(ISNUMBER(SEARCH("DRFP Questions ",[funnel status])),"live",IF(ISNUMBER(SEARCH("RFI",[funnel status])),"live",IF(ISNUMBER(SEARCH("RFP",[funnel status])),"live",IF(ISNUMBER(SEARCH("BAFO",[funnel status])),"live",IF(ISNUMBER(SEARCH("Orals",[funnel status])),"live",IF(ISNUMBER(SEARCH("Quals",[funnel status])),"live",IF(ISNUMBER(SEARCH("ENs",[funnel status])),"live",IF(ISNUMBER(SEARCH("FRP",[funnel status])),"live",IF(ISNUMBER(SEARCH("Bridge",[funnel status])),"live",IF(ISNUMBER(SEARCH("Contract MOD",[funnel status])),"live",IF(ISNUMBER(SEARCH("Unsolicited",[funnel status])),"live",""))))))))))))))) 

So basically if:

funnel status = EarlyEngagement (Active), EarlyEngagement (Non-Active), DRFP (Active), DRFP (Non-Active) or Post Proposal Submission then field would equal active

funnel status = DFRP Questions, RFI, RFP, BAFO, Orals Prep, Orals, Quals, ENs, FRP, Bridge, Contract MOD or Unsolicited would equal live

I tried this but it's not working

=IF(("Early Engagement",[funnel status]),"active",IF("DRFP(Active)",[funnel status]),"active",IF("DRFP(Non-Active)",[funnel status]),"active",IF("PostProposal Submission",[funnel status]),"active",IF("DRFPQuestions",[funnel status]),"live",IF("RFI",[funnel status]),"live",IF("RFP",[funnel status]),"live",IF("BAFO",[funnel status]),"live",IF("Orals",[funnel status]),"live",IF("Quals",[funnel status]),"live",IF("ENs",[funnel status]),"live",IF("FRP",[funnel status]),"live",IF("Bridge",[funnel status]),"live",IF("Contract MOD",[funnel status]),"live",IF("Unsolicited",[funnel status]),"live","")

Use an OR to do your multiple tests OR(cond 1, cond 2, cond3....)

OR([funnel status] = "EarlyEngagement (Active)", [funnel status] = "EarlyEngagement (Non-Active)", .....


IF(OR(x1,x2,x3), "active", IF(OR(y,y,y), "live" , ""))
  • so would the calculated field look like this: – pinkstonmatt Jun 19 '17 at 18:28
  • tried this and got syntax error =IF(OR([funnel status] = “Early Engagement (Active)”, [funnel status] = “Early Engagement (Non-Active)”, [funnel status] = “DRFP (Active)”, [funnel status] = “DRFP (Non-Active)”, [funnel status] = “Post Proposal Submission”), "active", IF(OR([funnel status] = “DRFP Question”, [funnel status] = “RFI”, [funnel status] = “RFP”), "live" , "")) – pinkstonmatt Jun 19 '17 at 18:39
  • looks OK to me, everything seems to match up. When I copy/pasted it, I got smart quotes instead of straight quotes, so double check your quotes. – Robin in Or Jun 19 '17 at 22:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.