All About The SharePoint Lookup Column: Options, Limitations, and Alternatives

Introduction to SharePoint Lookup Columns

Since the inception of SharePoint, Lookup columns have been and continue to be one of the most unclear and difficult concepts for non-technical SharePoint users to grasp.

When the SharePoint Lookup column or its advanced enhancements are not used or are not used effectively, it often results in a poor SharePoint list structure, which leads to confusing usage, time-consuming and costly maintenance, and development costs.

In this article I’ll try to clarify what SP Lookup columns is, why and when it’s needed, what are its main limitations, and how we can overcome these limitations to implement common Lookup requirements such as:

• SharePoint Cascading Lookup columns (dropdown)
• SharePoint cross site Lookup columns
• Cross site collection Lookup columns
• SharePoint Lookup column to external data such as REST API and Azure SQL

What is a SharePoint Lookup Column?

A Lookup column is one of the available column types in SharePoint. It looks like a Choice column, displaying multiple options usually in a drop-down for the user to chose from, but unlike a Choice column, in a SharePoint list Lookup column these displayed choices are retrieved from another list.

Why Do We Need a SharePoint Lookup Column?

The best way to explain this is through an example. Let’s say we have SharePoint site where we want to manage product sales.
We have these 2 lists:

1. “Sales leads” list – includes information about product sales leads: customer name, customer email, customer phone, product, quantity, price.
2. “Orders” list – where we keep information about customer orders: order date, customer name, customer email, customer phone, product, ordered quantity, price.

Here’s how our lists will look like:

Let’s have a look at one of the customers, Nireet inc. I have this customer’s details saved multiple times: in each of their sales leads and in their orders.

Now, what happens if this customer changes their phone number? If I want to keep my lists up-to-date, I’d need to look for all the sales leads and orders of this customer, and update their phone number in all the relevant sales leads and orders. Now imagine you have tens of thousands of sales leads and orders.

The problem here is that we keep duplicates of the exact same information. The solution is to separate the customer details from orders and sales leads lists and save it in a separate “Customers” list. Now, whenever I create a new sales lead or a new order for a customer, I will not update all customer details, but instead have a Lookup column that connects the sales lead or order item to the relevant customer in the “Customer” list.

Instead of typing current customer details again when creating a new sales lead item or a new order item, end users can just choose the existing customer from a Lookup drop-down list.

To sum up, with a Lookup column SharePoint you can connect a list item to an existing item in another list. This allows you to keep each piece of information in one place without duplicating it.

How to Create an Out-of-the-box Lookup Column in SharePoint

I’ll be using the Customers – Orders – Sales leads example to describe the steps needed to create a SharePoint Lookup column:

• Step 1: Create the target Lookup list
  In our example we need 1st to create a “Customers” list.
• Step 2: Create a Lookup column
  In the lists where I want end users to be able to connect items to existing customers, add a new Lookup column. (In our example it’s the “Sales leads” and “Orders” lists).

This is done by going to the list’s settings and clicking on ‘Create column’ link:

Then select a ‘Lookup’ type(1), give it a name(2) and select the target list to which you want to connect(3) (‘Customers’ in our example). And finally – select the column name which you want to display from the target lookup item(4):

Save your settings and now go to your SharePoint list and create a new item:

As you can see in the screenshot above, there is no need to type the customer’s name and other details, just select one of the existing customers displayed in your SharePoint Lookup “Customer” column!

SharePoint Lookup Column Limitations and Alternative Solutions

I hope I’ve been able to clarify why using SP Lookup columns helps keeping your list data normalized (no duplicates), and how it helps to reduce its maintenance as your data grows. However, there are several limitations that prevents end-users from using the Lookup column to implement their use-cases.

These SharePoint Lookup limitations can be resolved by using development skills and tools such as MS PowerApps and can be also resolved by using no-code SharePoint add-ins such as KWizCom’s Cascading Lookup column and External Data Lookup.

Main Limitations of Lookup Columns

Below are the main limitations of the SharePoint Lookup column:

• SharePoint list lookup from another site
  The SharePoint Lookup column enables connecting only to lists located in the same site.
  Solution: by using KWizCom’s Cascading Lookup column you can have your SharePoint Lookup columns connected to remote lists across sites and across site collections.
• SharePoint Cross site collection lookup column
  Of course, same as SharePoint Lookup column cannot be used to connect to lists located in other sites, it cannot be used to connect to remote lists connected in other site collections.
  Solution: by using the KWizCom External Data Lookup you can connect to many types of external data sources, including SharePoint REST API. This allows you to quickly connect to any remote SharePoint list or library and have a cross site collection Lookup in your modern SharePoint forms.
• Cascading Lookup columns
  Well…this one is one of the most common use-cases users are enquiring about. It can be implemented using various development or power-user tools and some coding practices. Here is an example of how to use the PowerApps lookup Column with the following piece of code:

But what do you do if you are not a SharePoint developer or power-user who’s not familiar with PowerApps dropdown?
Solution: by using KWizCom’s Cascading Lookup column you can easily create Cascading Lookup columns in your modern SharePoint list forms, no need for any coding or power-user skills!

• External Data Lookup column
  As you know by now, you can connect a Lookup column only to (local) SharePoint lists and libraries. So what do you do if you need to have a Lookup dropdown column that displays options pulled from your Azure SQL database or any other external system?
  Solution: by using KWizCom’s External Data Lookup you can easily create a SharePoint Lookup column securely connected to any web service using MSAL, including Azure SQL database.

Final Note

Now that you know what a Lookup columns in SharePoint is, why we need it and how to create one, you can easily overcome the limitations it has without any technical knowledge or coding skills by using the no-code apps that KWizCom provides.