Help with "Contains" formula

  • 0
  • 1
  • Question
  • Updated 1 year ago
  • Answered
Hi,

I am trying to create a formula that indicates the following:

IF [field 1- text multiple choice] contains "GM" AND [field 2 - text] contains "GM", display text "GM Opp/GM Agency"
IF [field 1- text multiple choice] contains "AA" AND [field 2 - text] contains "GM", display text "AA Opp/GM Agency"
IF [field 1- text multiple choice] contains "AA" AND [field 2 - text] contains "AA", display text "AA Opp/AA Agency"

I think that the AND portion if the formula is messing me up, but can't figure out how to fix. Below is the current formula I'm working with:

If(
(Contains([GM/AA Market - Opp],"GM" AND Contains([Agency/Client Market - Consolidated], "GM"), "GM Opp/Agency",
(Contains([GM/AA Market - Opp], "AA") AND Contains([Agency/Client Market - Consolidated],"GM"), "GM Opp/AA Agency",
(Contains([GM/AA Market - Opp], "AA"), AND Contains([Agency/Client Market - Consolidated],"AA"), "AA Opp/Agency", "NA"))

Thank you!
Photo of Coleen Schmitt

Coleen Schmitt

  • 524 Points 500 badge 2x thumb

Posted 1 year ago

  • 0
  • 1
Try this fix

If(
Contains([GM/AA Market - Opp],"GM") AND Contains([Agency/Client Market - Consolidated], "GM"), "GM Opp/Agency",

Contains([GM/AA Market - Opp], "AA") AND Contains([Agency/Client Market - Consolidated],"GM"), "GM Opp/AA Agency",

Contains([GM/AA Market - Opp], "AA"), AND Contains([Agency/Client Market - Consolidated],"AA"), "AA Opp/Agency", "NA")
Photo of Coleen Schmitt

Coleen Schmitt

  • 524 Points 500 badge 2x thumb
Still getting the following error message: "Please check the syntax of your formula. Look for mismatched parentheses, missing quotes, or extra brackets."

Please post the formula and the complete error message.
Photo of Coleen Schmitt

Coleen Schmitt

  • 524 Points 500 badge 2x thumb
Here is the error message and formula:

Formula syntax error

Please check the syntax of your formula. Look for mismatched parentheses, missing quotes, or extra brackets.


If(
Contains([GM/AA Market - Opp],"GM") AND Contains([Agency/Client Market - Consolidated], "GM"), "GM Opp/Agency",
Contains([GM/AA Market - Opp], "AA") AND Contains([Agency/Client Market - Consolidated],"GM"), "GM Opp/AA Agency",

Contains([GM/AA Market - Opp], "AA"),   AND Contains([Agency/Client Market - Consolidated],"AA"), "AA Opp/Agency", "NA")
The was an extra comma before the underlining started.


If(
Contains([GM/AA Market - Opp],"GM") AND Contains([Agency/Client Market - Consolidated], "GM"), "GM Opp/Agency",
Contains([GM/AA Market - Opp], "AA") AND Contains([Agency/Client Market - Consolidated],"GM"), "GM Opp/AA Agency",

Contains([GM/AA Market - Opp], "AA")   AND Contains([Agency/Client Market - Consolidated],"AA"), "AA Opp/Agency", "NA")
Photo of Coleen Schmitt

Coleen Schmitt

  • 524 Points 500 badge 2x thumb
Perfect, this worked. Thank you!!