1

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","")
Improve this question

1 Answer 1

Reset to default
1

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

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

so

IF(OR(x1,x2,x3), "active", IF(OR(y,y,y), "live" , ""))
Improve this answer
3
  • so would the calculated field look like this:
    – user63270
    Jun 19, 2017 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" , ""))
    – user63270
    Jun 19, 2017 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, 2017 at 22:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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