Advantages of Lookup column

Lookup column is field that takes value from the another list and display as a Choice column or Multi select List-Box depending upon the selection.

Below are the advantages of Look column over a Choice dropdown column:

• The Look up column allows non-admin users to add new choices in dropdown by adding new values in source list

• You can manage permission at source list per list item. Depending on permission lookup column will show/hide specific items to user based on permission

• Look up field can be used to make relationship between two lists

• We can create count related lookup column in source list, which is computed field that computes the number of items in the target list that point to the current item of source list. Here is article on the same.

How to create count related lookup column in SharePoint custom lists?

A lookup count column is a special type of computed field that computes the number of items in the target list that point to the current item.

Let’s say I have two lists Departments and Employee Data. Now in Department list I want to add new column which will calculate how many employees are there in that department.

Here’s how we need to setup our lists:

1) Create a Department list. And add your department values in a title column.

2) Create an Employee Data list. In “Employee Data” list I renamed ‘Title’ column to ‘Employee Name’. And then add column ‘Department’ which will be lookup column pointing to the title column in the Department list.

3) After adding some test values in “Employee Data” list the list will look like this

4) Now go to “Department” list, add a column of type "Lookup" referring to the “Employee Data” list and you'll notice that in the drop-down area where you define the lookup, you'll have a new option called "Count Related". This is here automatically because it recognizes that the “Department” list has a lookup pointing back to this one. Select that Count Related option.

5) Now your Department list will have a column counting how many employees are associated with that Department.

This doesn’t require any coding or JavaScript. A hidden SharePoint magic.