SharePoint Online List Lookup Column

This SharePoint tutorial explains the SharePoint Online list lookup column. We will see how to create a lookup column in the SharePoint Online list.

And we will learn how to filter the list items in the SharePoint Online list Lookup column and also we will discuss SharePoint Online list lookup multiple columns.

We will see, how to create and use the lookup column in the SharePoint Online list.

What is a Lookup Column in SharePoint Online List

• SharePoint Online List lookup column allows us to get data from another SharePoint Online list.
• By creating a lookup column, we can display data of the list items in one SharePoint list from another SharePoint list.
• We can fetch and display data from the lists only on the same SharePoint Online sites and not from the other modern SharePoint site.
• If we update anything on the Source list, changes will appear immediately on the destination list.
• For example, I have two SharePoint Online lists, one list for HRTeam with employees’ names, and another list called EmployeeListDetails with employee joining date, employee location, etc., By creating a lookup column in the HRTeam list we can see the Employees joining date and employees location list items fetch data and display it in the HRTeam list. It will not affect the EmployeeListDetails list, the data in the SharePoint Online list remains the same.

Read How to use content types in SharePoint online

How to create a lookup column in SharePoint Online List

Let us see how to create a lookup column and display the list items in the SharePoint Online list,

• Open the SharePoint list, for which you want to create a lookup column.
• Select the +Add column, and click on the Lookup option.

In the following, I have explained how to create a lookup column along with a simple scenario as an example.

Example:

In this example, I have selected EmployeeListDetails as my source list as mentioned below:

• Name: In the Name, field enter the Name of the Lookup column.
• Description: Description is optional.
• Type: While adding a new column when you select the Lookup option, the selected lookup column type appears by default.
• Select a list as a source: Choose a SharePoint list that you want to add as a source for the lookup column

• Select, a column from the list above: Choose the column from the Source SharePoint list to fetch and display it.
• Expand more options, to add additional columns from the source list, here I have selected the Employee Location State and Employee Location city columns additionally.
• Click on the Save button to save your changes.

• In the below screenshot you can that the JoiningDate lookup column was created in the HRTeam list, and the remaining columns are also added from the source list called EmployeeListDetails

• Now, if we select and edit a list item, you can see that we can able to choose the date in the Joining date column or field.
• Then, click on the Save button, to save your changes.

• In the below screenshot, you can see that the Employee Location state and city are fetched data automatically and displayed from the Source list (EmployeeListDetails).

• In the same manner, we can do for the remaining employees, based on the date selection it will fetch the employee’s location state and employees location city-data automatically and display them in the SharePoint Online list.

This is how to create a lookup column and display the list items in the SharePoint Online list.

Read SharePoint Online Code snippet web part

Supported column types for SharePoint list lookup column

Below listed column types are supported for the SharePoint list lookup column,

1. A single line of text
2. Number
3. Date and Time

Unsupported column types for SharePoint list lookup column

Below listed column types are Unsupported for the SharePoint list lookup column,

1. Multiple lines of text
2. Lookup
3. Choice
4. Currency
5. Yes/No
6. Calculated
7. Hyperlink or Picture
8. Person or Group

SharePoint Online list lookup column filter

Let us see how to filter the lookup column in the SharePoint Online list,

yes it is possible to apply a filter on the lookup column to filter the SharePoint list items,

• Go to the SharePoint Online list and select the lookup column for which you want to apply the filter.
• Expand the Lookup column and Select the Filter by option.

In this example, I have selected the Joining Date column which is of type lookup.

• Now select the date, that you want to filter and click on the Apply button. We can also see the selected items count.

• In the below screenshot, you can see that filtered list items based on the selected dates.

This is how to filter the lookup column in the SharePoint Online list.

Read SharePoint Online List Permissions

SharePoint Online List Lookup Column allows multiple values

Let us see how to display multiple values in the SharePoint online list lookup column,

Yes, it is possible to add the multiple values in the SharePoint online list lookup columns,

• Open the SharePoint Online list and select the lookup column for which you want to add multiple values.
• Expand the Lookup column, Select the Column settings option and then click edit.

In this example, I have selected the Reports to the column which is of type lookup.

• In the Edit column properties, scroll down and Enable the allow multiple selections to Yes.
• Click on the Save button to commit your changes.

• Now if we select and edit a list item, you can see that we can able to add multiple values in the Reports to column or field.
• Click on the Save button, to save your changes.

• In the below screenshot, you can see that the Reports to lookup column has multiple values for the single list item.

• In the same manner, we can do it for the remaining employees, to whom they are reporting.

This is how to display multiple values in the SharePoint online list lookup column.

Read SharePoint Online list view

SharePoint Online list lookup column limit

