How to Create and Use Lookup Column in SharePoint Online List (Step by Step)

When you build business solutions with SharePoint Online lists, you often need to connect data stored in different lists instead of duplicating the same information everywhere. A lookup column in SharePoint helps you reuse data like customers, products, or projects by pulling it from a source list and showing it as a selectable value in another list.

In this step-by-step tutorial, you will learn how to create and use a lookup column in a SharePoint Online list using a practical Customer and Sales Order scenario. You will also see which column types are supported, how to filter and allow multiple values in a lookup column, and how to safely delete a lookup column when it is no longer needed.

What is a Lookup Column in SharePoint Online?

A SharePoint lookup column pulls values from a column in another list (source list) and shows them as a dropdown in the current list (target list). This creates a simple relationship between lists so you can reuse master data like Customers, Products, or Projects.

Here are some examples where you can use the lookup column.

• Customer lookup in a Sales Orders list (Customer master list as source).
• Product lookup in an Orders or Inventory list.
• Project lookup in a Tasks or Issues list.

Check out SharePoint Online Site Date Format

Supported and Unsupported Column Types for Lookup

When you create a lookup column in SharePoint, only certain column types in the source list can be used as lookup targets.

Supported column types for lookup:

• Single line of text
• Number
• Date and Time

Common column types that cannot be used as the main lookup column or as additional fields:

• Multiple lines of text
• Choice
• Person or Group
• Yes/No
• Hyperlink
• Lookup (nested lookups)
• Calculated

Planning your list schema with these limitations in mind avoids surprises later when you try to add lookups or additional columns.

Check out How to Use Content Types in SharePoint Online Lists

SharePoint Lookup Column Example: Customer and Sales Order lists

In this tutorial, there are two SharePoint lists: Customer List and Sales Order List. The Customer information comes from the Customer List and is reused in the Sales Order List using a lookup column.

• Source List: Customer List
  • Customer ID
  • Name
  • Region
  • Phone Number
  • Email
• Target List: Sales Order List
  • Order ID
  • Order Date
  • Product Name
  • Quantity

In this case, we want the Customer ID to look up in the Sales Order List, so we have created a Lookup column in the Sales Order List.

Read Display Weather in SharePoint using Weather Web Part

Create a Lookup Column in SharePoint Online List

Follow these steps to create the lookup column in SharePoint in the Sales Order List.

1. Open the target list
   • Open the Sales Order List in your SharePoint Online site.
   • From the command bar, select + Add column and then choose Lookup > Next.

2. Configure basic settings
   • Enter a column name, for example, Customer.
   • The Type is automatically set to Lookup because you chose Lookup while creating the column.
   • Optionally enter a description to explain what the column is used for (helps users and accessibility).

3. Select the source list and main lookup field
   • Under Select a list as a source, choose Customer List.
   • Under Select a column from the list above, choose Customer ID so the dropdown will show Customer IDs in the Sales Order List.

4. Add additional fields from the source list
   • Select More options.
   • Under additional columns, select Customer Name and Email so they are automatically pulled from the Customer List.
   • Select Save to create the lookup and its extra columns.

After saving, the Customer lookup column plus the additional Customer Name and Email columns will appear in the Sales Order List view. Here is a screenshot for your reference.

How to Use Lookup Column in SharePoint List Forms

Once the lookup column is created, you can start using it in the SharePoint list items.

• Select an existing item and choose Edit to open the form.
• In the Customer lookup field, open the dropdown and select a Customer ID from the Customer List.
• Select Save; the related Customer Name and Email values are fetched from the Customer List and displayed automatically.
• Repeat for the remaining orders so each order is linked to the correct customer.

Here is a screenshot for your reference.

Now you can see the Customer Name and Email are fetched and displayed from the SharePoint source List.

In the same way, we have done for the remaining orders, and based on the customer, it fetches the Customer Name and Email and displays them in the SharePoint List.

This reduces data entry errors and ensures customer details stay consistent across lists.

Check out Pin to Quick Access in a SharePoint Document Library

Filter a Lookup Column in SharePoint Online

Modern SharePoint list views make it easy to filter items using a lookup column.

1. Open the list and select the lookup column
   • Open the Sales Order List and locate the Customer lookup column.
   • Select the column header and choose Filter by from the menu.

2. Choose lookup values to filter
   • In the filter pane, select the values to include, for example, CUST003 and CUST005.
   • The pane shows how many values are selected; choose Save or close the pane to apply the filter. Below is a screenshot for your reference.

3. View filtered results
   • The SharePoint list now shows only the orders where the Customer lookup is CUST003 or CUST005.

This is very useful for quickly viewing orders by customer, region, or any other lookup-based classification.

Check out Add Comments to SharePoint List Item

Allows Multiple Values in SharePoint List Lookup Column

Lookup columns can be configured to allow multiple selections when an item can relate to more than one record in the source list.

