Screen a multiple select field for a certain value to fill another column with data

[Gustavo100]

Dear all,

i am looking for a solution for the following problem:

“Column A” is a multiple select field where I have data on company presence in a variety of countries (e.g. “Poland”, “China”, “Germany”)

“Column B” should have a formula that checks whether “Column A” contains an entry (among others) that belongs to a specific list (e.g. “Poland” or "Germany). In this case the formula should return “Europe”

I am not sure, whether this problem should be solved with IF, nested IF, SWITCH formulas or whether I need a completely different approach

Thanks for any input,
Cheers

[Gustavo100]

In the meantime, I came up with the following solution myself as the formula for “Column B” (and {Production Country} is the “multiple select” Column A. If anybody has a leaner solution, that would be great! Cheers

---

IF(OR(
FIND(“Albania”,{Production Country}),
FIND(“Austria”,{Production Country}),
FIND(“Azerbaijan”,{Production Country}),
FIND(“Belarus”,{Production Country}),
FIND(“Belgium”,{Production Country}),
FIND(“Croatia”,{Production Country}),
FIND(“Czech Republic”,{Production Country}),
FIND(“Denmark”,{Production Country}),
FIND(“Estonia”,{Production Country}),
FIND(“Europe”,{Production Country}),
FIND(“Finland”,{Production Country}),
FIND(“France”,{Production Country}),
FIND(“Georgia”,{Production Country}),
FIND(“Germany (Production)”,{Production Country}),
FIND(“Greece”,{Production Country}),
FIND(“Hungary”,{Production Country}),
FIND(“Iceland”,{Production Country}),
FIND(“Ireland”,{Production Country}),
FIND(“Italy”,{Production Country}),
FIND(“Kazakhstan”,{Production Country}),
FIND(“Kosovo”,{Production Country}),
FIND(“Latvia”,{Production Country}),
FIND(“Lithuania”,{Production Country}),
FIND(“Malta”,{Production Country}),
FIND(“Moldova”,{Production Country}),
FIND(“Montenegro”,{Production Country}),
FIND(“Netherlands”,{Production Country}),
FIND(“North Mazedonia”,{Production Country}),
FIND(“Norway”,{Production Country}),
FIND(“Poland”,{Production Country}),
FIND(“Portugal”,{Production Country}),
FIND(“Romania”,{Production Country}),
FIND(“Russia”,{Production Country}),
FIND(“Serbia”,{Production Country}),
FIND(“Slovakia”,{Production Country}),
FIND(“Slovenia”,{Production Country}),
FIND(“Spain”,{Production Country}),
FIND(“Sweden”,{Production Country}),
FIND(“Switzerland”,{Production Country}),
FIND(“Turkey”,{Production Country}),
FIND("",{Production Country}),
FIND(“Ukraine”,{Production Country})
)
,“Europe”, “nein”)

[ScottWorld · Accepted solution]

Welcome to the community, @Gustavo100 !

That’s probably the best way to do it if you’re going to use a multiple select field.

However, a more straightforward approach might be to use a linked record field instead of a multiple select field.

To do this, you would create another table that lists all of your countries with their associated continent.

Then, back in your first table, you would create a linked record field (instead of your multiple select field) where you would choose all the associated countries.

Then, you would create a rollup field based on that linked record field which shows you all the linked continents. For your rollup field formula, you would want to use ARRAYUNIQUE(values) to only show you the unique continents that you’ve linked to.

If you only need to see Europe, you could setup a condition on that rollup field to only show you values when the continent is Europe. Otherwise, you could create another formula field to display what you want to see. (You could probably even create the formula within the rollup itself, but it’s a little trickier to do it there because it doesn’t guide you through the creation of the formula.)

[Gustavo100]

Thanks @ScottWorld for the detailed and quick answer! Very, very helpful for a newbie like me.

I tried to solve the Problem with linked records, but I did not now how to solve the problem to show only the unique values. Your solution appears to be a much more straightforward approach!

Cheers,
Gustavo