Lookup column in SharePoint Online/2013/2016 (with example on lookup site column)

In this SharePoint online tutorial, we will discuss lookup column in SharePoint online or SharePoint 2016/2013. We will see how we can create a lookup site column and then we can add the lookup site column to a SharePoint online custom list.

[toc]

What is SharePoint Lookup Column?

SharePoint Lookup columns are very much helpful when you want to display data which are presented in a separate list. In the large enterprise, there are list data which may be related to another list in SharePoint Online or SharePoint 2013. In those cases lookup columns are ideal. By using a lookup column, we can display data in one list from another list in SharePoint.

Here I have custom list known as “TrainingEnquiry” which has two columns as:

  • Title
  • TrainingType (Choice column having values Online/Offline/Classroom)

The list looks like below:

lookup column in sharepoint online
lookup column in sharepoint online

Here I wanted to add another lookup column as TrainingCourses, and the data will come from another list known as “TrainingCourses“.

lookup column in sharepoint 2013
lookup column in sharepoint 2013

SharePoint Supported Column Types for SharePoint lookup column

Below are the column types which are supported for SharePoint lookup columns.

  • Single line of text
  • Number
  • Date and Time

SharePoint Unsupported Column Types for SharePoint lookup column

Below are the column types which are not supported for SharePoint lookup columns.

  • Multiple lines of text
  • Currency
  • Person or Group
  • Calculated
  • Hyperlink or Picture
  • Custom Columns
  • Yes/No
  • Choice
  • Lookup

Create Lookup Site Column in SharePoint Online/2013/2016

Now we will create the site column of type lookup column. Open SharePoint Online Site Settings page then click on “Site columns” which is under “Web Designer Galleries“.

If you are new to site columns, read What is site column in SharePoint?

In the Site Columns page, click on Create like below:

sharepoint lookup column
lookup column in SharePoint 2016

Then in the Create Column page, Give a name for the Site columns and choose “Lookup (information already on this site)” radio button from the data type like below:

sharepoint lookup column from another site
sharepoint lookup column from another list

Then in the group, either you can add to any existing site column or you can create a new site column like below:

sharepoint lookup column from another site collection
SharePoint lookup site column

Then in the additional Column Settings, give a description of the column and then in the Get information from: choose the list name from which you want to bring the data. Then “IN this column”, choose the column which you want to show, here I have chosen Title column.

cross site collection lookup column sharepoint 2013

Then click on OK which will create all the lookup site column. And you can verify this from the Site Columns page. Choose show group as “SharePoint Site Columns” and then you can see the column.

sharepoint lookup column
SharePoint Online lookup column example

Add Lookup Site Column to SharePoint Online List

Now we will see how we can add the site column to the SharePoint Online/2016/2013 list. Open the list, click on List Settings from the ribbon which will open the List Settings page. There click on “Add from existing site column” like below:

sharepoint 2013 lookup column from another list
SharePoint 2013 lookup column from another list

Then in the “Add Columns from Site Columns” page, Select the group as our custom group name. And from the available columns choose the column and click on Add>.

sharepoint 2013 lookup column additional fields
sharepoint 2013 lookup column additional fields

Click ok, now the column will get added to the list.

sharepoint 2013 lookup column default value
sharepoint 2013 lookup column default value

Now if you will go to the list, and try to add one item to the list, you can see the lookup column details like below:

sharepoint online lookup column default value
sharepoint online lookup column default value

Select Additional Column from the Lookup column

In the lookup column, we have an additional column as “Duration”. If you want to show the additional column in the list, you can follow below steps.

Go to the list settings page. Click on the Courses lookup column. And then go to the “Additional Column Settings” section and select the additional “Duration (months )” column like below:

sharepoint 2013 list column default value
sharepoint 2013 list column default value

Now when you will add one item, you will add values for Title, Training Type and Courses, but the Duration (months) column will appear like below:

sharepoint column default value settings
sharepoint column default value settings

Read some SharePoint customization tutorials:

Conclusion

I hope this tutorial will be helpful to know about SharePoint lookup column. How to create a lookup site column in SharePoint 2013/2016 or SharePoint Online. Also, we have seen how to add a lookup site column to a SharePoint Online list.

  • Hi Bijay,
    i need to count number of semicolons present in a column(single line of text) and count should reflect in next column. Please help me

  • >