Expand 'EMEA excluding Turkey' Into a Flat ISO Territory List

Your contract says “EMEA excluding Turkey and Russia.” The platform you’re delivering to wants a flat list of ISO α-2 country codes. Manually typing out all 45+ EMEA members minus two exceptions is tedious and error-prone. You need to expand the region, remove the exclusions, and output a comma-separated list — ideally in one formula that re-runs when you edit the exclusions.

What This Isn’t

The Territory Group Exceptions for Windowing tutorial covers the windowing-rule version of this problem (what if your SVOD window in EMEA holds back Germany?). This tutorial is narrower: you have a region name and a list of excluded territories. Output a flat list. No window logic, no date arithmetic.

Step 1: Build the Region Lookup

On a sheet called Regions, make three columns:

A: Region  | B: ISO  | C: Name
EMEA       | GB      | United Kingdom
EMEA       | FR      | France
EMEA       | DE      | Germany
... (one row per member)
EMEA       | TR      | Turkey
EMEA       | RU      | Russia
APAC       | JP      | Japan
APAC       | KR      | Korea
... etc

One row per (region, member). This is the source of truth. Seed it once with the 45 EMEA members, 20 APAC members, and your LATAM list; reuse forever.

Step 2: The Expansion Formula

Given a cell with the region name (say A2 = "EMEA") and a cell with exclusions as a comma-separated list (say B2 = "TR, RU"), produce the flat list in column C:

=TEXTJOIN(", ", TRUE,
  FILTER(Regions!B:B,
    Regions!A:A = A2,
    ISERROR(SEARCH(Regions!B:B, B2))))

Breakdown:

• FILTER(Regions!B:B, Regions!A:A = A2, ...) — pull all ISO codes where Region matches A2.
• ISERROR(SEARCH(Regions!B:B, B2)) — keep only codes that do not appear in the exclusion string.
• TEXTJOIN(", ", TRUE, ...) — collapse the array to a comma-separated list.

Result: GB, FR, DE, IT, ES, NL, BE, CH, AT, ... — every EMEA member except TR and RU.

Step 3: Validate Your Exclusions

An exclusion that isn’t a member of the region is a typo, not an exclusion. Add a check column:

=IF(COUNTIF(Regions!B:B, TRIM(MID(B2, FIND(",", B2&","), 3))) = 0, "Unknown exclusion", "OK")

If someone wrote “EMEA excluding TK” (Turkey’s ISO is TR, not TK), this surfaces the mismatch.

Step 4: Output to Your Avails

Once you have the flat list in column C, either expand into one row per territory (=SPLIT(C2, ", ")) or keep it as a comma-separated string, depending on what the platform’s avails spec expects.

Most platforms want one row per (title × territory), so split and duplicate:

Title | Territory | Right | Start | End | Exclusive
The Ember Lanes | GB | SVOD | 2026-07-01 | 2027-06-30 | Yes
The Ember Lanes | FR | SVOD | 2026-07-01 | 2027-06-30 | Yes
...

A quick way to do the splitting: paste your single row with C2 as territory, then use an ARRAYFORMULA to cross-join title × split territories.

Gotchas

• Region membership changes. When Brexit happened, GB’s region membership was in flux — it’s still EMEA, but some contracts treat it separately. Add a “valid from” column to your Regions sheet if you deal with legacy contracts.
• Regions overlap. Cyprus is sometimes listed in EMEA, sometimes in ME. Pick one canonical region per territory and document why; don’t let the same territory appear in multiple regions or the expansion silently double-counts.
• Exclusion list encoding. If the exclusions are ISO codes (TR), the SEARCH approach works. If they’re country names (Turkey), you need a two-step: first resolve names → ISO, then exclude. The Normalize Territory Codes tutorial covers that conversion.
• Exclusions with shared substrings. SEARCH("IS", "ISR, SAU") matches on both IS(Iceland) and the IS in ISR. Safer: wrap exclusions with commas on both sides (", TR, RU, ") and search for ", TR," specifically. It’s uglier but correct.

If You Need the Full Workflow

The broader Normalize Territory Codes tutorial covers ISO ↔ IFTA ↔ platform-specific mappings, which is the prerequisite for consistent region expansion.

Download

The Avails Data Quality Scorecard ships with the Regions lookup tab pre-seeded for EMEA, APAC, LATAM, and NA. Drop in your region + exclusions and the formula populates.