Excel & Google Sheets users

Give your spreadsheet a lookup table

Whoever reads your report should not have to know that 44 means retail. Turn your vendor codes into readable industries with one formula and a free reference list.

  1. 1Your vendor list has NAICS codes.
  2. 2One formula looks up each code’s first two digits in the free sector list.
  3. 3Every vendor gets its industry, and bad codes say “Check code”.

Select a cell to see how it works. Try it: type 722511 in B13 and press Enter.

VVendor directory12 practice vendors
C2 =IFERROR(XLOOKUP(--LEFT(TRIM(B2),2), Keys!A:A, Keys!C:C), "Check code")

C2 · One formula returns Construction from prefix 23 in Keys. Select a marked code to see how the formula handles it.

Practice vendors with calculated sectors
ABC
1VendorAs receivedNAICS codeAs receivedSectorThe lookup
2
3
4
5
6
7
8
9
10
11
12
13

Keys / every two-digit prefix

Keys lookup reference
ABC
1PrefixNumberSector codeSector name
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

Sectors / the complete free dataset

Complete NAICS sector reference, in downloaded column order
ABC
1IDsectorCodesectorName
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
12 vendors · 24 lookup keys · 20 sectors

XLOOKUP works in Excel 2021/365 and Google Sheets. Corner marks identify the four practice problems; amber cells need a code to check.

Your turn / one formula

Build your own version.

  1. Import the reference sheets.

    Save the free sector CSV as Sectors and the expanded lookup CSV as Keys. Keys has numeric prefixes in A, sector codes in B and sector names in C. Import the practice file as Vendors.

  2. Paste in C2, then fill down.

    TRIM cleans surrounding spaces, LEFT reads two digits, and -- makes them a number. XLOOKUP returns the industry name from Keys column C; IFERROR keeps bad or missing codes visible.

    =IFERROR(XLOOKUP(--LEFT(TRIM(B2),2), Keys!A:A, Keys!C:C), "Check code")
  3. Highlight anything that needs checking.

    Apply a light amber fill to C2:C13. In Excel choose Conditional Formatting → New Rule → Use a formula. In Google Sheets choose Format → Conditional formatting → Custom formula is. Both use:

    =$C2="Check code"
  4. Older Excel? Use INDEX/MATCH.

    The same prefix and exact match, without XLOOKUP:

    =IFERROR(INDEX(Keys!C:C, MATCH(--LEFT(TRIM(B2),2), Keys!A:A, 0)), "Check code")
Google Sheets: import the reference directly

Downloading once is the dependable option. IMPORTDATA may refresh automatically and uses the anonymous API allowance shared by people on the same internet connection. A quota or network error can interrupt the lookup. Import this into Sectors; download Keys separately.

=IMPORTDATA("https://mysafeinfo.com/api/data/usnaics2022sectors?format=csv")

Free in full. No Data Pass or API key needed. Opening this guide does not use your API allowance.

Questions worth asking.

Why does Keys have 24 rows, but Sectors has 20?

The combined sectors 31–33, 44–45 and 48–49 are expanded into one numeric key per prefix. Each prefix still returns its original sector name.

Does this validate a full industry code?

No. The formula checks the first two digits only. A longer value with a valid sector prefix can return an industry even if the complete code is wrong; full industry validation needs the detailed classification.

Are these real vendors?

No. The names are fictional. Their valid practice codes come from the BLS NAICS hierarchy (opens in a new tab); two rows intentionally have an invalid or missing code.

What each record contains

Explore the dataset and its sources. These fields are in the complete download.

FieldHow to read it
ID Record identifier.
sectorCode Two-digit NAICS 2022 sector or combined range such as 31-33. Keep as text.
sectorName Name of the broad sector; not a six-digit industry’s description.
firstCode A field retained from the source dataset; see its source notes for scope.
lastCode A field retained from the source dataset; see its source notes for scope.
subsectors A field retained from the source dataset; see its source notes for scope.
industryGroups A field retained from the source dataset; see its source notes for scope.
naicsIndustries A field retained from the source dataset; see its source notes for scope.
nationalIndustries A field retained from the source dataset; see its source notes for scope.

Working with the download? Convert CSV to JSON, or turn a column into a list with the comma separator.

For your next project

More subjects to build with.

NAICS sectors are free in full. The Data Pass covers the rest of the catalog.

The Data Pass includes complete downloads and API access across the catalog.

Data Pass

$69one payment / 365 days

See what’s included

No account required. No automatic renewal.