Use LOOKUP to Add Checked Labels to a Dataset

Suppose you have a table of library visits and want to add a visit-band label before sharing it. A lookup formula can read each count, find the applicable threshold in a reference table, and return its label.

Use LOOKUP for sorted thresholds. Use an exact-match formula for IDs or codes. Ordinary LOOKUP can return a result even when the value you searched for is absent—a useful behavior for bands, but a dangerous one for identifiers.

1. Set up the input and reference tables

This fictional dataset uses whole-number visit counts. Enter it in A1:C5, leaving column C empty for now:

Place	Visits	Visit band
North Library	75
Central Library	240
East Library	810
West Library	1400

Enter these illustrative thresholds in E1:F5:

Minimum visits	Visit band
0	Low
100	Moderate
500	High
1000	Very high

Each threshold is the inclusive lower boundary of a band. For example, Moderate means 100–499 visits; Very high means 1,000 or more. These are sample labels, not an official classification.

Keep the threshold column in ascending order. If you sort it, sort the entire reference table together so each label stays attached to its threshold. Both Microsoft’s LOOKUP documentation and Google’s LOOKUP documentation require sorted search data for reliable results.

2. Enter the LOOKUP formula

In C2, enter:

=LOOKUP(B2,$E$2:$E$5,$F$2:$F$5)

Then copy or fill C2 down through C5.

The three arguments are:

• B2: the value to find—the visit count for this row.
• $E$2:$E$5: the search range containing the thresholds.
• $F$2:$F$5: the result range containing the labels.

The dollar signs keep the reference ranges fixed when you copy the formula down. B2 changes to B3, B4, and B5, so each row uses its own count. Keep the search and result ranges the same size, with each label in the corresponding position; here, both contain four rows.

The expected results are:

Place	Visits	Visit band
North Library	75	Low
Central Library	240	Moderate
East Library	810	High
West Library	1400	Very high

For 240, LOOKUP selects 100—the largest threshold less than or equal to 240—and returns Moderate. It does not choose the numerically nearest threshold. A value below the smallest threshold returns #N/A in Excel. See Microsoft’s matching rules.

3. Use exact matching for IDs instead

If your task is to attach a place name to a place ID, a nearby ID is not an acceptable substitute.

For a separate example, create a sheet named Places with this table in A1:B4:

Place ID	Place name
P-01	North Library
P-02	Central Library
P-03	East Library

On another sheet, enter P-02 in A2 and enter this formula in B2:

=XLOOKUP(A2,Places!$A$2:$A$4,Places!$B$2:$B$4,"Unmatched",0)

This returns Central Library. Searching for P-99 returns Unmatched rather than another place’s name. The final 0 explicitly requests an exact match, which is also the default in Excel and Google Sheets.

XLOOKUP is unavailable in Excel 2016 and 2019. An alternative is:

=VLOOKUP(A2,Places!$A$2:$B$4,2,FALSE)

Here, 2 selects the second column and FALSE requests exact matching. A missing ID returns #N/A. Don’t omit FALSE: VLOOKUP defaults to approximate matching in Excel and Google Sheets.

4. Check the results before publishing

Before turning the enriched table into a public dataset:

• Test boundaries: 99 should return Low; 100 should return Moderate; 500 should return High; 1000 should return Very high.
• Check inputs: resolve blank, negative, fractional, or nonnumeric visit counts rather than treating them as valid whole-number counts.
• Check reference coverage: extend ranges when you add reference rows. For ID lookups, investigate duplicate IDs rather than accepting the first match silently.
• Review unmatched IDs: check spaces and consistent data types. Preserve meaningful leading zeros in codes.
• Document the rule: include the threshold definitions and the reference source or version alongside the published data.

Keep the formula workbook as your working file. Prepare a separate copy containing only reviewed public columns, with one header row; copy the results and paste them as values in that copy. For export considerations, see reasons to convert data to CSV before sharing.

TablePage accepts CSV, TSV, XLSX, and XLS files and generates public dataset pages. Upload only data intended for public access—not confidential records or sensitive identifiers.