Skip to main content

Lookup Column

A Lookup column connects items in one SharePoint list to items in another list on the same site, creating a real relationship between the two. Instead of retyping project names, client details, or vendor information, users pick from the source list, and SharePoint can pull additional fields across automatically. Lookup columns also control what happens when source items are deleted, with restrict delete and cascade delete options that protect data integrity.
Related Features
Choice Column, Metadata, Person or Group Column
See It In Action

Common Use Cases

  • Project-task relationships: link tasks in a Task list to their parent project in a Projects list, enabling task views filtered by project without duplicating project metadata
  • Client-matter relationships: connect legal matters to clients in a Clients list so matter records display client name, contact, and billing code pulled from the client record
  • Employee-department linking: build an employee roster that looks up department details (manager, location, cost center) from a Departments list for consistent metadata
  • Vendor-contract associations: link contract records to a vendor registry so contract views display vendor contact, payment terms, and rating from a single authoritative source
  • Asset-location tracking: link equipment records to a Locations list so every asset displays its building, address, and facility contact from one maintained source
  • Order-product relationships: connect order line items to a Products list so each line pulls the product name and category without retyping

Benefits

  • Relational metadata without a database: Lookup columns create one-to-many relationships between lists, enabling normalized data structures without a custom database
  • Single source of truth: updating a value in the target list, such as a department name, automatically reflects in all items that look up to it
  • Additional columns from the target: a Lookup can surface multiple fields from the target item, such as Department Name and Department Manager, as read-only columns
  • Filterable and sortable: Lookup values can be used in view filters and sort orders, enabling views scoped to specific related records
  • Supports cascading filtering: combined with connected web parts, a Lookup can drive filtered views where selecting a parent record filters related child records

How It Works

  • Source and target lists: every Lookup column has a source list where the data lives and a target list where it is displayed; editing a target item means picking a value from the source instead of typing it
  • Source and display column: pick a source list, then which column to display; supported source columns are Single line of text, Number, Date and Time, and single-value Lookup
  • Additional fields: the same Lookup can pull extra columns from the source item, each appearing as its own read-only column in the target list
  • Multiple values: one item can reference several source items when the column allows multiple values
  • Edits flow one way: change a value in the source list and every item that looks it up shows the new value automatically
  • Delete behavior is configurable: Restrict delete blocks deleting a referenced source item, while Cascade delete removes the related target items along with it
  • Lookup values stay linked: selecting one opens the source item, so users can jump from a contract straight to the vendor record

Limits and Nuances

  • 12-lookup view limit: views that use more than 12 lookup-type columns are blocked; the List View Lookup Threshold counts Lookup, Person or Group, and Managed Metadata columns, including built-in ones like Created By and Modified By
  • Same-site only: the source list must be on the same site, and Lookup site columns created at the root cannot be used on subsites, so create and use them within one site
  • Additional fields are read-only: edit the values in the source list, not the target list
  • Supported source columns: only Single line of text, Number, Date and Time, and single-value Lookup columns can serve as the lookup value; Choice, Person, Currency, Yes/No, Calculated, and multi-line columns cannot
  • Enforcement prerequisites: Restrict and Cascade delete need Manage Lists permission and an indexed column (SharePoint prompts to create it), and cannot be enabled on a Lookup that allows multiple values
  • Unenforced deletions break silently: deleting a source item with no relationship behavior set leaves broken lookup values behind; turn on Restrict or Cascade delete where data integrity matters
  • List-size limits: relationship behavior cannot be enabled on lists already over the 5,000-item List View Threshold, and cascade delete is capped at 1,000 items per operation
  • Calculated columns cannot reference Lookups: formulas cannot reference Lookup columns

Common Questions About Lookup Columns

What is a Lookup column in SharePoint?

A Lookup column connects items in one list to items in another list on the same site. Instead of typing a value, users pick an item from the source list, and SharePoint displays the chosen value – plus any additional fields you configured – automatically. It is the out-of-the-box way to build relationships between lists, like tasks linked to projects or contracts linked to vendors.

Can a Lookup column reference a list on another site?

No. Lookup columns only work within a single SharePoint site – the source list and the target list must live on the same site. If you need the same reference data on several sites, the usual approaches are keeping related lists together on one site or using Managed Metadata, which is designed to share values across the whole tenant.

What is the 12 lookup column limit in SharePoint?

SharePoint blocks any list view that includes more than 12 lookup-type columns. The count covers Lookup, Person or Group, and Managed Metadata columns, and it includes built-in system columns like Created By and Modified By. The limit exists to protect performance, since each lookup requires an extra database join. When designing views, keep the heaviest ones under the threshold and spread relational columns across multiple views.

What is the difference between Restrict delete and Cascade delete?

Both control what happens when someone deletes an item from the source list. Restrict delete blocks the deletion entirely if any target list item still references it – useful for protecting master data like clients or vendors. Cascade delete removes the related target items along with the source item, keeping both lists consistent. Either option requires enforcing relationship behavior on the Lookup column and Manage Lists permission to configure.

Can a Lookup column show more than one field from the source list?

Yes. When you create or edit a Lookup column, the More options panel lets you pull additional fields from the source item – for example, showing both the vendor name and the vendor’s payment terms. Each extra field appears as its own column in the target list. Those projected columns are read-only, though: to change the values, you edit the item in the source list.

When should I use a Lookup column instead of a Choice column?

Use a Choice column for short, stable value lists like Status or Priority. Use a Lookup column when the values are real records that carry their own data – clients, projects, vendors – and change over time. Greg Zelfond, the SharePoint consultant behind LookBook 365, typically reaches for Lookup columns when building relational trackers, because updating the source list once keeps every related item accurate.