How to create a Lookup column in SharePoint

Though I am a huge advocate of metadata, one column type that I have never blogged about previously is the Lookup column in SharePoint. The reason for this is that it is a unique type of column you would only use in very rare scenarios, and it does come with limitations. But it can become handy in some instances, and I would like to use this opportunity to explain it to you and provide some tips on how to use it efficiently in your SharePoint environment.

What is a Lookup column

A Lookup column is a column type that allows you to connect a list or a library to a column from another list or library you have on your site and also pull in other information from that other list and display it the list you are connecting from. If I just confused you, let me give you an example. Say, you have two lists in SharePoint. One list for Client contacts with names, emails and phone numbers, and another for Client Company names with company address, company industry, etc. What you can do is associate Contacts with Company names by creating a Lookup column from Contacts list to a Company list. By doing this association, you will be able to bring in the Company information such as a company address, company industry, etc. into the contacts list, thus having all the information in one giant list. Hopefully, this makes sense now.

How to create a Lookup column in SharePoint

Let’s go ahead and use the above example and build this together.

Step 1: Build the custom lists (or document libraries)

Just to be clear, you can connect not just the SharePoint lists, but also the document libraries. In our case, we are going to connect the Contacts List (with individual’s names and contact info) with a Clients List (with company names and addresses).

The quickest way to dot his would be to create Custom Lists and add appropriate columns on both. I described how to do this here.

This is what it looks like in my case:

Contacts List

Clients/Company List

Step 2: Create a Lookup Column

Now we are going to build a Lookup Column from Contacts List to the Client List.

1. Navigate to the Contacts List
2. Click on Gear Icon > List Settings (we cannot use +Add Column option on the front end since Lookup Column is considered a classic feature and is not available there)
3. Under Columns list, click Create column
4. Give the column a name, then choose Lookup type, in the middle of the screen, choose the list or library where you will be looking up info from (in my case Clients), you also have an option to display (bring over) additional columns from that other list as well, so check those if need be, then click OK at the bottom of the page
5. Your Contact list should look like the image below
6. You can rename some of those pulled columns, so they make sense to you (i.e., rename Company:State to State)

Step 3: Tag the entries

1. Next, go ahead and tag the entries in your Contact List with Company Information
2. And this is what it looks like at the end

Step 4: One to Many Lookups (Optional)

It is important to note that you can also do a Lookup column in SharePoint to multiple entries at once. In the example above, I did a Lookup from the Contact List to Company List. You can also do it the other way around and link from Company List to multiple Contacts at once.

1. Make sure to select Allow multiple values when you Create a Lookup column.
2. Tag multiple fields when you tag an entry
3. And this is how it looks like when it is all set and done.

Lookup column limitations

Before you get too excited, I want you to be aware of several significant Lookup column limitations:

• You can only create a lookup to certain field types. Reference below image for supported and unsupported column types. Image and reference courtesy of Microsoft.
• Lookup might not be practical when you go against a list that contains many entries – drop-down gets lengthy.
• According to Nate Chamberlain, you can only have a max of 12 lookup columns in a view.
• You cannot create lookups across sites (linked lists and libraries need to reside in the same site)
• The lookup column only creates a one-way link. In other words, if you link Contact A to Company ABC, it won’t create a link from Company ABC to Contact A. So, if you want to do a 2-way relationship, you will need to do this manually both ways.

Lookup Column alternatives

Term Store

If you need a quick ability to link columns on lists/libraries, the Lookup column is definitely a life-saver! However, if you just need a list of metadata (drop-down choices) to be centrally accessible by various sites and site collections, consider using Term Store. It allows for much easier metadata management and mitigates the many limitations mentioned above.

Power Apps

This is not an out of the box feature by any means, but if you are into Power Apps, you can easily do Lookups there while addressing all the limitations mentioned above. Please reference these excellent instructions by Shane Young.