Help

Mark "Yes" IF cell contains "STRING 1" or "STRING 2"

Topic Labels: Formulas
Solved
Jump to Solution
9311 2
cancel
Showing results for 
Search instead for 
Did you mean: 
NYCEDU_Admin
5 - Automation Enthusiast
5 - Automation Enthusiast

I have a table “Ethnicity”. For the purpose of this post, I’ll use Asian ethnicities as an example, but my Ethnicity table will also include ethnicities of other racial groups.

On this table are entries like “Asian Indian”, “Bangladeshi”, “Bhutanese”, “Taiwanese”, “Chinese”, etc.

I have another table “Contacts” which contains a column that links to Ethnicity. Each record in Contacts can be linked to multiple ethnicities.

I now want a column that marks whether a record’s ethnicities fall under larger racial groups.

For example, if the record is linked to “Taiwanese” or “Chinese” or both, I want the record to be marked “Yes” under the column, “Asian”

Screenshot%20of%20Google%20Chrome%20(9-6-19%2C%209-39-37%20PM)

What’s the best way to accomplish this?

1 Solution

Accepted Solutions
JonathanBowen
13 - Mars
13 - Mars

Hi @NYCEDU_Admin - you could do this with an IF() statement or a SWITCH() statement, e.g.:

IF(
  {Ethnicity} = 'Taiwanese', 
  'Yes', 
  IF(
    {Ethnicity} = 'Chinese',
    'Yes',
    IF(...)
  )
)

but I think this is going to get pretty complicated pretty quickly.

As Ethnicity is a linked field, a better way is to add the racial grouping to the Ethnicity table and then lookup this value back into the contacts table:

Screenshot 2019-09-07 at 10.21.56.png

Screenshot 2019-09-07 at 10.22.03.png

As you can see on Person 4, if you select two ethnicities, you will get two occurrences of the ethnic grouping.

If you then want a column that has a Y/N on, for example, “Asian” you could then add a formula field which works this out:

Screenshot 2019-09-07 at 10.24.46.png

using:

IF(FIND('Asian', {Ethnic Grouping}), 'Y', 'N')

JB

See Solution in Thread

2 Replies 2
JonathanBowen
13 - Mars
13 - Mars

Hi @NYCEDU_Admin - you could do this with an IF() statement or a SWITCH() statement, e.g.:

IF(
  {Ethnicity} = 'Taiwanese', 
  'Yes', 
  IF(
    {Ethnicity} = 'Chinese',
    'Yes',
    IF(...)
  )
)

but I think this is going to get pretty complicated pretty quickly.

As Ethnicity is a linked field, a better way is to add the racial grouping to the Ethnicity table and then lookup this value back into the contacts table:

Screenshot 2019-09-07 at 10.21.56.png

Screenshot 2019-09-07 at 10.22.03.png

As you can see on Person 4, if you select two ethnicities, you will get two occurrences of the ethnic grouping.

If you then want a column that has a Y/N on, for example, “Asian” you could then add a formula field which works this out:

Screenshot 2019-09-07 at 10.24.46.png

using:

IF(FIND('Asian', {Ethnic Grouping}), 'Y', 'N')

JB

This was exactly what I needed, thank you!!!