Skip to content

Stockertim Dictionary · Technology

Lookup value

Also called lookup values, lookup_value, search key

In short

The value a lookup formula searches for, such as a product code, which must match an entry in the searched list exactly when exact matching is used.

  • Beginner
  • Concept
  • Checked

In detail

Every lookup function starts from one value: the thing to find. In =XLOOKUP(A2,Products[Code],Products[Price]) the lookup value is whatever sits in A2, perhaps the code P-1042. The function searches the lookup array for it and returns the item in the same position of the return array. VLOOKUP, MATCH and XMATCH take the lookup value as their first argument in the same way.

A good lookup value is a key: it identifies one record and only one. Product codes, staff numbers, invoice numbers and account codes make reliable keys. Names make poor ones, because two customers can share a name and the same person can be spelled in several ways. When no single column is unique, a helper column can join two fields, such as branch and date, into one key.

Most failed lookups come from values that look identical but are not. A trailing space, a number stored as text in one list and as a number in the other, a leading zero dropped from one copy of a code, or a hyphen typed differently are invisible on screen yet stop an exact match. Text matching ignores case, so capital letters are rarely the cause.

When several rows share the lookup value, the function returns the first match it meets and ignores the rest, which hides duplicates. Counting how often each key appears before relying on a lookup reveals that problem.

Worked example

A pharmacy in Port Harcourt looks up the price of code 1042 in its price list, where codes were imported as text.

  1. The order sheet holds 1042 as a number; the price list holds "1042" as text, so the exact match fails with #N/A
  2. Converting the price list codes to numbers lets the lookup find the row and return the price of 350

The lookup works once both copies of the code have the same data type.

In practice

Lookups by code join price lists to orders, staff lists to payroll, and account maps to ledgers. Cleaning the key columns first, with TRIM and consistent types, prevents most #N/A results.

Watch out

  • Numbers stored as text, stray spaces and dropped leading zeros all stop an exact match.
  • Duplicate keys return only the first match, so check the key column is unique.

Learn it properly

Official sources

Facts last checked on 9 November 2026.

Education, not advice: the Stockertim Dictionary explains what terms mean. It never recommends buying or selling anything.

English