In this article, I will explain

How to use Lookup Field in SharePoint Calculated Column Formula

Scenario

I have a list with a lookup field, I would like to use this lookup field in in SharePoint Calculated Column Formula but unfortunately, it’s not listed in the available formula column as shown below:

Cause

It’s by design, The lookup column can’t be used in SharePoint Calculated Column Formula and SharePoint Column Validation.

Check The Supported and Unsupported Columns in SharePoint Calculated Column Formula.

Workarounds

Using Choice Field instead of Lookup field

If you can change the lookup field to choice field with static options, so it’s recommended to use the choice field instead of lookup field, the choice field is supported in the Calculated Column. Check The Supported and Unsupported Columns in SharePoint Calculated Column Formula.

Using SharePoint Designer Workflow

• Create a new lookup field with a supported data type like a Single line of text.
• In the workflow, Set the new lookup field value with the original Lookup column value on item created or changed.
• Now you can use the new lookup field in the SharePoint calculated column Formula.

Using Code.

In case you have a development background,  you can also use the Event Receiver to build your formula via code.

In this article, I will explain the workflow workaround steps.

Steps

• Open your list, Create a new Field with an appropriate name like “LookupValue” with a single text data type.

• Hide the new “LookupValue” column from showing in the New Form and Edit form Using PowerShell.

$rootWeb = Get-SPWeb http://siteURL

#Get a reference to the  List

$list=$rootWeb.Lists["List Name"]

#Get a reference to the created field

$Field = $list.Fields["Column Name"]

#Hide the column in New and Edit Mode

$Field.ShowInEditForm = $false

$Field.ShowInNewForm = $false

#Push field Update

$Field.Update()

• Open SharePoint Designer > Workflow > add a new list workflow > Select your list.
• Provide an appropriate name for your workflow.
• In Actions > Below List actions > select Set Field in Current Item.
• Select your new field “LookupValue” and set it to the current List Lookup column.
• Make sure that the workflow will start on Item created/changed.
• Save & Publish Your Workflow.

Note: In the workflow,

For single lookup value use the following:

For Multiple lookup values use the following

• Test your workflow, by adding a new item, you should note that the lookup value column is equal to the Lookup field value.

• Open your List > List Setting > List column > Create a new Calculated Column.
• Add the listed new field “Lookup value” in your formula > save the column setting.

• Add new List item, you should note that the NewID=ID and reflected to the calculated column.

Applied To

• SharePoint Online.
• SharePoint 2016.
• SharePoint 2013.
• SharePoint 2010.

Conclusion

In this article, I have explained How to use Lookup field in SharePoint Calculated Column Formula.

See Also

• The Supported and Unsupported Columns in SharePoint Calculated Column Formula.
• Using ID Field in Calculated Column in SharePoint.
• My answer at SharePoint Stack Exchange, Use a LookUp field in a Calculated Column