Abbreviate state or country names to 2-letter postal codes (mixed-input safe)

Handle columns where some rows are already abbreviated

CRM lead forms frequently mix full state names ("California", "new york") with 2-letter codes ("tx", "FL"). Running plain XLOOKUP(State, FullNames, Codes) fails with #N/A on rows that already contain "TX"!

Test LEN(TRIM(A2)) = 2 first (and verify it exists in the valid code column with COUNTIF) or look up full names via XLOOKUP(TRIM(A2), FullNames, Codes, "Unknown") so both formats resolve cleanly.

Mixed-input state standardization formula

Validates existing 2-letter inputs against $D$2:$D$5 before accepting them so typos like "ZZ" are flagged instead of passed through.

=LET(s, TRIM(A2), IF(AND(LEN(s)=2, XLOOKUP(s, $D$2:$D$5, $D$2:$D$5, "")<>""), UPPER(s), XLOOKUP(s, $C$2:$C$5, $D$2:$D$5, "Unknown")))

Reverse lookup: expand 2-letter code to full state name

Swap lookup_array and return_array in XLOOKUP.

=XLOOKUP("NY", $D$2:$D$5, $C$2:$C$5, "Unknown")

Inputs and interpretation

The lookup table includes four example states, not the complete postal list. Extend it with an authoritative, unique mapping before using it operationally. Country codes require a separate mapping and standard. Exact XLOOKUP validation rejects wildcard-looking input instead of accepting it as a COUNTIF pattern.

Further reading

• Microsoft Support — XLOOKUP function