Need Help Creating IF Or And Switch Statement

[Juliet_S]

Hello, I have a table and need to assign a price depending on the status AND the Language:

    {Status} = Confirmed & {Language} = Spanish Then $95
	{Status} = Zoom & {Language} = Spanish then $85
	{Status} = cxl on time then $0
	{Status} = Late cxl & {Language} = Spanish then $95
	{Status} = Zoom & {Language} = X-Exotic then $115
	{Status} = Confirmed & {Language} = X-Exotic then $150
	{Status} = No Show & Zoom & {Language} = Spanish Then $95
	{Status} = No Show & Zoom & {Language} = Exotic Then $115

Thanks in advance!

[Jeremy_Oglesby · Accepted solution]

Hi @Juliet_S,

Since each one of those seems to have mutually exclusive conditions to the others, I think you could make each one its own conditional statement and concatenate them within the same formula field.

IF(
  AND(
    Status = 'Confirmed',
    Language = 'Spanish'
  ),
  95
) &
IF(
  AND(
    Status = 'Zoom',
    Language = 'Spanish'
  ),
  85
) &
IF(
  Status = 'cxl on time',
  0
) &
IF(
  AND(
    Status = 'Late cxl',
    Language = 'Spanish'
  ),
  95
) &
IF(
  AND(
    Status = 'Zoom',
    Language = 'X-Exotic'
  ),
  115
) &
IF(
  AND(
    Status = 'Confirmed',
    Language = 'X-Exotic'
  ),
  150
) &
IF(
  AND(
    Status = 'No Show & Zoom',
    Language = 'Spanish'
  ),
  95
) &
IF(
  AND(
    Status = 'No Show & Zoom',
    Language = 'Exotic'
  ),
  115
)

There may be clever ways to condense that by using SWITCH() functions and nested IF() functions, but I think this should work, and I think it’s probably the most straight-forward, and easily digestible presentation of your conditions.

[Juliet_S]

Thank you! I have tested and it works well.