Skip to main content

I’m trying to write a formula which returns either a value from field A or a string within the formula, dependant on the value in fields B & C. Where the values in fields B & C are met then a value within the formula should be returned, when the value in field B is not met then a value from field A should be returned.


My table has 3 fields:



  • Client Name

  • Currency

  • Customer Code


Where ‘Client Name’ is ABC and and ‘Currency’ is USD then return a string from the formula. Where ‘Client Name’ is ABC and and ‘Currency’ is GBP then return a different string from the formula. Where ‘Client Name’ is not ABC then return the ‘Customer Code’.


I’ve tried using nested formulas for the first 2 conditions but I do not know how to include the last condition. Can someone please help with this?

I’m sure you have many other cases which may not be reflected in this proposal, but I’d suggest starting with a SWITCH() function inside a conditional and see if that meets your needs:






Formula field reference



For an overview of formula fields, please refer to the Guide to Formula, Lookup, Count, and Rollup fields.
Formulas may involve functions, numeric operations, logical operations, and text operation...












IF(

   {Client Name} = "ABC",

   SWITCH(

      Currency,

      "USD", "$",

      "GBP", "£"

   ),

   {Customer Code}

)

Hi @Jeremy_Oglesby, apologies for the slow response I’ve been on holiday! Thanks so much for the suggestion I’ve managed to get the SWITCH statement working for the case I outlined. I’m now looking at having 2 separate conditions and then if neither are met the {Customer Code} is returned but I’m having issues writing it. For example:


Client Name=ABC, currency USD= & GBP=£, then if Client Name=DEF, currency USD= & GBP=£, otherwise return the {Customer Code}. I’ve written the below but it’s not working, are you able to point out where I’ve gone wrong?


IF(

{Client name} = “ABC”,

SWITCH(

{Invoice Currency},

“USD”, “",
"GBP", "£"
),
IF(
{Client name} = "DEF",
SWITCH(
{Invoice Currency},
"USD", "”,

“GBP”, “£”

),

{Customer Code}

))

Reply


Terms & Conditions