FIPS Codes in Excel: VLOOKUP and Power Query Tips

Despite the rise of Python and R, Microsoft Excel remains widely used for working with federal geographic data — especially in government agencies, nonprofits, and policy organizations. FIPS codes in Excel have some well-known pitfalls, but they're easily avoided with the right setup. The most important: always format FIPS code columns as Text before entering data, to prevent Excel from stripping leading zeros.

When importing a CSV containing FIPS codes via the standard open dialog, Excel will convert numeric-looking strings to numbers — turning "06037" into 6037 and destroying the leading zero. The fix: use the Data → From Text/CSV import wizard and explicitly set the FIPS column to "Text" format during import. In Power Query (Get & Transform Data), set the column type to "Text" in the Transform step before loading to the sheet. Once the leading zero is gone, it's painful to restore — 5-digit codes need =TEXT(A1,"00000"), 2-digit state codes need =TEXT(A1,"00").

For VLOOKUP-based FIPS lookups, build a reference table with FIPS codes as the left column and use =VLOOKUP(A2, FIPSTable, 2, FALSE). Ensure both the lookup value and the table's first column are formatted as Text and have matching zero-padding. If your lookup is returning #N/A errors even when the codes appear visually identical, the issue is almost always a type mismatch (text vs. number) or a trailing space. =TRIM(TEXT(A2,"00000")) cleans both problems.

Power Query is the better tool for large FIPS-based datasets in Excel. It handles type conversion explicitly, allows merging (joining) two tables on a shared FIPS key, and reloads automatically when source data changes. A Power Query merge on county FIPS between a Census ACS download and a BLS QCEW download is the Excel equivalent of a SQL join. Reference the county FIPS guide to build your lookup table, and use the search tool to verify any codes that don't match during development.