1. Edit the lookup column
   • Open the Sales Order List and select the Customer column.
   • Choose Column settings > Edit. See the screenshot below:

2. Enable multiple selections
   • In the Edit column pane, expand More options.
   • Turn on Allow multiple selections.
   • Select Save.

3. Add multiple values per item
   • Edit a list item and in the Customer lookup field, select more than one customer, for example, CUST001 and CUST002.
   • Select Save; the related additional fields, such as Customer Name and Email, will show multiple values as well.

4. Here is a screenshot for your reference.

Use multi-value lookups when a single order or record legitimately belongs to several parent records, but be aware that multi-value lookups count against lookup limits and can impact performance in very large lists.

Check out Give Unique Permissions For a SharePoint Library

Delete Lookup Column in SharePoint Online List

You can delete a lookup column from the list when it is no longer needed, but this removes existing values from that column in a SharePoint list.

There are two common ways to delete the lookup column: from the Edit column pane and from List settings.

Delete from the Edit column pane

Now, I will show you how to delete the lookup column in the SharePoint Online List from the Edit Column Properties. Here are the steps below.

1. Open the list and edit the column
   • Open the list that contains the lookup column.
   • Select the lookup column header, choose Column settings, and then Edit.

2. Delete the column
   • In the Edit column pane, select Delete.
   • Confirm the Delete? dialog by selecting Delete again.

Delete from List settings

Here, you can see how to delete a SharePoint list lookup column from list settings. For that, follow the steps below.

1. Go to List settings
   • Open the list and select the Settings (gear) icon.
   • Choose List settings to open the settings page.

2. Delete the column from the Columns section
   • Under the Columns section, select the lookup column (for example, Customer).
   • On the Edit column page, select Delete and then confirm by selecting OK.

3. A dialog box will then appear, as shown in the screenshot below. Click on OK.

4. After deletion, the lookup column and its values are removed from the list, and the view no longer shows that column.

In this way, we can delete the SharePoint Online lookup column from the edit column property pane and list settings.

Check out Customise SharePoint List Toolbar Buttons With List Formatting

Limitations and Best Practices for SharePoint Lookup Columns

SharePoint lookup columns are powerful but have several important limits that matter for large or complex lists.

Key things to consider:

1. Lookup threshold in views:
   • SharePoint list Views are effectively limited to 12 lookup-type columns (including Lookup, Person/Group, and Managed Metadata) before you may see errors like “the number of lookup columns exceeds the lookup column threshold”.
   • Design list views to use fewer lookup-type columns or split information across multiple views to avoid this error.
2. Large lookup lists:
   • Standard lookup dropdowns work best when the source list is relatively small; large lists (over a few thousand items) can cause slow dropdowns and poor user experience.
   • For very large source lists, consider filtered views, auto-complete style controls, or custom solutions with Power Apps.
3. Cross-site lookups:
   • Out-of-the-box lookup columns are designed to work within the same site; cross-site scenarios typically require site columns or custom solutions.
   • Planning for these constraints up front avoids performance problems as the solution grows.

Best Practices for Lookup Columns in SharePoint

Applying a few best practices will help your lookup-based lists remain scalable and easier to maintain.

Recommended practices:

• Normalize master data: Store shared entities like Customers, Products, Projects, and Departments in dedicated lists and reference them via lookups instead of duplicating text values.
• Keep lookup lists clean and indexed: Use views and indexed columns on the source list to keep lookups responsive, especially when the list grows large.
• Limit the number of lookup columns per view: Stay safely below the 12 lookup threshold and avoid unnecessary additional columns from the source list in the same view.
• Use meaningful display columns: Use user-friendly values (such as Customer Name) as the main lookup display where possible, and store the ID separately if needed for automation.
• Combine with Power Apps and Power Automate: For complex forms, dependent dropdowns, or multi-step business logic, use lookup columns together with Power Apps forms and Power Automate flows.

Conclusion

Lookup columns in SharePoint Online are a straightforward way to connect related data across multiple lists while keeping information consistent and easy to maintain. By using them correctly—defining clear source and target lists, configuring lookup settings, applying filters, and following the recommended limitations and best practices—you can build list solutions that are both user-friendly and scalable for real-world business scenarios.

You may also like the following tutorials:

• “Sharing is Limited on This Item” Error in SharePoint and OneDrive
• SharePoint List View Customization Using JSON Formatting
• Customize Modern SharePoint List Form Using JSON Formatting

Bijay Kumar

After working for more than 18 years in Microsoft technologies like SharePoint, Microsoft 365, and Power Platform (Power Apps, Power Automate, and Power BI), I thought will share my SharePoint expertise knowledge with the world. Our audiences are from the United States, Canada, the United Kingdom, Australia, New Zealand, etc. For my expertise knowledge and SharePoint tutorials, Microsoft has been awarded a Microsoft SharePoint MVP (12 times). I have also worked in companies like HP, TCS, KPIT, etc.