Google Sheets handles ZIP code lookups much like Excel, with two differences worth knowing: plain-text formatting is set under Format › Number, and reference data is often pulled in from another spreadsheet with IMPORTRANGE.
Keep codes as plain text
Select the ZIP column, choose Format › Number › Plain text, then paste. To repair codes that have already lost their zero, use =TEXT(A2,"00000") in a helper column and paste the results as values.
Reference sheet
Put ZIP, City, State and County in a sheet named Postal, with the ZIP column in plain text. To reuse a reference sheet across files, pull it in with =IMPORTRANGE("spreadsheet-url", "Postal!A:D").
Lookup formulas
=IFNA(VLOOKUP(TEXT(A2,"00000"), Postal!A:D, 2, FALSE), "Not found")
=XLOOKUP(TEXT(A2,"00000"), Postal!A:A, Postal!B:B, "Not found")Fill an entire column at once with =ARRAYFORMULA(IFNA(VLOOKUP(TEXT(A2:A,"00000"), Postal!A:D, 2, FALSE), "")).
Validate the format
Flag bad values with =REGEXMATCH(TO_TEXT(A2), "^\d{5}(-\d{4})?$"), or add a Data validation rule with a custom formula so invalid entries are highlighted as people type. The ZIP code validator explains what counts as valid.
Do not infer missing records
“Not found” means the code is not in your reference sheet, not that the ZIP is invalid. Verify the address with USPS before deleting or changing it.