Turn Excel Codes Into Labels You Can Safely Share

To build a lookup table in Excel, put codes and their labels in a reference range, then use XLOOKUP with exact matching to return each label. Check the number of matching reference entries with COUNTIF before publishing: a returned label does not prove that its key is unique. In Excel 2016 or 2019, use VLOOKUP with FALSE instead.

The worked example uses fictional region data. It keeps the original codes beside the readable labels and marks an unknown code as UNMAPPED rather than guessing a region.

Choose a sample region code to check its expected label and match count.

Set Up Unique Codes And Their Labels

Create a worksheet named Lookup. Enter this reference table in A1:B4, including the headers:

region_code	region_name
R01	North
R02	Central
R03	South

The region_code column is the key: the value used to connect the two datasets. For this mapping, each key should appear once in the reference table.

On a second worksheet named Data, enter the following in A1:B5:

region_code	observations
R01	12
R03	9
R02	18
R99	4

Add region_name in C1 and match_count in D1. The expected labels, in row order, are North, South, Central and an explicit unmatched marker for R99.

Neither range needs to be formatted as an Excel Table for these formulas. Here, “lookup table” means the reference data, not a formatting feature. It is also different from an Excel what-if Data Table, which calculates results for changing inputs.

Return Exact Matches With XLOOKUP

In Data!C2, enter =XLOOKUP(A2,Lookup!$A$2:$A$4,Lookup!$B$2:$B$4,"UNMAPPED",0) and fill down to C5.

Argument	Purpose
A2	Code in the current data row
Lookup!$A$2:$A$4	Codes to search
Lookup!$B$2:$B$4	Labels to return
"UNMAPPED"	Result when no match exists
0	Exact matching

Exact matching is XLOOKUP’s default; the formula writes 0 explicitly so the intended behavior is visible.

XLOOKUP can return data from either side of the search column. Its default search starts at the first item and returns the first matching result. That means a successful lookup can conceal duplicate reference keys. These behaviors are documented in Microsoft’s XLOOKUP reference.

The dollar signs keep the reference ranges fixed as you fill down. The row reference A2 changes to A3, A4 and A5. If you add reference rows later, extend both XLOOKUP ranges and the COUNTIF range below together.

Use VLOOKUP In Excel 2016 Or 2019

XLOOKUP is not available in Excel 2016 or 2019, according to Microsoft’s XLOOKUP reference. In those versions, enter =VLOOKUP(A2,Lookup!$A$2:$B$4,2,FALSE) in C2 and fill down to C5.

The 2 returns the second column of the selected range. FALSE requests an exact match, so R99 remains an error to investigate.

VLOOKUP requires the key to be in the first column of its lookup range. Do not omit FALSE for code-to-label mapping: omission defaults to approximate matching, which can return an unintended result. See Microsoft’s VLOOKUP documentation.

Count Matches Before Trusting The Label

In Data!D2, enter =COUNTIF(Lookup!$A$2:$A$4,A2) and fill down to D5. COUNTIF counts cells meeting the supplied criterion, as described in Microsoft’s COUNTIF guide.

match_count	Meaning And Action
0	Missing reference entry or mistyped code; investigate.
1	One entry exists; check that its label is correct and nonblank.
Greater than 1	Duplicate keys; resolve them before trusting the label.

For the sample, the counts should be 1, 1, 1, 0. This validates keys used by the dataset. Separately review unused reference rows if you intend to publish the lookup table itself.

COUNTIF ignores case and treats * and ? as wildcards. If your identifiers are distinguished by case or contain literal wildcard characters, this count is not a sufficient validation check. Microsoft’s COUNTIF guide documents those behaviors.

Investigate Missing Matches Without Hiding Them

When an expected match fails, compare the actual cell contents. Check for leading or trailing spaces and for a numeric key stored as text on one side but as a number on the other. Keep identifiers with meaningful leading zeros consistently as text. Microsoft’s VLOOKUP guidance documents these character and data-type pitfalls.

Do not replace unmatched results with zero or an empty string just to make the table look finished. Correct the mapping, or retain a clearly documented missing-label status.

For R99, the sample reference table supplies no region name. UNMAPPED is therefore the expected XLOOKUP result, not evidence that R99 belongs to any particular region.

Publish Reviewed Values, Not The Working Workbook

Create a separate release workbook containing only approved rows and columns. Retain the region code alongside its readable label. Remove temporary checks such as match_count after review.

Copy the output and use Home → Paste → Values in the release workbook. This pastes formula results rather than formulas, without source formatting or comments, according to Microsoft’s paste-options documentation. Reapply any necessary number or date formats.

Resolve VLOOKUP errors before export. If a missing mapping is intentional, replace its error with the documented missing-label status in the release copy.

Document the reference source, its release date and the meaning of any unmatched marker in a dataset reference sheet. Then save the release file as XLSX or export its output sheet as CSV. Excel’s text-file export saves only the current worksheet; check the exported headers, row count and identifiers before sharing it. See Microsoft’s CSV export guidance.

TablePage accepts CSV, TSV, XLSX and XLS files and turns uploads into public dataset pages with a shareable link and filterable table. Upload only the sanitized release file—never sensitive records or the full working workbook.

On the published page, verify a known matched row and any intentionally retained unmatched row. The code, label and missing-label status should retain the same meaning you checked in Excel.