If you have plenty of lists and libraries on one of your sites, then you may have wondered at some point if there was a way for you to show some columns from one list or library to another. Show With a lookup column, thats certainly possible. In this article, Ill show you how to easily create one on your SharePoint site. Note that although the use case for this feature is incredible, it comes with some limitations too. Lets get started! What is a lookup column?The simplest explanation is that a lookup column is a special type of column that enables you to connect a list or a library to a column from another list or library on one of your sites. Specifically, it will pull in other information from another list or library and display it to the list youre connecting from by matching the information on the lookup column on both lists or libraries. For example, lets say you have two lists in one of your SharePoint sites The first list contains information about your customer contacts like their full names, email addresses, contact information, and others. The second list contains information about your customers company, including the full company name, address, industry, and others. With a lookup column, you can associate customer contacts with company names simply by creating a lookup column from the customer contacts list to the company list. Through this connection, you will be able to pull in company information, like the name and address, into the customer contacts list. In a way, its like youre putting all possible information on one of the lists. By the way, a lookup column is quite different from a site column, which is built at the site collection level and can be used across subsites within that collection. A site column, just like a standard lookup column, doesnt provide any value across site collections like managed metadata can. How to create a lookup column?Fortunately, its not really that hard to create a lookup column. In fact, the steps required here are similar to creating a calculated column. But before we start, make sure that the lists or libraries you want to connect together are ready. After that, its time to proceed with the first step. Step 1: Add a Lookup ColumnFirst, navigate to the list or library where you want to display information from another list or library. Click on +Add column > More.  Since the lookup column is considered a classic feature, you cant find it on the list of available column types right after clicking on +Add column. On the next page, give the column a name, and choose lookup as the type of column. Step 2: Configure the Lookup ColumnThe next step is to configure the lookup column by specifying which list it will connect to and which column it will look for information. On the same page, scroll down a little until you get to this part: All you have to do here is to select the list or library where you want to connect to and which column to look up. In this case, the list will be Companies and the information will be on the Title column (which is actually renamed to Company in the Companies list). The Allow multiple values option will enable you to match an entry with more than one entry on the other list or library in a one-to-many relationship. I will show this to you later on in the third step. Note that you can also choose to show additional columns from the same list or library youre connecting to. For example, we can also set it up to show the companys industry and address. Once its all set up, all you need to do is to proceed with creating the lookup column. Step 3: Edit the MetadataAfter creating the lookup column, it will display immediately on the list or library. In our example, the newly created Company lookup, along with columns for the companys industry and address, will now be added to the Customers list. The additional fields are displayed in List:Column format. You can always rename them accordingly by clicking the dropdown icon of the column title > Column Settings > Rename as shown below. The next part is editing the metadata of the entries in your list and specify which company the customer belongs to. To do so, click on the entry > information icon and indicate which company that customer belongs to. Dont forget to press Enter to save the changes. Here is how it looks like now: By the way, in the second step, I mentioned that its possible to create a one-to-many relationship. For example, a certain customer can belong to two companies and make it reflect on the lookup column. To do this, its important that the option for allowing multiple values is enabled. When you select more than one match, it will now look like this: Limitations of a Lookup ColumnThe lookup column feature is an amazing functionality that will somehow turn your list or library into a functional database. Unfortunately, it comes with its own limitations too. For one, a lookup column only works one way. In our example earlier, we can view information about the company in which certain customers belong to. However, that relationship is only established on the Customers list. If you want to create a link from the Companies list and view the information there for customers that belong to that company, you must set up another lookup column there. As you can see below, the Companies list is untouched. Another limitation is that you can only have 12 lookup columns in a view. This includes the workflow status columns, traditional lookup columns to other lists, and person/group columns (including the two default people fields Created by and Modified by). If you push it, you will get the following error: This view cannot be displayed because the number of lookup and workflow status columns it contains exceeds the threshold (12) enforced by the administrator. Although its possible to change this with the on-premises SharePoint, you cant do anything about this in SharePoint Online. Another one is that according to Microsofts documentation, you can only create a lookup to three column types:
The last limitation is the fact that you cant create lookups across different sites. You can only use this feature to connect lists and libraries that are on the same site. Alternatives to a Lookup ColumnIf the limitations were a turn-off for you, there are a few things you can do. For example, if you only need a list of metadata to be accessible by different sites and site collections, you can use Managed Metadata via the Term Store. Through it, you can easily manage metadata without the limitations of a lookup column. The Term Store is controlled separately using the Admin Center of SharePoint. The feature is under content services as shown below: Organizing metadata in the Term Store deserves a guide of its own. But in essence, all you need to do is to:
Another thing you can use is Power Apps. This may be on a different level, but if youre familiar with it, you can easily create lookups while avoiding all the limitations of using the classic SharePoint lookup column feature. Using the SharePoint Lookup ColumnAs you can see, a lookup column is an interesting feature that can really save you time if you want to view information on a list or library from another list or library on the same site. It may come as a little bit confusing at first, but the bottom line is that it allows you to include information found on a list or library into another list or library of your choice. Its like creating a mega list or library. To create a lookup column, all you need to do is to:
Its also possible to create a one-to-many relationship by allowing multiple values on your lookup column. Unfortunately, it does come with limitations. For one, a lookup column only creates a one-way link. To view information on both lists or libraries, you must create another lookup column. Another is that there are only three supported types of columns. Furthermore, you can only have 12 lookup columns in a single view. The more extra fields you add, the faster that allowance gets depleted. Fortunately, there are alternatives you can use that will allow you to dodge the limitations. One of them is by using the term store feature, which is only accessible from the Admin Center of SharePoint. The other alternative is using Power Apps, although this is only possible if you know how to use it. But whats important here is that there are other things you can do if you want to have the lookup functionality without the limitations. If you need help with anything related to SharePoint, feel free to leave a comment below or send me a direct message via the contact page. About Ryan Clark As the Principal Solutions Architect at Mr. SharePoint, I help companies of all sizes better leverage the Modern Workplace and Digital Process Automation investments. View all posts by Ryan Clark | Website Tweet Share Share Pin |
