Skip to main content
Solved

If Column A contains "This Text", then return "That Text" in Column B

  • May 9, 2020
  • 2 replies
  • 109 views
H

Hi everyone,

I’ve got a table that I’m having trouble with. In the “Act” column, I have a single-select with Act 1, Act 2, Act 3, and Act 4 as options. When one of those Acts is selected, I want the next column (“Act Dynamics”) to return a string of text. I’ve gone through similar questions from other users and have tried a few different possibilities, but each time, Airtable says my formula is invalid. Can anyone see what the issue is?

Here are the 3 best guesses I have:

Possibility 1:

IF(
FIND(
'Act 1',
{Act}
),

IF(
FIND(
'Act 2',
{Act},
),

IF(
FIND(
'Act 3',
{Act},
),

IF(
FIND(
'Act 4',
{Act},
),

'Denial to anger;Rejecting opportunity;Ordinary world',
)
'Bargaining and losing;Wanderer'
),
'Bargaining and winning;Warrior'
)
'Martyr;'
)

Possibility 2:

IF(MID({Act},12,3)=“Act 1”,“Denial to anger;Rejecting opportunity;Ordinary world”,
IF(MID({Act},12,3)=“Act 2”,“Bargaining and losing;Wanderer”,
IF(MID({Act},12,3)=“Act 3”,“Bargaining and winning;Warrior”,"",
IF(MID({Act},12,3)=“Act 4”,“Martyr”))))

Possibility 3:

SWITCH(
    MID({Act}, 0, Original),
    "Act 1", "Denial to anger;Rejecting opportunity;Ordinary world",
    "Act 2", "Bargaining and losing;Wanderer",
    "Act 3", "Bargaining and winning;Warrior",
    "Act 4", "Martyr"
)

Best answer by Mohamed_Swella1

Hi @Holly_Ostara,

Welcome to Airtable Community! :grinning_face_with_big_eyes:

You do not need the Find or Mid functions unless the Single Select Field contains something other than Act 1, Act 2, etc.

You got it with the last option, the Switch formula should be

SWITCH({Act}, 
 "Act 1", "Denial to anger;Rejecting opportunity;Ordinary world",
    "Act 2", "Bargaining and losing;Wanderer",
    "Act 3", "Bargaining and winning;Warrior",
    "Act 4", "Martyr")

BR,
Mo

2 replies

Mohamed_Swella1
Forum|alt.badge.img+17

Hi @Holly_Ostara,

Welcome to Airtable Community! :grinning_face_with_big_eyes:

You do not need the Find or Mid functions unless the Single Select Field contains something other than Act 1, Act 2, etc.

You got it with the last option, the Switch formula should be

SWITCH({Act}, 
 "Act 1", "Denial to anger;Rejecting opportunity;Ordinary world",
    "Act 2", "Bargaining and losing;Wanderer",
    "Act 3", "Bargaining and winning;Warrior",
    "Act 4", "Martyr")

BR,
Mo

H
H
  • Holly_Ostara
  • Author
  • New Participant
  • May 9, 2020

Hi @Holly_Ostara,

Welcome to Airtable Community! :grinning_face_with_big_eyes:

You do not need the Find or Mid functions unless the Single Select Field contains something other than Act 1, Act 2, etc.

You got it with the last option, the Switch formula should be

SWITCH({Act}, 
 "Act 1", "Denial to anger;Rejecting opportunity;Ordinary world",
    "Act 2", "Bargaining and losing;Wanderer",
    "Act 3", "Bargaining and winning;Warrior",
    "Act 4", "Martyr")

BR,
Mo


It works! Thank you so much, Mo!

Mohamed_Swella1
Terms & ConditionsAccessibility statement