How to Use a Choice Field in a Lookup Column Using SharePoint and Microsoft Lists

By João Ferreira Feb 28, 2024 Tips and Tricks 1 Comment

SharePoint and Microsoft Lists are powerful tools for creating and managing data in various scenarios. One of the features that they offer is the ability to create lookup columns, which allow you to reference data from another list in your site. This can be useful for creating relationships between different types of data, such as customers and orders, products and categories, or employees and projects.

However, not all column types can be used as lookup fields. One of the column types that is not supported by default is the choice column, which lets you create a drop-down list of predefined options for your users to select from.

For example, you might have a choice column that stores the player position in a list of basketball players, such as point guard, shooting guard, small forward, power forward, or center. If you want to use this choice column as a lookup field in another list, such as a list of game statistics, you will notice that it does not show up in the list of available columns when you create the lookup column.

So, how can you use a choice field in a lookup column using SharePoint and Microsoft Lists? The answer is to use a bit of imagination and a workaround that involves creating another column type that can be used as a lookup field. In this blog post, I will show you how to do that step by step.

The Scenario

Let’s imagine that you are a basketball coach and you have two lists: one with the players and another one with the game statistics. In the players list, you have a choice column that stores the player position.

In the game statistics list, you want to have a lookup column that shows the player name and the player position from the players list.

However, when you try to create the lookup column, you will see that the choice column does not appear in the list of available columns. This is because the choice column is not supported as a lookup field by default. So, how can we solve this problem?

The Solution

The solution is to create a new column in the players list that can be used as a lookup field, and then use a formula to copy the value from the choice column to the new column. Here are the steps to do that:

1. In the players list, click Add column and select See all column types
   

   

2. Create a new column of the type Calculated. You can name it whatever you want, but for this example, I will name it Position Lookup
3. In the formula field, select the choice column that stores the player position. In this example, the choice column is named Player Position, so the formula will be [Position]
4. In the data type returned from this formula, select Single line of text. This will ensure that the new column can be used as a lookup field
   

   

5. Click OK to save the new column. You will see that the new column has the same value as the choice column, as shown below:
6. In the game statistics list, create a new lookup column. You can name it whatever you want, but for this example, I will name it Player.
7. Select the players list. This will allow you to reference data from the players list in the game statistics list
8. Expand the more options to select any extra column from the original list like the Player Position
   

   

9. Click OK to save the new lookup column. You will see that the new lookup column shows the player name and the player position from the players list, as shown below:

The Result

You have successfully used a choice field in a lookup column using SharePoint and Microsoft Lists. Now, you can use the lookup column to display and filter data from the players list in the game statistics list. For example, you can see the statistics of all the point guards, or compare the performance of different positions, or create charts and reports based on the player position.

The Limitation

This solution has one limitation that you should be aware of. You only be able to use it with choice columns with single values. If your choice column allows users to select more than one option it is not possible to reference it in the calculated column.

The Conclusion

In this blog post, I have shown you how to use a choice field in a lookup column using SharePoint and Microsoft Lists. This is a useful workaround for creating relationships between data that use choice columns, which are not supported as lookup fields by default. However, you should also be aware of the limitations of this solution. I hope you found this blog post helpful and informative. If you have any questions or feedback, please leave a comment below. Thank you for reading!