Help

IF statement to simplify multiple regions/ countries

Topic Labels: Formulas
196 1
cancel
Showing results for 
Search instead for 
Did you mean: 
hannahRFP
6 - Interface Innovator
6 - Interface Innovator

Hi,

I am trying to write a formula to write the wider region that the project is related to, e.g. if it's in Germany, it's in EMEA. 

This formula works; however, I now want it to be able to list multiple regions for projects that are taking place in two different countries that aren't in the same region.
e.g. a project that is in Germany and in Canada should show 'EMEA, CAN'. Currently it just shows 'EMEA', not both. 

Please could someone suggest how to change this formula to print all that apply? 

(Tagging @Databaser who helped with my previous question in case they can help, Complex Nested IF AND statement (dates and regions))

Thanks,

 

 

IF(
REGEX_MATCH({Division}, "UK|ROI|France|Spain|Italy|Greece|Poland|Germany|Portugal|Belgium|Netherlands|Denmark"),
"EMEA",

IF(
REGEX_MATCH({Division}, "Thailand|Indonesia|Malaysia"),
"SEA",

IF(
REGEX_MATCH({Division}, "New Zealand|Australia"),
"AUS/NZ",

IF(
REGEX_MATCH({Division}, "USA"),
"NAM",

IF(
REGEX_MATCH({Division}, "Egypt|Bahrain|Saudi Arabia"),
"MENA",

IF(
REGEX_MATCH({Division}, "Canada"),
"CAN"
)
)
)
)
)
)

 

 

1 Reply 1
jwag
6 - Interface Innovator
6 - Interface Innovator

Hi Hannah,

Not the answer to your specific question, but can you instead create a Country table where you track countries and their respective regions? You can then specify the country as a linked record and add the region as a lookup. This will display all regions where multiple countries are selected.