Need help building a Formula–Text field

Thanks, Mark. Below is a complete sanitized example using sample data.

The Multi-Select Text field contains options grouped into three priority tiers.

Tier 1 — highest priority

Red Option

Orange Option

Yellow Option

Green Option

Tier 2 — medium priority

Blue Option

Purple Option

Silver Option

Gold Option

White Option

Black Option

Gray Option

Tier 3 — lowest priority

North Option

South Option

East Option

West Option

Center Option

The required priority is:

Tier 1

Tier 2

Tier 3

If a record contains selections from multiple tiers, the highest-priority matching tier should be returned.

I created a helper Formula-Text field using:

Lower(ToText([My Multi-Select Field]))

Example helper-field result:

north option ; blue option ; red option

Expected result:

Tier 1

Another helper-field result:

east option ; purple option

Expected result:

Tier 2

A single-selection example:

red option

Expected result:

Tier 1

Here is the full Formula-Text formula I tested:

var text Criteria =

    Lower(ToText([My Multi-Select Field]));

If(

    Contains($Criteria, "red option") or

    Contains($Criteria, "orange option") or

    Contains($Criteria, "yellow option") or

    Contains($Criteria, "green option"),

    "Tier 1",

    Contains($Criteria, "blue option") or

    Contains($Criteria, "purple option") or

    Contains($Criteria, "silver option") or

    Contains($Criteria, "gold option") or

    Contains($Criteria, "white option") or

    Contains($Criteria, "black option") or

    Contains($Criteria, "gray option"),

    "Tier 2",

    Contains($Criteria, "north option") or

    Contains($Criteria, "south option") or

    Contains($Criteria, "east option") or

    Contains($Criteria, "west option") or

    Contains($Criteria, "center option"),

    "Tier 3",

    "NOT MATCHED"

)

I also tested Contains() directly against the Multi-Select Text field:

If(

    Contains([My Multi-Select Field], "Red Option") or

    Contains([My Multi-Select Field], "Orange Option") or

    Contains([My Multi-Select Field], "Yellow Option") or

    Contains([My Multi-Select Field], "Green Option"),

    "Tier 1",

    Contains([My Multi-Select Field], "Blue Option") or

    Contains([My Multi-Select Field], "Purple Option") or

    Contains([My Multi-Select Field], "Silver Option") or

    Contains([My Multi-Select Field], "Gold Option") or

    Contains([My Multi-Select Field], "White Option") or

    Contains([My Multi-Select Field], "Black Option") or

    Contains([My Multi-Select Field], "Gray Option"),

    "Tier 2",

    Contains([My Multi-Select Field], "North Option") or

    Contains([My Multi-Select Field], "South Option") or

    Contains([My Multi-Select Field], "East Option") or

    Contains([My Multi-Select Field], "West Option") or

    Contains([My Multi-Select Field], "Center Option"),

    "Tier 3",

    "NOT MATCHED"

)

Some choices still return NOT MATCHED, even when selected by themselves, while the helper field visibly shows the expected value.

Could you please test this sample setup and provide the exact formula you recommend for reliable Multi-Select Text membership checks and tier priority?