Excel can look up the city, state or county for a column of ZIP codes in seconds, provided the codes are stored as text and matched against a reference table. This guide covers the formulas, the leading-zero problem and the edge cases that break lookups.
Step 1: keep ZIP codes as text
Excel converts 02108 to 2108 the moment it sees a number. Before pasting or importing, select the ZIP column and set its format to Text, or import through Data › Get Data and set the column type to Text. If the zeros are already gone, restore them in a helper column with =TEXT(A2,"00000").
Step 2: build a reference table
Create a sheet with columns ZIP, City, State and County, and convert it to a table named Postal (Insert › Table). The ZIP column must also be text. You can build it from a licensed dataset or by exporting results from the bulk ZIP code lookup.
Step 3: match with XLOOKUP or VLOOKUP
=XLOOKUP(TEXT(A2,"00000"), Postal[ZIP], Postal[City], "Not found", 0)
=VLOOKUP(TEXT(A2,"00000"), Postal, 2, FALSE)The 0 in XLOOKUP and FALSE in VLOOKUP force an exact match. Wrap VLOOKUP in IFERROR(...,"Not found") to avoid #N/A.
Handling ZIP+4 and messy input
If some cells contain nine-digit values such as 02108-1234, match on the first five characters: =XLOOKUP(LEFT(TRIM(A2),5), Postal[ZIP], Postal[City], "Not found", 0). Use TRIM to remove stray spaces and SUBSTITUTE to strip unexpected characters.
Counties need care
A ZIP code can intersect several counties, and a one-row-per-ZIP table can hold only one. If county accuracy matters, use a relationship table with one row per ZIP-county pair from the HUD-USPS crosswalk files and keep the allocation ratios. See County by ZIP code.