Lookup & Reference
Convert State and Region Names to 2-Letter Postal Codes (Bidirectional XLOOKUP)
Standardize mixed CRM address columns that contain both full state names ("California", "new york ") and existing 2-letter postal codes ("TX") into clean ISO/USPS codes without #N/A errors.
Handling mixed full names and already-abbreviated codes in one column
Imported shipping and tax-nexus rosters rarely arrive cleanly: half the rows spell out "California" or " texas " with trailing spaces, while the other half already store "CA" or "NY". A naive XLOOKUP(A2, FullNames, Codes) fails with #N/A on rows that already hold valid 2-letter codes.
Normalizing input via UPPER(TRIM(A2)) and testing LEN(clean) = 2 lets the formula verify existing 2-letter codes against the code column first, and otherwise translate full names into 2-letter abbreviations—returning "UNMAPPED_REGION" for typos.
| Raw CRM Input | Normalized Token | Resolution Path | Output Code |
|---|---|---|---|
| " california " | "CALIFORNIA" | FullName -> Code XLOOKUP | "CA" |
| "tx" | "TX" | Verified in Code list | "TX" |
| "Ontario" | "ONTARIO" | Not in US reference table | "UNMAPPED_REGION" |
Bidirectional Postal-Code Normalization Formula
Clean the input once inside LET, validate 2-character tokens against $F$2:$F$6, and map longer names from $E$2:$E$6 to $F$2:$F$6.
=LET(key_in, UPPER(TRIM(B2)), IF(LEN(key_in) = 2, XLOOKUP(key_in, $F$2:$F$6, $F$2:$F$6, "UNMAPPED_REGION"), XLOOKUP(key_in, UPPER($E$2:$E$6), $F$2:$F$6, "UNMAPPED_REGION")))Cleaning Imported Vendor CSVs Together in Arte Sheets
When reconciling regional address exports in Arte Sheets, analysts can attach .csv or .xlsx reference tables (up to 2 MB per file as safe context excerpts) in the AI Assistant to scaffold the lookup table, while reviewers in Suggest mode propose spelling aliases and @mention regional ops leads before finalizing shipping labels.
=COUNTIF(C2:C6, "UNMAPPED_REGION")Try the example
Use the original values or change them to test your understanding.