• In the Modern SharePoint online list, a view can contain the maximum number of 12 lookup columns.
• Yes, the default value is also used for SharePoint Online, where the default value is increased from 8 to 12.
• We cannot use more than 12 lookup columns in a SharePoint online list view.
• Even if you try to create, you will get an error message like query cannot exist achieved, because the maximum digit of lookup columns it contains surpasses the lookup column threshold limitation.

How to increase lookup column limit in SharePoint online list

• No, it is not possible to change or increase the lookup column threshold limit in the modern SharePoint Online list.
• The maximum lookup columns that we can create in the SharePoint online list is 12.
• For example, if we try to create the 13th lookup column an error occurs like the maximum number of lookup columns it contains exceeds the lookup column threshold limit.

Read SharePoint Online List Web Part

SharePoint lookup column does not show all fields

Let us discuss why the lookup column is not showing all the columns or fields from the Source list.

• It populates only the supported column types from the source list to the lookup column and it will not display the unsupported column types fields in the SharePoint list.
• If the column types are choice, person/group, calculated, Yes/No, or multiple lines of text then they cannot be used in the lookup column. Because they are unsupported column types for the SharePoint list lookup column.
•  If the column types are date/time, a single line of text, and number then they can be used in the lookup column. Because they are supported column types for the SharePoint list lookup column.

This is why the lookup column is not showing all the columns or fields from the Source list.

SharePoint online lookup column list view threshold

• The SharePoint online lookup column fetches data from the source list and displays it.
• SharePoint’s online lookup column list view threshold is limited by 5000 items. If the list item occurs more than 5000, then the list view threshold error occurs.
• For example, if the source list has 8000 list items but it displays only 5000 items. If the SharePoint online lookup column list view item count exceeds then a list view threshold error occurs.

Read How to Customize SharePoint Modern list form using JSON

How to delete a lookup column in a SharePoint list

Let us how to delete a lookup column in a SharePoint list.

There are two ways that we can delete a lookup column in a SharePoint list, they are,

1. List settings page.
2. Edit column properties pane.

List settings page

• Open the SharePoint list, for which you want to delete a lookup column.
• Select the settings gear icon and click on the list settings.

• In the list settings page, under columns select the lookup column that you want to delete from the SharePoint list. Scroll to the bottom of the page and click on the Delete option.

• In the confirmation box, select the ok option to delete the column permanently. And the lookup column will be deleted from the SharePoint list.

Edit column properties pane

• Open the SharePoint list, for which you want to delete a lookup column.
• Expand the lookup column and select column settings -> Edit.

• In the Edit column properties pane, select the option Delete.

• In the confirmation box, select the delete option. And the lookup column will be deleted from the SharePoint list.

In the below screenshot, you can see that the lookup column was deleted.

These are two ways that we can delete a lookup column in a SharePoint online list.

Read How to customize a SharePoint List form

SharePoint online change lookup column source

Let us see how we can change the lookup column source in the SharePoint online list,

yes, it is possible to change the lookup column source in the SharePoint online list.

• Open the SharePoint list, and select the lookup column for which you want to change the source list.
• Expand the lookup column and select column settings and click on the Edit.

• In the Edit column properties pane, Under Select, the list as source option, choose the list that you want to change or update in the lookup column. And select the column to fetch data from the source list.
• Click on the Save button, to save your changes.

In the below screenshot, you can see that the source list has been changed.

This is how to change the lookup column source in the SharePoint online list

Read How to delete all items from SharePoint list

SharePoint Online lookup column limitations

Let us see what are the limitations that are presented when we use the lookup column in SharePoint Online.

• We can create a lookup column, for specific column types.
• If the list contains many list items entries, and we use that list as a source list for the lookup column then the drop-down gets extended.
• The maximum lookup column that we can create in a list view is 12.
• We cannot create a lookup column across sites, connected lists need to be on the same SharePoint site.

These are the limitations that are presented when we use the lookup column in SharePoint Online.

In this SharePoint modern tutorial, we learned what is lookup column in SharePoint online list is and how to add a lookup column in SharePoint online list. And also covered the below-mentioned topics:

• Supported Column Types for SharePoint list lookup column
• Unsupported Column Types for SharePoint list lookup column
• SharePoint Online List Lookup column Filter
• SharePoint Online List Lookup Column allows multiple values
• SharePoint online list lookup column limit
• How to increase lookup column limit in SharePoint online list
• SharePoint lookup column does not show all fields
• SharePoint online lookup column list view threshold
• SharePoint lookup column limitations
• How to delete a lookup column in a SharePoint list
• SharePoint online change lookup column source

You may like the following SharePoint list tutorials:

• SharePoint Online image column
• PowerShell SharePoint list operations
• SharePoint list operations using rest api
• SharePoint list title column remove
• SharePoint modern list view customization example
• How to create a calculated column in SharePoint List or Library