Skip to content

Stockertim Dictionary · Artificial intelligence · Technology

Lookup function

Also called lookup functions, lookup formula, spreadsheet lookup

In short

A spreadsheet function that finds a value in a table, such as a product code, and returns related information from the same row, such as its price.

  • Beginner
  • Technology
  • Checked

In detail

A lookup function connects two tables. Given a key, such as a product code or customer number, it searches a reference table for that key and returns a value from the matching row, such as the product's price or the customer's region. It saves copying information by hand and keeps one reference table as the single source.

Lookups behave differently depending on how they are set up. An exact match returns a result only when the key is found precisely; an approximate match returns the nearest value, which suits bands such as tax brackets but gives wrong answers for codes. When a key appears twice in the reference table, most lookups quietly return the first match, and when a key is missing they return an error unless told what to show instead.

AI assistants are good at writing lookup formulas when the columns are described precisely: which column holds the key, which holds the answer and whether matches must be exact. They can also explain an inherited formula in plain words. The result still needs testing on rows whose answer is already known.

Typical checks are counting how many lookups return errors, confirming the reference table has no repeated keys and spot-checking a handful of results by hand.

Worked example

Northwind Bikes adds prices to 1,500 order lines with an exact-match lookup into its price list.

  1. Lines returning not found: 12, so 12 / 1,500 = 0.8%
  2. The 12 lines use old product codes that were renamed last year.
  3. The price list holds one repeated code, so that product's lines had picked up the first of two prices.
  4. After fixing both problems the team tests five known orders against their invoices.

Every line now finds the right price, and the error count is zero before totals are produced.

In practice

Lookups join price lists to orders, customer regions to sales, staff codes to departments and exchange rates to transactions. Analysts ask assistants to draft them, then test on known rows and count errors before relying on the output.

Watch out

  • An approximate match on codes returns a wrong value without any error message.
  • A repeated key in the reference table makes the lookup return only the first match.

Learn it properly

Official sources

Facts last checked on 3 November 2026.

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

English