How to use modern lookup columns in Microsoft Lists

By João Ferreira Feb 4, 2022 Microsoft Lists 4 Comments

Lookup columns allows you to enforce a relationship between different sources of data in your data system. This feature is a simplistic version of the classic relationship between tables in a data base.

Up until recently this type of data was only available through the classic interface of SharePoint but now it’s made a debut in the modern interface of Microsoft Lists and can be used without jumping between 2 different environments.

Lookup columns

Lookup column example

You have a list where you store the information of all your suppliers and now you are building a list to keep track of your assets, each item in the assets list should display the name and the phone number of the supplier and you can only select suppliers previously added to suppliers list.

To create a lookup column, you must have 2 different lists, one where you will create the actual column and another one that will be feeding the information displayed in the main list. With these requirements fulfilled to create a lookup column you should:

Open the main list where you are storing the information and click in the Add column option
From the menu click on Lookup
Provide a Name for the new column and optionally a Description
Select a list as a source
Select a column from the selected list
Click in the More options, and select any other column you may want to see also in the list
Select the remaining options according to your needs and click Save

As you can see highlighted in the following picture, we have the supplier column and the supplier phone number, any extra column connected to the source list will be displayed in the following format ColumnNameMainList: ColumnNameSourceList.

Lookup columns

In the new item form or in the item detail, a lookup column will be displayed as a drop-down in the list form and will always be updated with the values you have in the source list, in the example shown in this article if the phone number of the supplier gets updated in the source list it propagates to all list items where this lookup is connected.

4 Responses to “How to use modern lookup columns in Microsoft Lists”

Lia
February 6, 2024

Hello, can you help with my issue?
the columns in the source list don’t match with the list of column showed in lookup drop down menu and additional column. In the lookup option they show default column name such Title, Color Tag, Compliance Asset ID, etc., while my source list has custom column names that I would like to use.
Thank you.
- João Ferreira
  February 6, 2024
  
  Hi Lia,
  
  I did a quick test and by default the name on the lookup columns reflects the name on the original list when the column was created.
  However, if after adding the lookup and the extra columns you have the possibility to edit each one of the columns and rename them.
  
  Hope this helps.
  
  Have a nice day 🙂
Bill Spang
February 12, 2024

It seems that Lookups in Lists are not consistent with common enumeration principles in RDBMS’s…

In RDBM’s we will have an ID and a Description. When we add the Enum Lookup to the Application Interface, we will have 2 columns, i.e., the ID and the Description, where the ID is a hidden column, and we can select from the List the Description. The ID will be seeded on the Record. IN MS Lists, we cannot seed the ID from the Lookup by selecting the Description.

Is there a solution to seed the ID on the List Record when the Enum from the Lookup is selected?

Thanks!
- João Ferreira
  February 13, 2024
  
  Hi Bill,
  
  If I understood you question correctly, to get the ID from the original lookup column you need to open the More options in the lookup column settings and select the ID.
  
  In the list with the lookup column you will then see the Value followed by the ID.
  
  Let me know if this is what you are looking for.
  
  Have a nice day 🙂