When you create a lookup column in SharePoint, you pull data from one list into another list. This lets you connect related information without copying it. But many users set up the lookup column incorrectly and end up with broken references or missing data. This article explains the most common mistakes when creating a lookup column between two lists and how to avoid them.
Key Takeaways: Creating a Lookup Column Between Two Lists
- List settings > Column > Lookup: Choose the source list and field you want to reference.
- Source list must exist in the same site: Lookup columns cannot reference lists in other sites.
- Use a single line of text for the lookup field: Avoid using lookup columns that pull from another lookup column.
What Is a Lookup Column in SharePoint?
A lookup column in SharePoint lets you display data from another list on the same site. For example, you have a Projects list and a Tasks list. You can add a lookup column to the Tasks list that shows the Project Name from the Projects list. This creates a relationship between the two lists.
To create a lookup column, go to the target list, add a new column, and choose the type Lookup. You then select the source list and the field you want to display. The lookup column stores the ID of the source item and shows the selected field value.
Lookup columns are powerful, but they have strict rules. The source list must be in the same SharePoint site. You cannot create a lookup column that references a list in a different site or subsite. Also, you can only pull data from one field per lookup column, though you can add additional fields from the same source list.
Common Mistakes When Creating a Lookup Column
Mistake 1: Using a Lookup Column as the Source Field
SharePoint does not allow a lookup column to reference another lookup column. If your source list has a column that is itself a lookup, you cannot use it as the source field for a new lookup column. Instead, create a separate column in the source list that stores the actual text value you need.
For example, if you want to show the Project Manager name in the Tasks list, and the Project Manager is a lookup from a Users list, create a text column in the Projects list that copies the Manager name. Then use that text column as the lookup source.
Mistake 2: Changing the Source List After Creating the Lookup
If you delete or rename the source list, the lookup column breaks. The column will show an error or no values. To avoid this, never delete the source list while the lookup column exists. If you must rename the list, update the lookup column settings to point to the new list name.
Also, if you delete a column that is used as the lookup source field, the lookup column stops working. Always check which columns are referenced before deleting any column in the source list.
Mistake 3: Not Setting Up Indexing for Performance
When a source list has thousands of items, a lookup column can slow down the target list. SharePoint creates an index on the lookup column, but only if the source list has an index on the referenced field. If the source field is not indexed, the lookup column will perform poorly.
Go to the source list settings, select the column used in the lookup, and enable indexing. This improves performance when the lookup column loads data.
Mistake 4: Using a Lookup Column in a Calculated Column
Calculated columns cannot reference lookup columns directly. If you try to write a formula that uses a lookup column value, SharePoint returns an error. To work around this, create a text column in the source list that contains the value you need, then use that text column in the calculated column.
Mistake 5: Not Allowing Multiple Values When Needed
By default, a lookup column allows only one value. If you need to select multiple items from the source list, you must enable the Allow multiple values option when creating the column. This changes the lookup column to a multi-select list of checkboxes.
Be aware that multi-value lookup columns cannot be used in views, filters, or calculated columns. They also cannot be exported to Excel easily.
Steps to Create a Lookup Column Correctly
Follow these steps to set up a lookup column without errors.
- Open the target list settings
Go to the list where you want the lookup column. Click the gear icon and select List settings. If you are in a modern list, click the settings gear and choose List settings from the menu. - Add a new column
Scroll down to the Columns section and click Create column. Enter a name for the column, such as Project Name. - Select the Lookup column type
Under The type of information in this column is, choose Lookup. This reveals the source list and field options. - Choose the source list
In the Get information from dropdown, select the list that contains the data you want to reference. The list must be in the same site. - Select the source field
In the In this column dropdown, choose the field that holds the value you want to display. Avoid selecting a lookup column or a calculated column. - Add additional fields if needed
Check the box Add a column to show each of these additional fields to bring in extra columns from the source list. Each additional field becomes a separate column in the target list. - Set column options
Choose whether the column requires a value, allows multiple values, and whether to enforce relationship behavior. For most cases, leave the default settings. - Save the column
Click OK at the bottom of the page. The lookup column appears in the target list.
If the Lookup Column Still Has Issues
Lookup Column Shows No Data
If the lookup column displays empty values, the source list may be empty or the source field may have been deleted. Check that the source list contains items and that the referenced column still exists. Also verify that the lookup column is not pointing to a deleted list.
Lookup Column Shows an Error Message
A common error is “The lookup column cannot be created because the source list does not exist.” This happens when the source list has been moved or deleted. Restore the source list from the recycle bin or recreate the lookup column with the correct source.
Lookup Column Is Slow
If the lookup column takes a long time to load, check that the source field is indexed. Also reduce the number of items in the source list by archiving old data or creating views that filter the source list.
Lookup Column vs Choice Column: Key Differences
| Item | Lookup Column | Choice Column |
|---|---|---|
| Data source | Items from another list in the same site | Fixed list of values you type manually |
| Updates | Values update automatically when source list changes | Values stay static unless you edit the column |
| Multi-select | Supported with performance tradeoffs | Supported with checkboxes |
| Dependency | Requires source list to exist and be accessible | No external dependency |
Now you can create a lookup column between two lists without the common pitfalls. Always verify that the source list is in the same site and that the referenced field is not a lookup column. For advanced scenarios, consider using Power Apps to build more flexible relationships between lists across sites.