Build a Lookup Table Readers Can Understand and Reuse

If your dataset contains codes such as O, C and P, readers need their meanings—not a spreadsheet full of unexplained abbreviations. Build a lookup table that maps each code to a label, check the matches, then publish the codes and labels together.

The result should be a table readers can interpret directly, plus a reusable reference for anyone working with the original coded data.

What is a lookup table?

A lookup table stores reference values associated with a key: a code, identifier or combination of fields used to find the corresponding information. For public datasets, it often connects a short code to a readable label, definition or category.

This is different from a lookup formula. The table holds the mapping; a formula or join retrieves information from it. W3C’s CSV on the Web primer describes using a separate CSV to list valid values and provide additional information about them.

Here is a fictional service-request lookup table, not an official classification:

status_code	status_label	status_definition
O	Open	Request received; work not yet started
P	In progress	Work has started
C	Closed	Request marked complete

For this mapping, one row represents one status code. The label helps readers scan the data; the definition explains what the category actually means.

1. Choose a key that identifies one mapping

Use the identifier already present in your source data. Do not replace it with the label: keeping the original code makes the published result easier to compare with the source.

For a simple exact-match lookup, require:

• A nonblank key on every lookup row.
• One mapping per key, with a nonblank label.
• The same key representation in the source and lookup files.

Check spaces, capitalization and leading zeros before matching. Preserve meaningful differences rather than automatically removing spaces or changing case. If 001 is an identifier, treat it as text throughout preparation rather than assuming it means the number 1. Inspect the exported file and the published display to confirm it remains intact.

Sometimes one field is not enough. If a code’s meaning changes between releases, the key may need to be code plus release version, with both fields available in the source records. W3C’s primary-key guidance supports keys made from multiple columns that are unique in combination.

Do not combine different classification versions and then arbitrarily keep the first duplicate.

2. Match the codes and inspect exceptions

Suppose the working dataset contains these fictional records:

request_id	status_code
R101	O
R102	C
R103	X

Match status_code against the lookup table and add the label. Keep every source record and a separate field for the match result:

request_id	status_code	status_label	lookup_match
R101	O	Open	Matched
R102	C	Closed	Matched
R103	X	Unmapped code	Unmapped

Unmapped code is an exception marker, not a new status category. Investigate X before publication: it might indicate a typo, a newer classification or a code omitted from the lookup.

Keep missing source codes separate from unmatched codes. In this example, a blank means no status was supplied; X is a supplied value that the mapping did not resolve. For a real dataset, follow its documented missing-value rules—some codes explicitly mean unknown, withheld or not applicable.

If using SQL, a left join keeps unmatched source rows, whereas an inner join drops them. Check lookup-key uniqueness first: multiple matches can produce multiple output rows for one source record. These behaviors are described in PostgreSQL’s join documentation.

For spreadsheet formulas and key checks, use the companion Excel lookup-table workflow.

3. Validate the enriched table

Before exporting, check four things:

1. Duplicate lookup keys: each source record should resolve to no more than one lookup row under your chosen key. Check unused lookup rows too if you will publish the reference separately.
2. Unmatched codes: list every distinct unmatched value and how many source rows use it. Check missing codes separately.
3. Record preservation: adding labels should not change the source records in this workflow. Compare row counts and record identifiers; equal counts alone do not prove that every record survived. An increase can reveal multiple matches; a decrease can reveal dropped records.
4. Spot checks: compare several labels with the reference, including an uncommon code and any special missing-value code. A matched key with an empty label is still an exception to resolve.

Export reviewed values rather than relying on the publication tool to run your working formulas or join separate files.

4. Publish the result and document the mapping

For readers exploring the records, publish a flat table containing both codes and labels. If other analysts need to reuse the classification, publish the lookup table separately as well and link it from the accompanying documentation.

Record the lookup’s source, release version, retrieval date, key fields and missing-value rules. Say whether labels reproduce the source wording or are your own shortened display labels. A dataset reference sheet can hold these details without cluttering every row. The W3C tabular-metadata specification likewise describes column definitions, data types, modification dates and licences as useful metadata.

As of October 8, 2026, TablePage accepts CSV, TSV, XLSX and XLS files and publishes public dataset pages with filterable tables. Upload only a reviewed, publication-safe file—not a working workbook containing sensitive records or private reference mappings.

After publication, check a known code-label pair, any identifiers with leading zeros, and the explanation of unresolved codes. Share the page only once its labels and documentation agree with the lookup version you used.