Create a Lookup Column in SharePoint List

A SharePoint Lookup column is a column type that allows us to establish a relationship between two lists within a SharePoint site. In this SharePoint tutorial, I will explain everything about the SharePoint lookup column. I will show you how to create a lookup column in a SharePoint Online list.

SharePoint Online Lookup Column

A Lookup column in SharePoint Online List is used to retrieve data from one list to another list.

Let me explain with an example.

We have created a list as ‘MB Trade Program’ with columns such as Program Title, Program Benefits, Program Start Date, etc., shown below.

On the above SharePoint list, we have created a Lookup Column named ‘Program Guider’ and retrieved the data such as Employee Name, Employee Contact Number, and Employee DOB, as shown below.

Create SharePoint Online List Lookup Column

Now, we will see how to create a SharePoint Online List Lookup Column. Here, I have created two lists:

Employee Directory List: This list stores a few basic information like:

• ID
• Name
• DOB
• Email
• Contact Number, etc.

MB Trade Program List: This SharePoint list contains information such as

• Program Name
• Program Benefits
• Program Start Date, etc.

Here, we will create a SharePoint Online Lookup column and take a list of sources from the Employee Directory List’s column, such as Employee Name, Employee Contact Number, and Employee DOB.

Follow the below steps to create a SharePoint list lookup column.

1. Open the SharePoint list where you want to add the lookup column and then Click on + Add column, then select the ‘Lookup’ data type from the ‘Create a column‘ window. Click on the Next button as shown below.

2. The ‘Create a column‘ dialog box opens up. Here, provide a Name and Description of the lookup column. Click on the ‘Select a list as a source’ and select the source list from where we want to retrieve the data. For Example, we have taken the ‘Employee Directory List’

3. click ‘Select a column from the list above’. For Example, we have selected Employee Name.

4. Go to the ‘More option’ and expand it. Now, ‘Add additional columns from source list’. For example, we have selected Employee Contact Number and Employee DOB. Click on the Save button as shown below.

• Click on ‘More options‘ and expand it to show various options.
• Here, when we want to delete the lookup column > select ‘Deletions from source list affect this list’ and move the toggle to Yes we get two options as listed below.
  • Source item cannot be deleted: When we select this option, we can delete any item from the ‘MB Official Email‘ column it will not affect the Employee Directory’s Office Email column items.
  • Deleting source items delete values in the list: When we select this option, we can delete any item from the Employee Directory’s Office Email column. It will also affect the ‘MB Official Email column.

• Allow multiple selections: When we move the toggle toward Yes then we can select multiple options, and Deletions from source list affect this list, and Enforce unique values get disabled.
• Require that this column contains information: Require that this column contains information to ensure that certain information and the data entered into the list are consistent and complete. In this option, the toggle is, by default, placed towards No.
• Enforce unique value: This will only limit the values on one list. Nothing happens if multiple lists use the same site column and fill in the same value. So, there is no way to enforce unique values in all lists.

• Now, when you try to add an item to a SharePoint list, you can see the screenshot like below:

• We can see our lookup column is created, as shown below.

This is how to create a SharePoint Online List Lookup Column.

SharePoint list lookup column supported data types

SharePoint list lookup column does not support all the data types. Here is a list of column data type that is supported and not supported in a lookup column in SharePoint.

Supported Data Type Column	Unsupported Data Type Column
Single line of text	Multiple lines of text
Number	Choice
Date and Time	Calculated
Lookup(single value)	Hyperlink or Picture
	Custom Column
	Lookup(multi-valued)
	Person or Group
	Yes/No
	Currency

SharePoint Online List Lookup Column Limitations

Let’s see what are the limits of the SharePoint Online Lookup Column:

• SharePoint Online Lookup Column can contain a maximum of 750 columns, and the size per column is 10 bytes.
• In the SharePoint Online list, a view can contain 12 lookup columns maximum, and by default value used in SharePoint can increase from 8 to 12. We cannot use more than 12 columns in the SharePoint Online list.
• The SharePoint Online list’s threshold limit will not exceed 5000 items.

Conclusion

In this SharePoint tutorial, I have explained to you what is a lookup column in SharePoint. And how to create a lookup list column in SharePoint Online.

You may also like:

• Create a Folder in SharePoint Document Library
• Add a Lookup Column to Power Apps Collection
• Format a SharePoint Online List Column using JSON