Convert 'United Kingdom' to 'GB' in One VLOOKUP

Your avails file has United Kingdom, Germany, Japan in the Territory column. The platform spec wants ISO α-2 codes: GB, DE, JP. You could find-and-replace 30 times, but you’d miss the one-off case where someone typed U.K. with periods or Deutschland instead of Germany. A one-VLOOKUP solution converts all of them and surfaces the unknowns.

Step 1: Build the Lookup Sheet

On a sheet called ISO Lookup, make two columns:

A: Country Name      | B: ISO α-2
United Kingdom       | GB
United States        | US
Germany              | DE
France               | FR
Japan                | JP
Republic of Korea    | KR
... (top 50 territories)

Seed with the top 30-50 territories you handle. It’s fine if this isn’t exhaustive — the IFERROR wrapper catches misses.

Step 2: The Lookup Formula

In your avails sheet, next to the Territory column, add:

=IFERROR(VLOOKUP(TRIM(B2), 'ISO Lookup'!A:B, 2, FALSE), "UNKNOWN: "&B2)

Breakdown:

TRIM(B2) — whitespace-proof the input.
VLOOKUP(..., 'ISO Lookup'!A:B, 2, FALSE) — exact-match lookup of name → ISO.
IFERROR(..., "UNKNOWN: "&B2) — if the name isn’t in the lookup, output UNKNOWN: Deutschland so you can see what needs adding to the lookup.

Conditional-format the output column: amber for any value starting with UNKNOWN. You’ll see every territory you need to teach the lookup.

Step 3: Two-Way Lookup (Optional)

If some rows already have ISO codes and others have names, handle both directions:

=IFERROR(VLOOKUP(TRIM(B2), 'ISO Lookup'!A:B, 2, FALSE),
  IFERROR(VLOOKUP(TRIM(B2), 'ISO Lookup'!B:B, 1, FALSE),
    "UNKNOWN: "&B2))

If the value matches a name in column A → return the ISO. If not, check if it’s already an ISO code in column B → return itself. If neither → flag UNKNOWN.

Gotchas

Common aliases. UK vs U.K. vs United Kingdom vs Great Britain. Seed the lookup with all the common variants pointing to the same ISO. Don’t rely on one canonical name.
Case sensitivity. VLOOKUP is case-insensitive by default in Sheets, but Excel’s is too for most text operations. Still — wrap in UPPER() if you need to be extra safe: UPPER(TRIM(B2)) and matching on UPPER(A:A).
Non-ASCII country names. Côte d'Ivoire, São Tomé. If the avails source has the accented version and the lookup has the plain version (or vice versa), VLOOKUP misses. Either seed both, or pre-normalize with an ASCII-only transformation.
Sub-national territories. Hong Kong (HK) vs China (CN), Puerto Rico (PR) vs United States (US). Some platforms treat these as separate ISO-equivalent; others roll up. Encode the platform’s preference in your lookup — possibly as a third column pinning the exceptions.

If You Need the Full Workflow

Normalize Territory Codes Across ISO, IFTA, and Platform-Specific Formats — covers the full three-way mapping (ISO ↔ IFTA ↔ platform-specific) with a richer lookup sheet.

Download

The Avails Data Quality Scorecard ships with an ISO Lookup tab seeded with 50 territories plus common aliases. Add to it as you encounter new names.