What are SharePoint Lookup Columns? How to Create it?

By  //  March 25, 2022

The main goal of SharePoint is to organize the data and workflow of the business. It has some best resources and tools to enhance the users’ productivity. There are a lot of pieces of the puzzle that you must be aware of to unlock the best for your organization.

We will be exploring the SharePoint lookup column in this content piece. We will begin from its definition to create one.

What is a SharePoint lookup column?

This column type allows connecting a list or library to a column from another library or list. You have two lists in SharePoint with different details about the same topic. You can create a SharePoint list lookup with a few elements from the SharePoint lists. This means you have all the information in a single list.

Users get the freedom to create lookup columns in the document library UI and modern list. You must be aware of the data types that are supported and unsupported by the SharePoint lookup column:

Supported Data Types:

Single Line of text

 Number

Date and time

Unsupported Data Types:

Multiple lines of text

Menu of choice

Currency

Checkbox

Person or Group

Hyperlink

Calculations

External Data

How to create a SharePoint lookup column?

1. Create custom lists or document libraries

SharePoint lookup column is not only limited to lists, but you can also connect with document libraries. Start by creating custom lists and adding appropriate columns. The process to create a custom list is:

Click on Gear Icon and then Site Contents

New > List

Decide a name and click on Create

You can see a blank custom list on the screen

2. Build a Lookup Column

It is time to create a SharePoint Lookup list:

Navigate to your list

Click on Settings, then List Settings

Under the Columns List, click on Create column

Give a column name, then select Lookup type. Choose the library or list you are looking for. You also get the choice to display additional columns from another list if required. Check the ones you need and click on OK.

You can also rename some pulled columns to make it understanding

3. Tag Entries

Add the entries in the SharePoint lookup list. This will give you all the related items in a single place.

 What are the limitations of the SharePoint lookup list?

There are certain things that you must be familiar with before exploring the features of the SharePoint lookup list:

Users can only create a lookup of certain field types.

Lookup might not be practical when you need to add a list with many entries

SharePoint allows only 12 lookup columns in a view

Users cannot create lookups on different sites. That means the linked lists and libraries should be available on the same site.

The SharePoint lookup works only one-way

What are the alternatives to the SharePoint lookup column?

To fight the limitations, you must be aware of the alternative solution to provide better results. One of the alternatives is the Term Store feature, but it is only accessible from the admin centre of SharePoint. It includes a list of metadata that is centrally accessible by various sites and site collections. The management of metadata becomes effortless.

The other one is Power Apps, but you need to have deep knowledge before choosing this platform. It has many functionalities that will overcome the limitations in the best possible way.

How to enforce relationship behaviour in the SharePoint lookup column?

Let us consider a situation where you have used some information from the source list into the destination list in the Lookup column. Now, after a while, you want to remove the records from the source list, then what happens?

In such a scenario, you need to enforce relationship behaviour in the SharePoint lookup column. Let us look at the correct procedure:

Redirect to List Settings

Click on the Lookup column

Edit the column

Now scroll down and checkmark the Enforce relationship behaviour

Here, you will encounter two choices:

Restrict Delete: If you enable this, then if a user tries to delete a Source List that is used in the Destination list, they will not be able to do it and get a warning:

This item cannot be deleted because an article in the “Destination List” list is related to an item in the “Source List” list.

Cascade Delete: If this is enabled, then when a user tries to delete a record in the Source list that is used in the Destination list, they will get a Pop-up saying:

Are you sure you want to send this item to the sire Recycle bin?

If you confirm, then the record with be deleted without any issue.           

How to turn your SharePoint lookup column into a super lookup?

■ Cross-Site Lookups: There is a Cascading LookApp in the Microsoft 365 store that helps remote lists available in the SharePoint sites.

■ Filtered lookups by views: The SharePoint lookup list can be filtered by list view. It enables to display of different lookup content to the various users.

■ Cascaded Lookups: Ease of filtering the field values by other fields in the same lookup list.

■ Filter lookup values using typed text: The LookApp will become a part of the text that you might be searching for and match the items for selection.

■ Remove duplicate values: The user can filter the lookup by other columns from the lookup list. This enables the display of unique values and makes it easier for the users.

Also, note that this will not work if the SharePoint lookup column is a site column, the multiple values are enabled on the column, or the lookup column is referencing data from a different site.

The Endnote!

We have covered all the core segments of the SharePoint lookup column to make this journey effortless for you. Being a part of an organization, this feature is very useful to access and maintain information in a proper structure.